?> Special Trigger Variables(Syntax Record Description)

Creating Triggers  «Prev  Next»

Lesson 4 Special variables available in triggers
Objective Describe the syntax and use of special record variables.

Special Variables available in Triggers

This lesson covers the basic syntax of a trigger and describes some special variables that can only be used in triggers.

Trigger syntax

Check the following graphic for syntax and an example of a trigger.
There are two sets of variables that you can use when coding your triggers:
  1. :OLD.columnname: Each value (including null values) found in the row before it is updated or deleted can be referenced by using the :OLD.columnname variables.
  2. :NEW.columnname: The new value that will be inserted or updated into the row before it is updated or inserted can be referenced by using the :NEW.columnname variables.
Look at the MouseOver for an example of the code and the values when using these variables. The MouseOver shows you the CREATE TRIGGER command for a trigger on the PRODUCT table. The next line shows you the current values in one row of the PRODUCT table. Finally, an UPDATE command is used to illustrate the new values that are going to be updated into that same row in the PRODUCT table. Place your mouse over the variables in the trigger code to view the value that each one contains when the trigger is fired for the update.

Syntax and an example of a trigger 
CREATE OR REPLACE TRIGGER PRE_CUSTOMER
BEFORE
INSERT OR UPDATE ON CUSTOMER
FOR EACH ROW
BEGIN
   // PL/SQL Statements
END
Syntax and example of a trigger

Special variables

PL/SQL Data Types
  1. NULL
  2. 4.95
  3. 4.50
  4. 4.50
  5. 3.50
  6. Today's date and time
  7. Old sale price = 6.50
  8. Old store cost = 3.50
  9. New store cost = 4.50
  10. New sale price = NULL

CREATE OR REPLACE TRIGGER PRE_PRODUCT
  BEFORE UPDATE ON PRODUCT 
  FOR EACH ROW 
  BEGIN 
  IF :NEW.SALE_PRICE IS NULL THEN 
      :NEW.SALE_PRICE := :NEW.STORE_COST * 1.10
  END IF;
  IF :NEW.STORE_COST <> :OLD.STORE_COST THEN
      :NEW.LAST_UPDATE_DATE := SYSDATE;
  END IF;
  END;

Old New Column Variables