Database Triggers

1
Database Triggers

• Carl Dudley
• Staffordshire University, UK

2
Database Triggers

• Pieces of code (PL/SQL blocks) which are
  activated (fired) by a triggering
• statement
• Each trigger applies only to one base table
  (or view)
• Consists of four parts
• Trigger type
• Triggering Event
• Trigger Restriction (applicable to ROW level
  triggers)
• Trigger action (body)

3
Structure of Database Triggers (DML)

• Trigger Type
• Triggers based on tables
• Before Statement
• After Statement
• Before (each) Row
• After (each) Row
• Triggers based on views
• INSTEAD OF
• Triggering Event - INSERT UPDATE DELETE
• Trigger Body
• Written in PL/SQL

4
Structure of Database Triggers (continued)

• Triggering Restriction
• Conditions governing which rows fire the trigger
• Condition is placed in the WHEN clause before the
  body of the trigger

CREATE OR REPLACE TRIGGER test AFTER INSERT ON
emp FOR EACH ROW WHEN (NEW.deptno ltgt 30) BEGIN
END /
5
Database Triggers Example1

• CREATE OR REPLACE TRIGGER ex_1
• BEFORE INSERT OR DELETE OR UPDATE OF sal ON emp
• FOR EACH ROW
• WHEN (NEW.job CLERK OR NEW.job CAHIER)
• DECLARE
• deptname VARCHAR2(20)
• average_sal NUMBER(7,2)
• salary_out_of_range EXCEPTION
• minsal NUMBER
• maxsal NUMBER
• BEGIN
• SELECT MIN(sal),MAX(sal) INTO minsal,maxsal
• FROM emp WHERE emp.job NEW.job
• IF (NEW.sal lt minsal OR NEW.sal gt maxsal) THEN
• RAISE salary_out_of_range
• END IF
• INSERT INTO emp_audit VALUES (NEW.empno,NEW.sal
  ,sysdate)
• EXCEPTION
• WHEN salary_out_of_range THEN

6
Database Triggers Example2

• CREATE OR REPLACE TRIGGER ex_2
• AFTER INSERT OR DELETE OR UPDATE OF deptno,sal ON
  emp
• FOR EACH ROW
• BEGIN
• IF DELETING OR UPDATING (deptno) THEN
• UPDATE dept SET totsal NVL(totsal,0) -
  NVL(OLD.sal)
• WHERE deptno OLD.deptno
• END IF
• IF INSERTING OR UPDATING(deptno) THEN
• UPDATE dept SET totsal NVL(totsal,0)
  NVL(NEW.sal,0)
• WHERE deptno NEW.deptno
• END IF
• IF UPDATING (sal) THEN
• UPDATE dept SET totsal
• NVL(totsal,0) NVL(NEW.sal,0) -
  NVL(OLD.sal,0)
• WHERE deptno NEW.deptno
• END IF
• END
• /

Dept DEPTNO DNAME TOTSAL ------ --------
------ 10 RESEARCH 2500 20 ACCOUNTS
3000 30 FINANCE 3300 40 SALES 1000

• This trigger maintains a derived salary total

7
Database Triggers vs. Declarative Constraints

• Triggers can handle more complex business rules
• Security authorisations
• Prevention of invalid transactions
• auditing mechanisms
• Transparent event logging
• Maintenance of derived data values stored in the
  database
• Less efficient than declarative constraints
• Can be used to supply comprehensive integrity
  checking
• But not simply to supply the missing
  referential constraints which can not be
  implemented via declarative constraints
• Can not perform retrospective checking
• Required for synchronous table replication across
  sites

8
Database Triggers vs. Client Side Code (Forms
Triggers)

• Database triggers
• Apply to all applications which use the tables on
  which they are based
• Fire only when a commit takes place
• Cannot give immediate feedback on errors
• Fire independently of application code
• Fired by any tool which issues DML against the
  table
• Maintenance of derived data values stored in the
  database
• Application code on the client
• Immediate error checking
• Only apply to one form (application)
• Can give very fine and detailed control
• Execute only when application is run

9
Database Triggers Usage

• Integrity
• Behaviour
• Derivation
• Dependency
• Audit (value based)
• Replication

10
Database Triggers - Characteristics

• Developed as a DBA tool to enhance database
  management and improve security
• Also a facility to enhance application
  development (reduction of code in an application)
• Written in PL/SQL or Transact SQL
• Part of SQL99
• DML triggers may fire before and after statements
  or each row update
• Fire on insert, update or delete activity
• Gives a total of twelve possible trigger types
  per table
• Most DBMSs allow multiple triggers of the same
  type on a single table
• A trigger fires with the privileges of the owner,
  not the user

11
Controlling Triggers

• ALTER TRIGGER trig1 DISABLE
• ALTER TRIGGER trig1 ENABLE
• ALTER TABLE emp DISABLE ALL TRIGGERS
• DROP TRIGGER trig2
• DROP TABLE emp
• This drops all triggers
• TRUNCATE does not drop triggers
• Triggers are automatically enabled on creation

12
Trigger Firing Sequence
Before statement trigger fires
ENAME DEPTNO CLARK 10 SCOTT
20 TURNER 30
Before row trigger fires
After row trigger fires
Before row trigger fires
After row trigger fires
After statement trigger fires

• NEW and OLD values are available only in row
  level triggers

13
Cascading Updates

• Updates can not be cascaded into child tables
  (foreign key columns)
• using referential integrity constraints
• A simple trigger MIGHT suffice

CREATE OR REPLACE TRIGGER emp_dept AFTER UPDATE
ON dept FOR EACH ROW BEGIN UPDATE emp SET deptno
NEW.deptno WHERE emp.deptno
OLD.deptno END /

• But there are problems with certain updates

UPDATE dept SET deptno deptno 10
14
Cascading Updates (continued)

• Result of update on previous slide
• Employee rows suffer multiple updates
• All employees are now in department 50

emp
dept
EMPNO ENAME DEPTNO ----- ------ ------ 7369
SMITH 50 7499 ALLEN 50 7521 WARD
50 7566 JONES 50 7654 MARTIN 50 7698
BLAKE 50 7782 SCOTT 50

DEPTNO DNAME ------ ---------- 20
ACCOUNTING 30 RESEARCH 40 SALES 50
OPERATIONS
15
Cascading Updates (continued)

• The following update would give an expected
  result

UPDATE dept SET deptno deptno 1
DEPTNO DNAME ------ ---------- 11
ACCOUNTING 21 RESEARCH 31 SALES 41
OPERATIONS
EMPNO ENAME DEPTNO ----- ------ ------ 7369
SMITH 21 7499 ALLEN 31 7521 WARD
31 7566 JONES 21 7654 MARTIN 31 7698
BLAKE 31 7782 SCOTT 11

16
Cascading Updates - an Explanation

• Consider situation after update of first
  department row (deptno 10)

DEPTNO DNAME ------ ---------- 20
ACCOUNTING 20 RESEARCH 30 SALES 40
OPERATIONS
EMPNO ENAME DEPTNO ----- ------ ------ 7369
SMITH 20 7499 ALLEN 30 7521 WARD
30 7566 JONES 20 7654 MARTIN 30 7698
BLAKE 30 7782 SCOTT 20

10
20

• Scott is now in department 20
• His/her department number will be updated
  again when deptno 20 is changed to 30

17
Cascading Updates (continued)

• If the rows in the department table happened
  to be stored differently

DEPTNO DNAME ------ ---------- 40
OPERATIONS 30 SALES 20 RESEARCH 10
ACCOUNTING

• The original update would give the expected
  result

UPDATE dept SET deptno deptno 10

• So, the order of storage of rows determines
  the output
• Contrary to basic rules of relational theory!

18
Cascading Updates - a way forward?

• Oracle documentation suggests the following
• 1 Generate a sequence number to use as a flag
  to prevent multiple updates on the same row
• 2 Alter the emp table - add a column which will
  contain the flag values
• 3 Create a trigger that tests and sets the flag
  before performing the cascade update
• Note Any use of triggers to perform
  referential actions or checks on a table,
  prevent the use of any referential constraint on
  that table
• What about SQL99
• A recognised problem
• Many proposals but no real solutions
• Still under debate

19
Maintaining Full Referential Integrity with
Triggers

• A simple (inelegant) solution is to create a
  temporary table to hold the new and old
  values of the primary key of the parent table

CREATE TABLE temp (oldkey NUMBER, newkey NUMBER)

• Create the following triggers
• 1. Before delete or update row trigger on dept

INSERT INTO temp VALUES (OLD.deptno,NEW.deptno)
2. After delete or update of deptno on dept
(statement level)
IF UPDATING THEN UPDATE emp e SET e.deptno
(SELECT newkey FROM temp WHERE e.deptno
oldkey) WHERE e.deptno (SELECT oldkey FROM
temp WHERE oldkey e.deptno IF DELETING THEN
DELETE FROM emp WHERE deptno IN (SELECT oldkey
FROM temp DELETE FROM temp

• 3. After insert or update of deptno row trigger
  on dept
• Uses NO_DATA_FOUND to check for existence of
  the new deptno in dept

SELECT deptno INTO dummy FROM dept WHERE deptno
NEW.deptno)
20
Visibility of Triggered Action

• Suppose we try to handle cascade update by
  preventing triggers seeing values produced
  during the trigger action
• Prevents multiple updates of the same row

x y --- --- 4 a 7 b
CREATE TABLE h1 (x NUMBER PRIMARY KEY,
y CHAR)

• Table h2 keeps a count of the rows in other
  tables

Tab_name row_count -------- --------- h1
2
CREATE TABLE h2 (tab_name CHAR PRIMARY KEY,
row_count NUMER NOT NULL)
CREATE OR REPLACE TRIGGER trg1 AFTER DELETE ON
h1 FOR EACH ROW BEGIN UPDATE h2 SET row_count
row_count - 1 WHERE tab_name h1 END

• Create a trigger to maintain the h2 table

• What will be the result of this statement
  if the trigger can not see the data produced
  by a previous action?

DELETE FROM h1 WHERE x gt 2
21
Triggers and Referential Integrity Constraints
x y --- --- 1 a 2 b
CREATE TABLE d1 (x NUMBER PRIMARY KEY,
y CHAR)
CREATE TABLE d2 (p CHAR, s
NUMBER REFERENCES d1(x) ON
DELETE CASCADE
p s --- --- a 1
CREATE OR REPLACE TRIGGER del_on_d1 BEFORE DELETE
ON d1 FOR EACH ROW BEGIN UPDATE d2 SET s s
1 WHERE d2.s OLD.x END

• Create a trigger which affects the foreign
  key

• This statement will produce a
• runtime table mutation error

DELETE FROM d1 WHERE y a
22
Triggers Conflicting with Integrity Constraints

• Oracle generates an error

ERROR at line 1 ORA-04088 error during
execution of trigger SCOTT.del_on_d1 ORA-06551
PL/SQL Unhandled exception ORA-06512 at line
2 ORA-04091 Scott.d2 is mutating, trigger may
not read or modify

• The mutating table error occurs only with row
  level triggers.
• What about statement level triggers?

CREATE OR REPLACE TRIGGER statement_d1 BEFORE
DELETE ON d1 BEGIN UPDATE d2 SET s s 1
WHERE d2.s OLD.x END

• Error because new and old values are not
  allowed in statement level triggers

23
Trigger Processing

• Fire BEFORE statement level trigger
• For each triggering row
• Fire BEFORE row level trigger
• Change row and check constraints for
  intermediate violations
• Fire AFTER row level trigger
• Fire AFTER statement level trigger
• Complete deferred constraint checking (check on
  any remaining violations)
• What would happen if the trigger itself updated
  the same table?
• At present, you must disable all referential
  constraints on tables
• which are affected by row level triggers having
  referential actions
• If update cascade is required, all referential
  integrity must be done by way of triggers

24
Cascade Delete and BEFORE/AFTER Triggers
CREATE OR REPLACE TRIGGER t_d1 BEFORE DELETE ON
d1 BEGIN UPDATE d2 SET s s 1 END
x y --- --- 2 b
p s --- --- a 2
DELETE FROM d1 WHERE y a

• If the trigger is changed to after statement,
  we see a rather different result

CREATE OR REPLACE TRIGGER t_d1 AFTER DELETE ON
d1 BEGIN UPDATE d2 SET s s 1 END
x y --- --- 2 b
No rows
DELETE FROM d1 WHERE y a
25
BEFORE and AFTER Triggers

• Normally have identical functionality
• BEFORE triggers have to read the data twice
• AFTER triggers have better performance

CREATE OR REPLACE TRIGGER after_test AFTER UPDATE
OF x ON d1 FOR EACH ROW BEGIN NEW.y 1 END

• Error at line 2
• ORA-04084 cannot change trigger NEW values in
  after row triggers
• Consider the equivalent BEFORE UPDATE trigger
• What would happen if it were fired by the
  following statement
• UPDATE t1 SET x 10, y 999
• The resultant value of y would actually be 1
  and not 999

26
BEFORE Row Level Trigger

• Useful because you can alter the values about to
  be produced/changed by the triggering statement

UPDATE emp SET sal sal 2 WHERE ename user

• This kind of change can be controlled with the
  following trigger

CREATE OR REPLACE TRIGGER limit_sal BEFORE UPDATE
OF sal ON emp FOR EACH ROW BEGIN IF NEW.sal gt
OLD.sal 1.1 THEN NEW.sal OLD.sal END
IF END

• Beware, this does not prevent repetitive
  updates winding up the salary value

27
Recursive Triggering
a b --- --- 20 8
CREATE TABLE rrr(a NUMBER, b NUMBER)
CREATE OR REPLACE TRIGGER recur AFTER UPDATE ON
rrr BEGIN UPDATE rrr SET a a 1 END
SQLgt UPDATE rrr SET a a 2

• This causes recursive firing of the trigger

• ERROR at line 1
• ORA-01000 maximum number of cursors exceeded
• ORA-06512 at line 2
• ORA-04088 error during execution of trigger
  SCOTT.RECUR
• ORA-06512 at line 2
• ORA-04088 error during execution of trigger
  SCOTT.RECUR

28
Recursive Triggering

• Replicated data in distributed databases is
  susceptible to recursive triggering
• Any change at any site must be triggered to the
  other sites
• Each site will require at least two triggers
• Techniques must be used to prevent recursion

London
New York
San Francisco
29
Triggering Statements Which Affect no Rows

• Statement level update/delete triggers fire
  normally even if the triggering statement affects
  no rows

X Y --- --- a 1 b 2
t1

• The following DML will still fire any appropriate
  statement level triggers on t1

DELETE FROM t1 WHERE x c
UPDATE t1 SET y 9999 WHERE y 3
UPDATE t1 SET y y
Also fires row level triggers
30
Triggering with Conterminous Psths
Table A
Row trigger (on A)
Row trigger (on A)
Table B
Row trigger (on B)
Table C

• Table C is in danger of suffering a double update
• Which update will arrive first?
• Does it matter?

31
Multiple Triggers of the Same Type on the Same
Table

• Originally not allowed
• Can lead to unsafe conditions
• Now allowed due to market pressure from 3rd party
  developers

CREATE OR REPLACE TRIGGER tr1 AFTER UPDATE ON
empproj BEGIN UPDATE emp SET sal sal 1000
WHERE eanme FORD END
CREATE OR REPLACE TRIGGER tr2 AFTER UPDATE ON
empproj BEGIN UPDATE emp SET sal sal 2
WHERE eanme FORD END

• Suppose original salary for FORD is 2000
• If tr1 fires before tr2, FORD has a salary of
  6000
• If tr2 fires before tr1, FORD has a salary of
  5000
• The order of firing is actually random !
• Recommended to sequence all trigger code in a
  single trigger
• Similar problems may occur with multi-table
  inserts and INSERT triggers

32
Additional Trigger Types

• Triggers can now fire on DDL events (statements)
• CREATE USER, TABLE, VIEW, SEQUENCE ....
• DROP
• ALTER
• TRUNCATE
• GRANT
• REVOKE
• DDL (fires on any DDL statement)
• Triggers can fire on database events
• LOGON
• LOGOFF
• STARTUP
• SHUTDOWN
• SERVERRERROR
• SUSPEND