Name
CREATE TRIGGER
Synopsis
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.
Keywords
- OR REPLACE
Specifies that if the trigger already exists, it is to be replaced.
- BEFORE
Specifies that this trigger is to be fired before executing the triggering statement.
- AFTER
Specifies that this trigger is to be fired after executing the triggering statement.
- INSTEAD OF
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.
- INSERT
Specifies that this trigger is to be fired whenever an INSERT statement adds a row to the table.
- DELETE
Specifies that this trigger is to be fired whenever a DELETE statement removes a row from the table.
- UPDATE
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.
- ON
Specifies the name of the table (table_name) and optional schema on which the trigger is to be created.
- REFERENCING
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.
- FOR EACH ROW
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.
- WHEN condition
Specifies a SQL condition that must be true in order to fire the trigger.
- pl/sql_block
The PL/SQL block that will be executed when the trigger fires. This block may not contain COMMIT, ROLLBACK, or SAVEPOINT commands.
Notes
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.
Example
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;