18-sql - Announcements(Thu Nov 10 SQL Triggers Views...

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

View Full Document Right Arrow Icon
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: INSERTON table DELETEON table UPDATE [ OF column ] ON table Granularity—trigger can be activated: FOREACHROW modified FOREACHSTATEMENT that performs modification Timing—action can be executed: AFTER or BEFORE the triggering event INSTEADOF the triggering event on views (more later)
Image of page 1

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

View Full Document Right Arrow Icon
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. AFTERINSERT statement-level triggers • Can use only NEW TABLE BEFOREDELETE row-level triggers • Can use only OLD ROW etc.
Image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern