18-sql

18-sql - 1 SQL: Triggers, Views, Indexes CPS 116...

Info iconThis preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
This is the end of the preview. Sign up to access the rest of the 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 Granularitytrigger can be activated: FOR EACH ROW modified FOR EACH STATEMENT that performs modification Timingaction 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

Page1 / 5

18-sql - 1 SQL: Triggers, Views, Indexes CPS 116...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online