1 / 19

Triggers

Triggers. Database Management System Design. Saba Aamir Computing and Software McMaster University. Introduction. A trigger is a statement that the system executes automatically as a side effect of a modification to the database.

efia
Download Presentation

Triggers

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

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

More Related