6.4 Trigger Body


The trigger body is written in PL/SQL. There are PL/SQL expressions that only have meaning within a trigger body. These topics are covered in the following paragraphs.

A trigger body can refer to two states of a column value. The syntax for each state is:

 
 :NEW.COLUMN_NAME :OLD.COLUMN_NAME 

:NEW.COLUMN_NAME is the syntax for referencing a column value within the body of the row INSERT and UPDATE triggers. This expression evaluates to NULL in DELETE row triggers. It evaluates to the value included in the SQL INSERT or UPDATE statement.

If the SQL statement does not reference a column, the following rules apply:

  • For INSERT statements, :NEW.COLUMN_NAME is either NULL or the value of the DEFAULT used in creating the table.

  • For UPDATE statements, :NEW.COLUMN_NAME is the value currently in the table.

:OLD.COLUMN_NAME is valid for UPDATE and DELETE row triggers only. This evaluates to NULL for INSERT triggers. The :OLD.COLUMN_NAME evaluates to the value currently in the table.

The values of :OLD.COLUMN_NAME and :NEW.COLUMN_NAME expressions are identical for BEFORE and AFTER row triggers. The choice of a BEFORE or AFTER row trigger is a choice to execute the trigger before or after Oracle constraint enforcement. Visibility to :OLD and NEW values is identical with each. One caveat is that you can change the value of :NEW.COLUMN_NAME in BEFORE row triggers, not in AFTER row triggers.

The following illustrates a table that has BEFORE UPDATE and AFTER UPDATE row triggers. It prints the old and after column values.

 
 CREATE TABLE TEMP(N NUMBER, M NUMBER DEFAULT 5); CREATE OR REPLACE TRIGGER temp_bur BEFORE UPDATE ON TEMP FOR EACH ROW BEGIN     dbms_output.put_line('BUR old N:':old.n ' M:':old.M);     dbms_output.put_line('BUR new N:':new.n ' M:':new.M); END; CREATE OR REPLACE TRIGGER temp_aur AFTER UPDATE ON TEMP FOR EACH ROW BEGIN     dbms_output.put_line('AUR old N:':old.n ' M:':old.M);     dbms_output.put_line('AUR new N:':new.n ' M:':new.M); END; 

The OLD and NEW values are the same for BEFORE and AFTER row triggers.

 
 SQL> INSERT INTO TEMP (n) VALUES (1); SQL> UPDATE TEMP SET n=n+1 WHERE n>=1; BUR old: N=1 M=5 BUR new: N=2 M=5 AUR old: N=1 M=5 AUR new: N=2 M=5 SQL> UPDATE TEMP SET m=2 WHERE M=5; BUR old: N=2 M=5 BUR new: N=2 M=2 AUR old: N=2 M=5 AUR new: N=2 M=2 

The BEFORE UPDATE row trigger is changed. It increments the :NEW.N. This effectively changes the value inserted into the database. This trigger has one line added, the statement to increment :NEW.N.

 
 CREATE OR REPLACE TRIGGER temp_bur BEFORE UPDATE ON TEMP FOR EACH ROW BEGIN     :NEW.N := :NEW.N + 1;     dbms_output.put_line('BUR old N:':old.n ' M:':old.M);     dbms_output.put_line('BUR new N:':new.n ' M:':new.M); END; 

The results of the same INSERT and UPDATE statements show that N was inserted into the database with an incremented value. The first UPDATE in the following statement increments: N to N + 1, from 1 to 2. The trigger increments N again to a value of 3. The second UPDATE statement does not reference the column N, yet it is incremented in the column.

 
 SQL> INSERT INTO TEMP (n) VALUES (1); SQL> UPDATE TEMP SET n=n+1 WHERE n>=1; BUR old: N=1 M=5 BUR new: N=3 M=5 AUR old: N=1 M=5 AUR new: N=3 M=5 SQL> UPDATE TEMP SET m=2 WHERE M=5; BUR old: N=3 M=5 BUR new: N=4 M=2 AUR old: N=3 M=5 AUR new: N=4 M=2 


Programming Oracle Triggers and Stored Procedures
Programming Oracle Triggers and Stored Procedures (3rd Edition) (Prentice Hall PTR Oracle Series)
ISBN: 0130850330
EAN: 2147483647
Year: 2003
Pages: 111
Authors: Kevin Owens

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net