?> Old/new PL/SQL column variables[Diagram]

Creating Triggers  «Prev 

Old and new column variables in PL/SQL

Old and new column variables 
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;

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

Creating a DML Trigger

To create (or replace) a DML trigger, use the syntax shown here: 
1 CREATE [OR REPLACE] TRIGGER trigger_name
2 {BEFORE | AFTER}
3 {INSERT | DELETE | UPDATE | UPDATE OF column_list } ON table_name
4 [FOR EACH ROW]
5 [WHEN (...)]
6 [DECLARE ... ]
7 BEGIN
8 ...executable statements...
9 [EXCEPTION ... ]
10 END [trigger_name];

The following table provides an explanation of these different elements:
LineDescription
1 States that a trigger is to be created with the name supplied. Specifying OR REPLACE is optional. If the trigger exists and REPLACE is not specified, then your attempt to create the trigger anew will result in an ORA-4081 error. It is possible, by the way, for a table and a trigger (or a procedure and a trigger, for that matter) to have the same name.I recommend, however, that you adopt naming conventions to avoid the confusion that will result from this sharing of names.
2Specifies if the trigger is to fire BEFORE or AFTER the statement or row is processed.
3Specifies the combination of DML types to which the trigger applies: insert, update, or delete. Note that UPDATE can be specified for the whole record or just for a column list separated by commas. The columns can be combined (separated with an OR) and may be specified in any order. Line 3 also specifies the table to which the trigger is to apply. Remember that each DML trigger can apply to only one table.
4If FOR EACH ROW is specified, then the trigger will activate for each row processed by a statement. If this clause is missing, the default behavior is to fire only once for the statement (a statement-level trigger).
5An optional WHEN clause that allows you to specify logic to avoid unnecessary execution of the trigger.
6Optional declaration section for the anonymous block that constitutes the trigger code. If you do not need to declare local variables, you do not need this keyword. Note that you should never try to declare the NEW and OLD pseudorecords.This is done automatically.
7-8The execution section of the trigger. This is required and must contain at least one statement.
9Optional exception section. This section will trap and handle (or attempt to handle) any exceptions raised in the execution section only.
10Required END statement for the trigger. You can include the name of the trigger after the END keyword to explicitly document which trigger you are ending.