CREATE TRIGGER
CREATE [OR REPLACE] TRIGGER [schema
.]triggername
{ BEFORE {INSERT | DELETE | UPDATE [OFcolumn
[,column
...]} | AFTER {INSERT | DELETE | UPDATE [OFcolumn
[,column
...]} | INSTEAD OF {INSERT | DELETE | UPDATE [OFcolumn
[,column
...]} | } ON [schema
.]table_name
[REFERENCING {OLD [AS]old
| NEW [AS]new
} [FOR EACH ROW [WHEN (condition
)]]
pl/sql_block
Creates a trigger (trigger_name), a stored PL/SQL block associated with a table that is automatically executed when a particular SQL statement is executed against that table.
Specifies that if the trigger already exists, it is to be replaced.
Specifies that this trigger is to be fired before executing the triggering statement.
Specifies that this trigger is to be fired after executing the triggering statement.
Specifies that this trigger is to fire instead of the INSERT, UPDATE, or DELETE statement. Use this construct when you want to modify a view that is not normally modifiable.
Specifies that this trigger is to be fired whenever an INSERT statement adds a row to the table.
Specifies that this trigger is to be fired whenever a DELETE statement removes a row from the table.
Specifies that this trigger is to be fired whenever an UPDATE statement changes the value in one of the columns specified in the OF clause. If the OF clause is omitted, an UPDATE to any column will cause the trigger to fire.
Specifies the name of the table (table_name) and optional schema on which the trigger is to be created.
Specifies correlation names, which allow the PL/SQL block to refer to old and new values of the current row. The default values are OLD as old and NEW as new.
Specifies that this trigger is to be a row trigger fired once for each row that is affected by the triggering mechanism and that meets the conditions of the WHEN clause.
Specifies a SQL condition that must be true in order to fire the trigger.
The PL/SQL block that will be executed when the trigger fires. This block may not contain COMMIT, ROLLBACK, or SAVEPOINT commands.
You must have the CREATE TRIGGER privilege to create a trigger on a table in your own schema, and you must have the CREATE ANY TRIGGER privilege to create a trigger on a table in any other schema. The SQL script dbmsstdx.sql must have been run by SYS prior to issuing this command.
The following example creates a trigger that inserts a row into a table each time an INSERT, DELETE, or UPDATE performed on scott’s emp table results in a salary increase:
CREATE TRIGGER scott.empaud BEFORE INSERT OR UPDATE OF sal ON scott.emp FOR EACH ROW WHEN (new.sal <> old.sal) DECLARE empno number(6); oldsal number(7.2); newsal number(7.2); BEGIN /* First get the old salary and empno */ SELECT empno,sal INTO empno,oldsal FROM scott.emp WHERE empno = :old.empno; /* Now write the record */ INSERT INTO track_sal_changes VALUES (empno,oldsal); END;