Determining the DML action within a trigger

Oracle offers a set offunctions (also known as operational directives) that allow you to determine which DML action caused the firing of the current trigger. Each of these functions returns TRUE or FALSE, as described below.

INSERTING

Returns TRUE if the trigger was fired by an insert into the table to which the trigger is attached, and FALSE if not.

UPDATING

Returns TRUE if the trigger was fired by an update of the table to which the trigger is attached, and FALSE if not.

DELETING

Returns TRUE if the trigger was fired by a delete from the table to which the trigger is attached, and FALSE if not.

Using these directives, it is possible to create a single trigger that consolidates the actions required for each of the different types of operations. Here is one such trigger:

/* File on web: one_trigger_does_it_all.sql */CREATE OR REPLACE TRIGGER three_for_the_price_of_oneBEFORE DELETE OR INSERT OR UPDATE ON account_transactionFOR EACH ROWBEGIN -- track who created the new row IF INSERTING THEN :NEW.created_by := USER; :NEW.created_date := SYSDATE; -- track deletion with special audit program ELSIF DELETING THEN audit_deletion(USER,SYSDATE); -- track who last updated the row ELSIF UPDATING THEN :NEW.LAST_UPDATED_BY := USER; :NEW.LAST_UPDATED_DATE := SYSDATE; END IF;END;

The UPDATING function is overloaded with a version that takes a specific column name as an argument. This is handy for isolating specific column updates.

/* File on web: overloaded_update.sql */CREATE OR REPLACE TRIGGER validate_updateBEFORE UPDATE ON account_transactionFOR EACH ROWBEGIN IF UPDATING ('ACCOUNT_NO') THEN errpkg.raise('Account number cannot be updated'); END IF;END;

Specification of the column name is not case-sensitive. The name is not evaluated until the trigger executes, and if the column does not exist in the table to which the trigger is attached, it will evaluate to FALSE.

Operational directives can be called from within any PL/SQL block, not just triggers. They will, however, only evaluate to TRUE within a DML trigger or code called from within a DML trigger.

18.1.3 DML Trigger Example: No Cheating Allowed!

One application function for which triggers are perfect is change auditing. Consider the example of Paranoid Pam (or Ms Trustful as we call her), who runs a bowling alley and has been receiving complaints about people cheating on their scores. She recently implemented a complete Oracle application known as Pam's Bowl-A-Rama Scoring System, and now wants to augment it to catch the cheaters.

The focal point of Pam's application is the frame table that records the score of a particular frame of a particular game for a particular player:

/* File on web: bowlerama_tables.sql */CREATE TABLE frame(bowler_id NUMBER, game_id NUMBER, frame_number NUMBER, strike VARCHAR2(1) DEFAULT 'N', spare VARCHAR2(1) DEFAULT 'N', score NUMBER, CONSTRAINT frame_pk PRIMARY KEY (bowler_id, game_id, frame_number));

Pam augments the frame table with an audit version to catch all before and after values, so that she can compare them and identify fraudulent activity:

CREATE TABLE frame_audit(bowler_id NUMBER, game_id NUMBER, frame_number NUMBER, old_strike VARCHAR2(1), new_strike VARCHAR2(1), old_spare VARCHAR2(1), new_spare VARCHAR2(1), old_score NUMBER, new_score NUMBER, change_date DATE, operation VARCHAR2(6));

For every change to the frame table, Pam would like to keep track of before and after images of the affected rows. So she creates the following single audit trigger:

/* File on web: bowlerama_full_audit.sql */ 1 CREATE OR REPLACE TRIGGER audit_frames 2 AFTER INSERT OR UPDATE OR DELETE ON frame 3 FOR EACH ROW 4 BEGIN 5 IF INSERTING THEN 6 INSERT INTO frame_audit(bowler_id,game_id,frame_number, 7 new_strike,new_spare,new_score, 8 change_date,operation) 9 VALUES(:NEW.bowler_id,:NEW.game_id,:NEW.frame_number,10 :NEW.strike,:NEW.spare,:NEW.score,11 SYSDATE,'INSERT');12 13 ELSIF UPDATING THEN14 INSERT INTO frame_audit(bowler_id,game_id,frame_number,15 old_strike,new_strike,16 old_spare,new_spare,17 old_score,new_score,18 change_date,operation)19 VALUES(:NEW.bowler_id,:NEW.game_id,:NEW.frame_number,20 :OLD.strike,:NEW.strike,21 :OLD.spare,:NEW.spare,22 :OLD.score,:NEW.score,23 SYSDATE,'UPDATE');24 25 ELSIF DELETING THEN26 INSERT INTO frame_audit(bowler_id,game_id,frame_number,27 old_strike,old_spare,old_score,28 change_date,operation)29 VALUES(:OLD.bowler_id,:OLD.game_id,:OLD.frame_number,30 :OLD.strike,:OLD.spare,:OLD.score,31 SYSDATE,'DELETE');32 END IF;33 END audit_frames;

Notice that for the INSERTING clause (lines 6-11), she relies on the NEW pseudo-record to populate the audit row. For UPDATING (lines 14-23), a combination of NEW and OLD information is used. For DELETING (lines 26-31), Pam only has OLD information with which to work. With this trigger in place, Pam can sit back and wait for action.

Of course, Pam doesn't announce her new auditing system. In particular, Sally Johnson (a very ambitious but not terribly skilled bowler) has no idea she is being watched. Sally has decided that she really wants to be the champion this year, and will stop at nothing to make it happen. Her father owns the bowling alley, she has access to SQL*Plus, and she knows that her bowler ID is 1. All that constitutes enough privilege and information to allow her to bypass the application GUI altogether, connect directly into SQL*Plus, and work some very unprincipled "magic."

Sally starts out by giving herself a strike in the first frame:

SQL> INSERT INTO frame 2 (BOWLER_ID,GAME_ID,FRAME_NUMBER,STRIKE) 3 VALUES(1,1,1,'Y');1 row created.

But then she decides to be clever. She immediately downgrades her first frame to a spare to be less conspicuous:

SQL> UPDATE frame 2 SET strike = 'N', 3 spare = 'Y' 4 WHERE bowler_id = 1 5 AND game_id = 1 6 AND frame_number = 1;1 row updated.

Uh oh! Sally hears a noise in the corridor. She loses her nerve and tries to cover her tracks:

SQL> DELETE frame 2 WHERE bowler_id = 1 3 AND game_id = 1 4 AND frame_number = 1;1 row deleted. SQL> COMMIT;Commit complete.

She even verifies that her entries were deleted:

SQL> SELECT * FROM frame;no rows selected

Wiping the sweat from her brow, Sally signs out, but vows to come back later and follow through on her plans.

Ever suspecting, Pam signs in and quickly discovers what Sally was up to by querying the audit table (Pam might also consider setting up an hourly job via DBMS_JOB to automate this part of the auditing procedure):

SELECT bowler_id, game_id, frame_number, old_strike, new_strike, old_spare, new_spare, change_date, operation FROM frame_audit;

Here is the output:

BOWLER_ID GAME_ID FRAME_NUMBER O N O N CHANGE_DA OPERAT--------- ------- ------------ - - - - --------- ------ 1 1 1 Y N 12-SEP-00 INSERT 1 1 1 Y N N Y 12-SEP-00 UPDATE 1 1 1 N N 12-SEP-00 DELETE

Sally is so busted! The audit entries show what Sally was up to even though no changes remain behind in the frame table. All three statements were audited by Pam's DML trigger: the initial insert of a strike entry, the downgrade to a spare, and the subsequent removal of the record.