This is based on the original table called employee, which you created on Day 9, "Using SQL: INSERT, SELECT, Advanced Declarations, and Tables." After you execute the code, the following message should appear at the prompt:

Table Created

You can now create the trigger that will occur whenever anyone alters the employee table.

Creating the Trigger to Signal an Alert

You can now create the trigger that will signal an alert when any changes have been made to the employee table. Enter and execute the code in Listing 20.2.

INPUTListing 20.2. Creating the trigger to signal the alert.

CREATE or REPLACE TRIGGER security

/* This trigger package will send an alert called emp_change when
   a row has been inserted, deleted, or updated. It will also send
   a message with the old Employee ID, the New Employee ID, the old
   Pay Rate and the new Pay Rate  */

BEFORE INSERT OR UPDATE OR DELETE ON employee
FOR EACH ROW
BEGIN

/* Send the Alert emp_change with the old and new values from the
   row being updated, changed, or deleted. Notice the use of :OLD
   for the contents of the original data and :NEW for the contents
   of the new data  */

     DBMS_ALERT.SIGNAL(`emp_change','NOTICE:  OLD ID: ` || :OLD.emp_id
          || ` NEW ID: ` || :NEW.emp_id || ` OLD Pay Rate: `
          || :OLD.pay_rate || ` NEW Pay Rate:  ` || :NEW.pay_rate);

/* Insert all of the values into the security table */
     INSERT INTO security
          (OLD_emp_id,OLD_emp_name,OLD_supervised_by,
           OLD_pay_rate,OLD_pay_type,OLD_emp_dept_id,
           NEW_emp_id,NEW_emp_name,NEW_supervised_by,
           NEW_pay_rate,NEW_pay_type,NEW_emp_dept_id,
           verified,changed_by,time_changed)
     VALUES
          (:OLD.emp_id,:OLD.emp_name,:OLD.supervised_by,
           :OLD.pay_rate,:OLD.pay_type,:OLD.emp_dept_id,
           :NEW.emp_id,:NEW.emp_name,:NEW.supervised_by,
           :NEW.pay_rate,:NEW.pay_type,:NEW.emp_dept_id,
           `N',USER,SYSDATE);

END security; -- End of the Trigger Security

ANALYSIS Because you are looking at values being altered in a row, you base the trigger on FOR EACH ROW only when the values of the row have been inserted, updated, or deleted from the table employee. If any of those conditions occur, an alert is signaled called emp_change, which passes the following in the message:

• The original employee ID
• The new employee ID
• The original employee pay rate
• The new employee pay rate

All of these are concatenated into a VARCHAR2 string using the concatenation operator (||). This is well under the message limit of 1800 characters.

The trigger then performs an INSERT on the security table to add all the original data, the new data, who changed the data, the date the data was changed, and finally whether the data has been verified by security. At any point in time you could run a query against this table for Security.Verified = `N' when no one has been watching the screen waiting for this to occur.

Waiting for the Alert

The next step is to wait for an alert, and then finally cause an alert to happen. Because you are going to practice inserting, deleting, and updating, I recommend that before you do anything, you enter the code in Listings 20.3 through 20.6. Listing 20.3 registers the alert and then waits for the alert. The other three listings practice, in order, an insert, an update, and then a delete. Again, at the SQL*Plus prompt, type SET SERVEROUTPUT ON and press Enter to see output to the screen.

INPUTListing 20.3. Registering and waiting for an alert.

DECLARE
    message VARCHAR2(1800); -- Display Incoming Message from Alert
    status INTEGER; -- Holds Status 0 if success, 1 if timed out
BEGIN
     DBMS_ALERT.REGISTER(`emp_change'); -- Registers for Alert emp_change
     DBMS_ALERT.WAITONE(`emp_change',message,status,60); -- Wait for alert
     DBMS_OUTPUT.PUT_LINE(message);  -- Display Message
     DBMS_ALERT.REMOVE(`emp_change'); -- Remove Registration for Alert
END;

ANALYSIS You first create two variables, one called message to hold the message sent by the alert, and the other called status to hold the status of the procedure WAITONE. You begin by registering for the alert emp_change. You then wait for the alert for 60 seconds. For these examples, I recommend that you set this to a value of 600 to wait for the alert. This gives you enough time to execute this procedure in one window, and you can then execute the insert,

update, and delete in another window. If the alert is signaled before your time limit expires, the DBMS_OUTPUT package displays the message to the screen. Then remove the alert from the registration. This wait time will change, depending upon the circumstance.

Using INSERT to Signal the Alert
You can practice inserting a record into the employee database. This will require two open sessions. In the first session, execute the code in Listing 20.3. Make sure that you have first typed SET SERVEROUTPUT ON and hit Enter. Before you execute the code, make sure that you have changed the time to wait to 600 seconds if you need the time to enter the SQL code in Listing 20.4.

INPUTListing 20.4. Inserting a record to trigger an alert.

INSERT INTO employee
     (emp_id, emp_name,supervised_by,pay_rate,pay_type,emp_dept_id)
     VALUES(9109,'Benjamin Franklin',209,20.50,'H',10);
COMMIT;

Without the final COMMIT statement, the alert would never trigger. When you execute the code from Listing 20.4, on the screen where you perform the insert, your output should be

1 row created

After the COMMIT has been executed, you should see

Commit complete

On the other screen that is monitoring the alert, your output should look like

NOTICE:  OLD ID:  NEW ID: 9109 OLD Pay Rate:  NEW Pay Rate:  20.5

PL/SQL procedure successfully completed.

If the procedure ends without output, make sure that you have entered SET SERVEROUTPUT ON. The other possibility is that the INSERT command did not complete before the time to wait for the alert elapsed.

Because this is a new record, as would be expected, there is no data in the OLD values.

Using UPDATE to Signal the Alert
With your two sessions still open, execute in one of the SQL*Plus screens the code in Listing 20.3. On the other screen, execute the code in Listing 20.5 to practice updating a record. Before you execute the code from Listing 20.3, make sure that you have changed the time to wait to 600 seconds if you need the time to enter the SQL code in Listing 20.5.