Advanced SQL: Views & Triggers

1. Advanced SQL: Views & Triggers Instructor: Mohamed Eltabakh meltabakh@cs.wpi.edu

2. Views

3. What is a View • An SQL query that we register (store) inside the database • Any query can be a view CREATE VIEW <name> AS <select statement>; DROP VIEW <name>; ProfNumStudents is a view with schema (pNumber, CNT) CREATE VIEW ProfNumStudentsAS SELECTpNumber, count(*) AS CNT FROMStudent GROUP BYpNumber;

4. Why Need a View • Frequent queries: query is used again and again • Complex queries: query written once and stored in the database • Logical data independence: the base table may change but the the view is still the same • Hide information (Security): allow users to see the view but not the original tables CREATE VIEW StudentBasicInfoAS SELECTsNumber, sName FROMStudent; See only sNumber and sName

5. View Schema • You can think of a view as a table, but it gets its data during runtime • Only the definition is stored without data • View Schema • Consists of the columns produced from the select statement CREATE VIEW ProfNumStudentsAS SELECTpNumber, count(*) AS CNT FROMStudent GROUP BYpNumber; ProfNumStudents(pNumber, CNT) CREATE VIEW StudentBasicInfoAS SELECTsNumber, sName FROMStudent; StudentBasicInfo(sNumber, sName)

6. Example Customers who have accounts Customers who have loans

7. Example (Cont’d) In this example, we added an extra column (constant) to differentiate between the two customer types , ‘A’ as type , ‘L’ as type

8. Querying a View • Exactly as querying a table

9. Triggers

10. Triggers: Introduction • The application constraints need to be captured inside the database • Some constraints can be captured by: • Primary Keys, Foreign Keys, Unique, Not NULL, and domain • Other application constraints are more complex • Need for assertions and triggers Sum of loans taken by a customer does not exceed 100,000

11. Triggers • A procedure that runs automatically when a certain event occurs in the DBMS • The procedure performs some actions, e.g., • Check certain values • Fill in some values • Inserts/deletes/updates other records • Check that some business constraints are satisfied • Commit (approve the transaction) or roll back (cancel the transaction)

12. Trigger Components • Three components • Event: When this event happens, the trigger is activated • Condition (optional): If the condition is true, the trigger executes, otherwise skipped • Action: The actions performed by the trigger • Semantics • When the Event occurs and Condition is true, execute the Action Lets see how to define these components

13. Trigger: Event • Example Create Trigger <name> Before|After Insert|Update|Delete ON <tablename> …. That is the event Create Trigger ABC Before Insert On Students …. Create Trigger XYZ After Update On Students …. This trigger is activated when an insert statement is issued, but before the new record is inserted This trigger is activated when an update statement is issued and after the update is executed

14. Granularity of Event • An UPDATE or DELETE statement may update (or delete) many records at the same time • May insert many records in the same statement as well • Does the trigger execute for each updated or deleted record, or once for the entire statement ? • We define such granularity Create Trigger <name> Before| After Insert| Update| Delete For Each Row | For Each Statement …. That is the event That is the granularity

15. Example: Granularity of Event Create Trigger XYZ After Update ON <tablename> For each statement …. Create Trigger XYZ Before Delete ON <tablename> For each row …. This trigger is activated once (per UPDATE statement) after all records are updated This trigger is activated before deleting each record

16. Trigger: Condition • This component is optional Create Trigger <name> Before| After Insert| Update| Delete On <tableName> For Each Row | For Each Statement When <condition> … That is the condition If the employee salary > 150,00, then some actions will be taken Create Trigger EmpSal After Insert or Update On Employee For Each Row When (New.salary >150,000) …

17. Trigger: Action • Action depends on what you want to do, e.g.: • Check certain values • Fill in some values • Inserts/deletes/updates other records • Check that some business constraints are satisfied • Commit (approve the transaction) or roll back (cancel the transaction) • In the action, you may want to reference: • The new values of inserted or updated records • The old values of deleted or updated records Use REFERENCING Clause

18. Trigger: Referencing Clause Create Trigger <name> Before| After Insert| Update| Delete On <tableName> Referencing OLD AS oldRec, NEW AS newRec For Each Row | For Each Statement When <condition> Begin …. End; • Now, can reference the old record as oldRec and new record as newRec • Insert event does not have old record • Delete event does not have new record • Update event has both old and new That is Referencing clause That is the action

19. Example 1 If the employee salary increased by more than 10%, make sure the ‘comment’ field is not empty and its value has changed, otherwise reject the update Create Trigger EmpSal Before Update On Employee Referencing OLD ROW AS oldRec, NEW ROW AS newRec For Each Row Begin IF (newRec.salary > oldRec.salary * 1.10) Then IF (newRec.comment = ‘’ or newRec.comment is null or newRec.comment = oldRec.comment) RAISE_APPLICATION_ERROR(-20004, ‘Comment field not correct’); End IF; End IF; End;

20. Example 2: Using Temp Variable If the newly inserted record in employee has null date field, fill it in with the current date Create Trigger EmpDate Before Insert On Employee Referencing NEW ROW AS newRec For Each Row Declare temp date; Begin Select sysdate into temp from dual; IF (newRec.date is null) Then newRec.date := temp; End IF; End; Define variables Oracle system table always has the current date Updating the new value to be inserted

21. Example 3: Maintenance of Derived Attributes Keep the bonus attribute in Employee table always 3% of the salary attribute Create Trigger EmpBonus Before Insert Or Update On Employee For Each Row Begin newRec.bonus := newRec.salary * 0.03; End; Indicate two events at the same time The bonus value is always computed automatically