18-sql-notes

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

Info iconThis preview shows pages 1–4. 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

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 2 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 for triggers on views (more later) 3 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....
View Full Document

Page1 / 9

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

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

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