This preview has intentionally blurred sections. Sign up to view the full version.View Full Document
Unformatted text preview: 1 SQL: Triggers, Views, Indexes CPS 116 Introduction to Database Systems 2 Announcements (Thu. Nov. 10) ¡ Homework #3 sample solution available next Tuesday ¡ Project milestone #2 due today! ¡ Homework #4 (last one) will be assigned in 12 days ¢ Good time to work on the extra credit and project 3 “Active” data ¡ Constraint enforcement: When an operation violates a constraint, abort the operation or try to “fix” data ¢ Example: enforcing referential integrity constraints ¢ Generalize to arbitrary constraints? ¡ Data monitoring: When something happens to the data, automatically execute some action ¢ Example: When price rises above $20 per share, sell ¢ Example: When enrollment is at the limit and more students try to register, email the instructor 4 Triggers ¡ A trigger is an event-condition-action (ECA) rule ¢ When event occurs, test condition; if condition is satisfied, execute action ¡ Example: ¢ Event: whenever there comes a new student… ¢ Condition: with GPA higher than 3.0… ¢ Action: then make him/her take CPS116! 5 Trigger example CREATE TRIGGER CPS116AutoRecruit AFTER INSERT ON Student REFERENCING NEW ROW AS newStudent FOR EACH ROW WHEN (newStudent.GPA > 3.0) INSERT INTO Enroll VALUES(newStudent.SID, ’CPS116’); Event Condition Action 6 Trigger options ¡ Possible events include: ¢ INSERT ON table ¢ DELETE ON table ¢ UPDATE [ OF column ] ON table ¡ Granularity—trigger can be activated: ¢ FOR EACH ROW modified ¢ FOR EACH STATEMENT that performs modification ¡ Timing—action can be executed: ¢ AFTER or BEFORE the triggering event ¢ INSTEAD OF the triggering event on views (more later) 2 7 Transition variables ¡ OLD ROW : the modified row before the triggering event ¡ NEW ROW : the modified row after the triggering event ¡ OLD TABLE : a hypothetical read-only table containing all modified rows before the triggering event ¡ NEW TABLE : a hypothetical table containing all modified rows after the triggering event ) Not all of them make sense all the time, e.g. ¢ AFTER INSERT statement-level triggers • Can use only NEW TABLE ¢ BEFORE DELETE row-level triggers • Can use only OLD ROW ¢ etc....
View Full Document
This document was uploaded on 01/17/2012.
- Spring '09