CREATE TRIGGER v14

Suggest edits

Name

CREATE TRIGGER — Define a simple trigger.

Synopsis

CREATE [ OR REPLACE ] TRIGGER <name>
  { BEFORE | AFTER | INSTEAD OF }
  { INSERT | UPDATE | DELETE | TRUNCATE }
      [ OR { INSERT | UPDATE | DELETE | TRUNCATE} ] [, ...]
    ON <table>
  [ REFERENCING { OLD AS <old> | NEW AS <new> } ...]
  [ FOR EACH ROW ]
  [ WHEN <condition> ]
  [ DECLARE
      [ PRAGMA AUTONOMOUS_TRANSACTION; ]
      <declaration>; [, ...] ]
    BEGIN
      <statement>; [, ...]
  [ EXCEPTION
    { WHEN <exception> [ OR <exception> ] [...] THEN
        <statement>; [, ...] } [, ...]
  ]
    END

Name

CREATE TRIGGER — Define a compound trigger.

Synopsis

CREATE [ OR REPLACE ] TRIGGER <name>
  FOR { INSERT | UPDATE | DELETE | TRUNCATE }
        [ OR { INSERT | UPDATE | DELETE | TRUNCATE } ] [, ...]
           ON <table>
       [ REFERENCING { OLD AS <old> | NEW AS <new> } ...]
       [ WHEN <condition> ]
       COMPOUND TRIGGER
       [ <private_declaration>; ] ...
       [ <procedure_or_function_definition> ] ...
       <compound_trigger_definition>
        END

Where private_declaration is an identifier of a private variable that any procedure or function can access. There can be zero, one, or more private variables. private_declaration can be any of the following:

  • Variable declaration
  • Record declaration
  • Collection declaration
  • REF CURSOR and cursor variable declaration
  • TYPE definitions for records, collections, and REF CURSOR cursors
  • Exception
  • Object variable declaration

Where procedure_or_function_definition :=

procedure_definition | function_definition

Where procedure_definition :=

PROCEDURE proc_name[ argument_list ]
  [ options_list ]
  { IS | AS }
    procedure_body
  END [ proc_name ];

Where procedure_body :=

[ declaration; ] [, ...]
BEGIN
  statement; [...]
[ EXCEPTION
   { WHEN exception [OR exception] [...]] THEN statement; }
   [...]
]

Where function_definition :=

FUNCTION func_name [ argument_list ]
  RETURN rettype [ DETERMINISTIC ]
  [ options_list ]
  { IS | AS }
    function_body
  END [ func_name ] ;

Where function_body :=

[ declaration; ] [, ...]
BEGIN
  statement; [...]
[ EXCEPTION
  { WHEN exception [ OR exception ] [...] THEN statement; }
  [...]
]

Where compound_trigger_definition :=

{ compound_trigger_event } { IS | AS }
  compound_trigger_body
END [ compound_trigger_event ] [ ... ]

Where compound_trigger_event :=

[ BEFORE STATEMENT | BEFORE EACH ROW | AFTER EACH ROW | AFTER STATEMENT  | INSTEAD OF EACH ROW ]

Where compound_trigger_body :=

[ declaration; ] [, ...]
BEGIN
  statement; [...]
[ EXCEPTION
   { WHEN exception [OR exception] [...] THEN statement; }
   [...]
]

Description

CREATE TRIGGER defines a new trigger. CREATE OR REPLACE TRIGGER either creates a trigger or replaces an existing definition.

If you're using the CREATE TRIGGER keywords to create a new trigger, the name of the new trigger can't match any existing trigger defined on the same table. Triggers are created in the same schema as the table where the triggering event is defined.

If you're updating the definition of an existing trigger, use the CREATE OR REPLACE TRIGGER keywords.

When you use syntax that's compatible with Oracle to create a trigger, the trigger runs as a SECURITY DEFINER function.

Parameters

name

The name of the trigger to create.

BEFORE | AFTER

Determines whether the trigger is fired before or after the triggering event.

INSTEAD OF

Modifies an updatable view. The trigger executes to update the underlying tables appropriately. The INSTEAD OF trigger executes for each row of the view that's updated or modified.

INSERT | UPDATE | DELETE | TRUNCATE

Defines the triggering event.

table

The name of the table or view on which the triggering event occurs.

condition

A Boolean expression that determines if the trigger executes. If condition evaluates to TRUE, the trigger fires.

  • If the simple trigger definition includes the FOR EACH ROW keywords, the WHEN clause can refer to columns of the old or new row values by writing OLD.column_name or NEW.column_name. INSERT triggers can't refer to OLD, and DELETE triggers can't refer to NEW.

  • If the compound trigger definition includes a statement-level trigger having a WHEN clause, then the trigger executes without evaluating the expression in the WHEN clause. Similarly, if a compound trigger definition includes a row-level trigger having a WHEN clause, then the trigger executes if the expression evaluates to TRUE.

  • If the trigger includes the INSTEAD OF keywords, it can't include a WHEN clause. A WHEN clause can't contain subqueries.

REFERENCING { OLD AS old | NEW AS new } ...

REFERENCING clause to reference old rows and new rows. This clause is restricted in that you can replace old only with an identifier named old or any equivalent that's saved in all lowercase. Examples include REFERENCING OLD AS old, REFERENCING OLD AS OLD, and REFERENCING OLD AS "old". Also, you can replace new only with an identifier named new or any equivalent that's saved in all lowercase. Examples include REFERENCING NEW AS new, REFERENCING NEW AS NEW, and REFERENCING NEW AS "new".

You can specify one or both phrases OLD AS old and NEW AS new in the REFERENCING clause, such as REFERENCING NEW AS New OLD AS Old.

This clause isn't compatible with Oracle databases in that you can't use identifiers other than old or new.

FOR EACH ROW

Determines whether to fire the trigger once for every row affected by the triggering event or just once per SQL statement. If you specify this parameter, the trigger is fired once for every affected row (row-level trigger). Otherwise the trigger is a statement-level trigger.

PRAGMA AUTONOMOUS_TRANSACTION

PRAGMA AUTONOMOUS_TRANSACTION is the directive that sets the trigger as an autonomous transaction.

declaration

A variable, type, REF CURSOR, or subprogram declaration. If you include subprogram declarations, declare them after all other variable, type, and REF CURSOR declarations.

statement

An SPL program statement. A DECLARE - BEGIN - END block is considered an SPL statement. Thus, the trigger body can contain nested blocks.

exception

An exception condition name, such as NO_DATA_FOUND and OTHERS.

Examples

This example shows a statement-level trigger that fires after the triggering statement (insert, update, or delete on table emp) executes.

CREATE OR REPLACE TRIGGER user_audit_trig
    AFTER INSERT OR UPDATE OR DELETE ON emp
DECLARE
    v_action        VARCHAR2(24);
BEGIN
    IF INSERTING THEN
        v_action := ' added employee(s) on ';
    ELSIF UPDATING THEN
        v_action := ' updated employee(s) on ';
    ELSIF DELETING THEN
        v_action := ' deleted employee(s) on ';
    END IF;
    DBMS_OUTPUT.PUT_LINE('User ' || USER || v_action ||
        TO_CHAR(SYSDATE,'YYYY-MM-DD'));
END;

This example is a row-level trigger that fires before each row is either inserted, updated, or deleted on table emp.

CREATE OR REPLACE TRIGGER emp_sal_trig
    BEFORE DELETE OR INSERT OR UPDATE ON emp
    FOR EACH ROW
DECLARE
    sal_diff       NUMBER;
BEGIN
    IF INSERTING THEN
        DBMS_OUTPUT.PUT_LINE('Inserting employee ' || :NEW.empno);
        DBMS_OUTPUT.PUT_LINE('..New salary: ' || :NEW.sal);
    END IF;
    IF UPDATING THEN
        sal_diff := :NEW.sal - :OLD.sal;
        DBMS_OUTPUT.PUT_LINE('Updating employee ' || :OLD.empno);
        DBMS_OUTPUT.PUT_LINE('..Old salary: ' || :OLD.sal);
        DBMS_OUTPUT.PUT_LINE('..New salary: ' || :NEW.sal);
        DBMS_OUTPUT.PUT_LINE('..Raise     : ' || sal_diff);
    END IF;
    IF DELETING THEN
        DBMS_OUTPUT.PUT_LINE('Deleting employee ' || :OLD.empno);
        DBMS_OUTPUT.PUT_LINE('..Old salary: ' || :OLD.sal);
    END IF;
END;

This example shows a compound trigger that records a change to the employee salary by defining a compound trigger hr_trigger on table emp.

Create a table named emp:

CREATE TABLE emp(EMPNO INT, ENAME TEXT, SAL INT, DEPTNO INT);
CREATE TABLE

Create a compound trigger named hr_trigger. The trigger uses each of the four timing-points to modify the salary with an INSERT, UPDATE, or DELETE statement. In the global declaration section, the initial salary is declared as 10,000.

CREATE OR REPLACE TRIGGER hr_trigger
  FOR INSERT OR UPDATE OR DELETE ON emp
    COMPOUND TRIGGER
  -- Global declaration.
  var_sal NUMBER := 10000;

  BEFORE STATEMENT IS
  BEGIN
    var_sal := var_sal + 1000;
    DBMS_OUTPUT.PUT_LINE('Before Statement: ' || var_sal);
  END BEFORE STATEMENT;

  BEFORE EACH ROW IS
  BEGIN
    var_sal := var_sal + 1000;
    DBMS_OUTPUT.PUT_LINE('Before Each Row: ' || var_sal);
  END BEFORE EACH ROW;

  AFTER EACH ROW IS
  BEGIN
    var_sal := var_sal + 1000;
    DBMS_OUTPUT.PUT_LINE('After Each Row: ' || var_sal);
  END AFTER EACH ROW;

  AFTER STATEMENT IS
  BEGIN
    var_sal := var_sal + 1000;
    DBMS_OUTPUT.PUT_LINE('After Statement: ' || var_sal);
  END AFTER STATEMENT;

END hr_trigger;

Output: Trigger created.

INSERT the record into table emp.

INSERT INTO emp (EMPNO, ENAME, SAL, DEPTNO) VALUES(1111,'SMITH', 10000, 20);

The INSERT statement produces the following output:

Before Statement: 11000
Before each row: 12000
After each row: 13000
After statement: 14000
INSERT 0 1

The UPDATE statement updates the employee salary record, setting the salary to 15000 for a specific employee number:

UPDATE emp SET SAL = 15000 where EMPNO = 1111;

The UPDATE statement produces the following output:

Before Statement: 11000
Before each row: 12000
After each row: 13000
After statement: 14000
UPDATE 1

SELECT * FROM emp;
 EMPNO | ENAME |  SAL  | DEPTNO
-------+-------+-------+--------
  1111 | SMITH | 15000 |     20
(1 row)

The DELETE statement deletes the employee salary record:

DELETE from emp where EMPNO = 1111;

The DELETE statement produces the following output:

Before Statement: 11000
Before each row: 12000
After each row: 13000
After statement: 14000
DELETE 1

SELECT * FROM emp;
 EMPNO | ENAME | SAL | DEPTNO
-------+-------+-----+--------
(0 rows)

The TRUNCATE statement removes all the records from the emp table:

CREATE OR REPLACE TRIGGER hr_trigger
  FOR TRUNCATE ON emp
    COMPOUND TRIGGER
  -- Global declaration.
  var_sal NUMBER := 10000;
  BEFORE STATEMENT IS
  BEGIN
    var_sal := var_sal + 1000;
    DBMS_OUTPUT.PUT_LINE('Before Statement: ' || var_sal);
  END BEFORE STATEMENT;

  AFTER STATEMENT IS
  BEGIN
    var_sal := var_sal + 1000;
    DBMS_OUTPUT.PUT_LINE('After Statement: ' || var_sal);
  END AFTER STATEMENT;

END hr_trigger;

Output: Trigger created.

The TRUNCATE statement produces the following output:

TRUNCATE emp;
Before Statement: 11000
After statement: 12000
TRUNCATE TABLE
Note

You can use the TRUNCATE statement only at a BEFORE STATEMENT or AFTER STATEMENT timing point.

This example creates a compound trigger named hr_trigger on the emp table. It is a WHEN condition that checks and prints employee salary whenever an INSERT, UPDATE, or DELETE statement affects the emp table. The database evaluates the WHEN condition for a row-level trigger, and the trigger executes once per row if the WHEN condition evaluates to TRUE. The statement-level trigger executes regardless of the WHEN condition.

CREATE OR REPLACE TRIGGER hr_trigger
  FOR INSERT OR UPDATE OR DELETE ON emp
  REFERENCING NEW AS new OLD AS old
  WHEN (old.sal > 5000 OR new.sal < 8000)
    COMPOUND TRIGGER

  BEFORE STATEMENT IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Before Statement');
  END BEFORE STATEMENT;

  BEFORE EACH ROW IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Before Each Row: ' || :OLD.sal ||' ' || :NEW.sal);
  END BEFORE EACH ROW;

  AFTER EACH ROW IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('After Each Row: ' || :OLD.sal ||' ' || :NEW.sal);
  END AFTER EACH ROW;

  AFTER STATEMENT IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('After Statement');
  END AFTER STATEMENT;

END hr_trigger;

Insert the record into table emp:

INSERT INTO emp(EMPNO, ENAME, SAL, DEPTNO) VALUES(1111, 'SMITH', 1600, 20);

The INSERT statement produces the following output:

Before Statement
Before Each Row: 1600
After Each Row: 1600
After Statement
INSERT 0 1

The UPDATE statement updates the employee salary record, setting the salary to 7500:

UPDATE emp SET SAL = 7500 where EMPNO = 1111;

The UPDATE statement produces the following output:

Before Statement
Before Each Row: 1600 7500
After Each Row: 1600 7500
After Statement
UPDATE 1

SELECT * from emp;
 empno | ename | sal  | deptno
-------+-------+------+--------
  1111 | SMITH | 7500 |     20
(1 row)

The DELETE statement deletes the employee salary record:

DELETE from emp where EMPNO = 1111;

The DELETE statement produces the following output:

Before Statement
Before Each Row: 7500
After Each Row: 7500
After Statement
DELETE 1

SELECT * from emp;
 empno | ename | sal | deptno
-------+-------+-----+--------
(0 rows)

See also

ALTER TRIGGER, DROP TRIGGER

← Prev

CREATE TABLE AS

↑ Up

Database Compatibility for Oracle Developers: SQL

Next →

CREATE TYPE