1SQL: Triggers, Views, IndexesCPS 116Introduction to Database Systems2Announcements (October 30) Homework #3 due next Thursday Project milestone #2 due in 1½ weeks3“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 instructor4Triggers 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!5Trigger exampleCREATE TRIGGER CPS116AutoRecruitAFTER INSERT ON StudentREFERENCING NEW ROW AS newStudentFOR EACH ROWWHEN ( St d t GPA 3 0)EventCdiiWHEN (newStudent.GPA > 3.0)INSERT INTO EnrollVALUES(newStudent.SID, ’CPS116’);ConditionAction6Trigger 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 event27Transition 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 :ypo c bco g oddrows 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. 8Statement-level trigger exampleCREATE TRIGGER CPS116AutoRecruitAFTER INSERT ON StudentREFERENCING NEW TABLE AS newStudentsFOR EACH STATEMENTINSERT INTO Enroll(SELECT SID, ’CPS116’FROM newStudentsWHERE GPA > 3.0);9BEFORE trigger example Never give faculty more than 50% raise in one updateCREATE TRIGGER NotTooGreedyBEFORE UPDATE OF salary ON FacultyREFERENCING OLD ROW AS o, NEW ROW AS nFOR EACH ROWFOR EACH ROWWHEN (n.salary > 1.5 * o.salary)SET n.salary = 1.5 * o.salary;) BEFORE triggers are often used to “condition” data) Another option is to raise an error in the trigger body to abort the transaction that caused the trigger to fire10Statement- vs. row-level triggersWhy are both needed? Certain triggers are only possible at statement level If the average GPA of students inserted by this statement exceeds 3.0, do …Si lllti itil tSimple row-level triggers are easier to implement Statement-level triggers require significant amount of state to be maintained in OLD TABLE and NEW TABLE However, a row-level trigger does get fired for each row, so complex row-level triggers may be inefficient for statements that generate lots of modifications11Another statement-level trigger Give faculty a raise if GPA’s in one update statement are all increasingCREATE TRIGGER AutoRaiseAFTER UPDATE OF GPA ON StudentREFERENCING OLD TABLE AS o, NEW TABLE AS nREFERENCING OLD TABLE AS o, NEW TABLE AS nFOR EACH STATEMENTWHEN (NOT EXISTS(SELECT * FROM o, nWHERE o.SID = n.SIDAND o.GPA >= n.GPA))UPDATE Faculty SET salary = salary + 1000;) A row-level trigger would be difficult to write in this case12System issues Recursive firing of triggers Action of one trigger causes another trigger to fire Can get into an infinite loop• Some DBMS restrict trigger actions• Most DBMS set a maximum level of recursion (16 in DB2) Interaction with constraints (very tricky to get right!) When do we check if a triggering event violates constraints?•After a BEFORE trigger (so the trigger can fix a potential violation)• Before an AFTER trigger AFTER triggers also see the effects of, say, cascaded deletes caused by referential integrity constraint violations(Based on DB2; other DBMS may implement a different policy)313Views A view is like a “virtual” table Defined by a query, which describes how to compute the view contents on the fly DBMS stores the view definition query instead of view contents Can be used in queries just like a regular table14Creating and dropping views Example: CPS116 roster CREATE VIEW CPS116Roster ASSELECT SID, name, age, GPAFROM StudentWHERE SID IN (SELECT SID FROM EnrollCalled “base tables”WHERE SID IN (SELECT SID FROM EnrollWHERE CID = ’CPS116’); To drop a view DROP VIEW view_name;15Using views in queries Example: find the average GPA of CPS116 students SELECT AVG(GPA) FROM CPS116Roster; To process the query, replace the reference to the view by its definition() SELECT AVG(GPA)FROM (SELECT SID, name, age, GPAFROM StudentWHERE SID IN (SELECT SIDFROM EnrollWHERE CID = ’CPS116’));16Why use views? To hide data from users To hide complexity from users Logical data independence If applications deal with views, we can change the pp c o s d w v ws, w c c gunderlying schema without affecting applications Recall physical data independence: change the physical organization of data without affecting applications To provide a uniform interface for different implementations or sources) Real database applications use tons of views17Modifying views Does not seem to make sense since views are virtual But does make sense if that is how users see the database Goal: modify the base tables such that the ymodification would appear to have been accomplished on the view18A simple caseCREATE VIEW StudentGPA ASSELECT SID, GPA FROM Student;DELETE FROM StudentGPA WHERE SID = 123;translates to:DELETE FROM Student WHERE SID = 123;419An impossible caseCREATE VIEW HighGPAStudent ASSELECT SID, GPA FROM StudentWHERE GPA > 3.7;INSERT INTO HighGPAStudentVALUES(987, 2.5); No matter what you do on Student, the inserted row will not be in HighGPAStudent20A case with too many possibilitiesCREATE VIEW AverageGPA(GPA) ASSELECT AVG(GPA) FROM Student; Note that you can rename columns in view definitionUPDATE AverageGPA SET GPA = 2.5; Set everybody’s GPA to 2.5? Adjust everybody’s GPA by the same amount? Just lower Lisa’s
View Full Document
Unlocking...