Compound Triggers v12

Suggest edits

The following example of a compound trigger records a change to the employee salary by defining a compound trigger (named hr_trigger) on the emp table.

First, create a table named emp.

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

Then, create a compound trigger named hr_trigger. The trigger is created for each of the four timing-points to INSERT, UPDATE, or DELETE salary in the emp table. In the global declaration section, the 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 will update 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

The TRUNCATE statement may be used only at a BEFORE STATEMENT or AFTER STATEMENT timing-point.

← Prev

INSTEAD OF Trigger

Next →

Packages