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:
| Line(s) | Description |
| 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. | |
| Specifies if the trigger is to fire BEFORE or AFTER the statement or row is processed. | |
| Specifies the type of DML 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. | |
| If 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). | |
| An optional WHEN clause that allows you to specify logic to avoid unnecessary execution of the trigger. | |
| Optional 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 pseudo-records. This is done automatically. | |
| 7-8 | The execution section of the trigger. This is required and must contain at least one statement. |
| Optional exception section. This section will trap and handle (or attempt to handle) any exceptions raised in the execution section only. | |
| Required 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. |
Here are a few examples of DML trigger usage:
I want to make sure that whenever an employee is added or changed, all necessary validation is run. Notice that I pass the necessary fields of the NEW pseudo-record to individual check routines in this row-level trigger:
CREATE OR REPLACE TRIGGER validate_employee_changes AFTER INSERT OR UPDATE ON employee FOR EACH ROW BEGIN check_age (:NEW.date_of_birth); check_resume (:NEW.resume);END;The following BEFORE INSERT trigger captures audit information for the CEO compensation table. It also relies on the Oracle8i autonomous transaction feature to commit this new row without affecting the "outer" or main transaction:
CREATE OR REPLACE TRIGGER bef_ins_ceo_comp AFTER INSERT ON ceo_compensation FOR EACH ROW DECLARE PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO ceo_comp_history VALUES (:NEW.name, :OLD.compensation,:NEW.compensation, 'AFTER INSERT', SYSDATE); COMMIT;END;
