18-sql - 1 SQL Triggers Views Indexes CPS 116 Introduction...

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 ¡ 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.

Page1 / 5

18-sql - 1 SQL Triggers Views Indexes CPS 116 Introduction...

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