Download
triggers n.
Skip this Video
Loading SlideShow in 5 Seconds..
Triggers PowerPoint Presentation

Triggers

165 Views Download Presentation
Download Presentation

Triggers

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  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. Database Managment System Design

  3. Agenda • Roles of Triggers in database processing • Structure of a trigger • Semantic issues in trigger handling • Triggers in SQL • Syntax Of SQL:1999 Triggers • Syntax Of Triggers in DB2 Database Managment System Design

  4. Roles Of Triggers • Constraint maintenance • Business rules • Monitoring • Maintenance of auxiliary cached data • Simplified application design • Generating an attribute value Database Managment System Design

  5. Structure Of A Trigger --------------------------------------------------- ON event IF precondition THEN action --------------------------------------------------- • Triggers are called event-condition-action or ECA rules. Database Managment System Design

  6. Semantic Issues • Trigger consideration • Immediate • Deferred • Trigger execution • Before trigger • After trigger • Instead-of trigger Database Managment System Design

  7. Semantic Issues • Trigger granularity • Row-level granularity • Statement-level granularity • Trigger conflicts • Ordered conflict resolution • Group conflict resolution • Triggers and integrity constraints Database Managment System Design

  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. Database Managment System Design

  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. Ordered—SQL:1999 assumes that all triggers are ordered and executed in some implementation-specific way. Database Managment System Design

  10. Triggers in SQL • Trigger consideration. Immediate—the preconditions of all triggers activated by an event are checked immediately when the event is requested. • Trigger execution. Immediate—execution can be either before or after the event. • Trigger granularity. Row-level and statement level are both available. Database Managment System Design

  11. Syntax Of SQL:1999 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-tuple] [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 Database Managment System Design

  12. Constraint Maintenance CREATE TRIGGER CrsChangeTrigger AFTER UPDATE OF CrsCode, Semester ON Transcript WHEN ( Grade ISNOTNULL) ROLLBACK Database Managment System Design

  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 > 0.05 * O.Salary) UPDATE Employee SET Salary = 1.05 * O.Salary WHERE Id = O.Id Database Managment System Design

  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 Database Managment System Design

  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-tuple] [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} Database Managment System Design

  16. Example: System Without Trigger Database Managment System Design

  17. Example: Creating Trigger Database Managment System Design

  18. Example: System With Trigger Database Managment System Design

  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 Database Managment System Design