Triggers

1
Triggers

• Database Management System Design

Saba Aamir
Computing and Software McMaster
University
2
Introduction

• A trigger is a statement that the system executes
  automatically as a side effect of a modification
  to the database.
• Triggers define a set of actions that are
  executed by a delete, insert or update operation
  on a specified table.

3
Agenda

• Roles of Triggers in database processing
• Structure of a trigger
• Semantic issues in trigger handling
• Triggers in SQL
• Syntax Of SQL1999 Triggers
• Syntax Of Triggers in DB2

4
Roles Of Triggers

• Constraint maintenance
• Business rules
• Monitoring
• Maintenance of auxiliary cached data
• Simplified application design
• Generating an attribute value

5
Structure Of A Trigger

• --------------------------------------------------
  -
• ON event IF precondition THEN action
• --------------------------------------------------
  -
• Triggers are called event-condition-action or ECA
  rules.

6
Semantic Issues

• Trigger consideration
• Immediate
• Deferred
• Trigger execution
• Before trigger
• After trigger
• Instead-of trigger

7
Semantic Issues

• Trigger granularity
• Row-level granularity
• Statement-level granularity
• Trigger conflicts
• Ordered conflict resolution
• Group conflict resolution
• Triggers and integrity constraints

8
Triggers in SQL

• Triggering events. An event can be the execution
  of an SQL INSERT, DELETE and UPDATE statement as
  a whole or a change to individual rows made by
  such statements.
• Trigger precondition. Any condition allowed in
  the where clause of SQL.

9
Triggers in SQL

• Triggering action. An SQL query, a Delete,
  INSERET, UPDATE, ROLLBACK or SIGNAL statement or
  a program in SQL/PSM.
• Trigger conflict resolution. OrderedSQL1999
  assumes that all triggers are ordered and
  executed in some implementation-specific way.

10
Triggers in SQL

• Trigger consideration. Immediatethe
  preconditions of all triggers activated by an
  event are checked immediately when the event is
  requested.
• Trigger execution. Immediateexecution can be
  either before or after the event.
• Trigger granularity. Row-level and statement
  level are both available.

11
Syntax Of SQL1999 Triggers

• CREATE TRIGGER trigger-name
• BEFORE AFTER
• INSERT DELETE UPDATE OF
  column-name-list
• ON table-name
• REFERENCING OLD AS var-to-refer-to-old-tup
  le
• NEW AS
  var-to-refer-to-new-tuple
• OLD TABLE AS
  name-to-refer-to-old-table
• NEW TABLE AS
  name-to-refer-to-new-table
• FOR EACH ROW STATEMENT
• WHEN (precondition)
• statement-list

12
Constraint Maintenance

• CREATE TRIGGER CrsChangeTrigger
• AFTER UPDATE OF CrsCode, Semester
• ON Transcript
• WHEN ( Grade ISNOTNULL)
• ROLLBACK

13
Business Rule

• CREATE TRIGGER LimitSalaryRaise
• AFTER UPDATE OF Salary ON Employee
• REFERENCING OLD AS O
• NEW AS N
• FOR EACH ROW
• WHEN (N.Salary O.Salary gt 0.05 O.Salary)
• UPDATE Employee
• SET Salary 1.05 O.Salary
• WHERE Id O.Id

14
Generating An Attribute Value

• CREATE TRIGGER SetValue
• BEFORE UPDATE ON r
• REFERENCING NEW ROW AS nrow
• FOR EACH ROW
• WHEN nrow.phone-number
• SET nrow.phone-number NULL

15
Syntax Of Triggers In DB2

• CREATE TRIGGER trigger-name
• NO CASCADE BEFORE AFTER
• INSERT DELETE UPDATE OF
  column-name-list
• ON table-name
• REFERENCING OLD AS var-to-refer-to-old-tup
  le
• NEW AS
  var-to-refer-to-new-tuple
• OLD_TABLE AS
  name-to-refer-to-old-table
• NEW_TABLE AS
  name-to-refer-to-new-table
• FOR EACH ROW STATEMENT MODE DB2SQL
• WHEN (precondition)
• triggered-SQL-statement
• BEGIN ATOMIC triggered-SQL-statement-list
  END

16
Example System Without Trigger
17
Example Creating Trigger
18
Example System With Trigger
19
References

• Database Systems An Application-Oriented
  Approach by Kifer, Bernstein, Lewis
• Database System Concepts by Silberschatz, Korth,
  Sudarshan
• Active Rules in Database Systems by Paton
• DB2 Online Manual SQL Reference, Volume 1 and 2