Oracle DB DML Trigger

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];

Line(s)
Description

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 procedure and trigger, for that matter) to have the same name. We recommend, however, that you adopt naming conventions to avoid the confusion that will result from this sharing of names.

2
Specifies if the trigger is to fire BEFORE or AFTER the statement or row is processed.

3
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.

4
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).
缺省的触发器类型是语句级的，但加了for each row之后就是行级的。
语句级的触发器对一个语句触发一次，行级触发器对语句涉及到的每一行都触发一次。

5
An optional WHEN clause that allows you to specify logic to avoid unnecessary execution of the trigger.

6
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.

9
Optional exception section. This section will trap and handle (or attempt to handle) any exceptions raised in the execution section only.

10
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.

1.trigger 不能含有 commit; 语句
2.缺省的触发器类型是语句级的，但加了for each row之后就是行级的。
语句级的触发器对一个语句触发一次，行级触发器对语句涉及到的每一行都触发一次