Advanced SQL Instructor Mohamed Eltabakh meltabakhcs wpi edu
Advanced SQL Instructor: Mohamed Eltabakh meltabakh@cs. wpi. edu 1
Roadmap l Views l Triggers l Assertions l Cursors l Stored Procedures
What is a View l An SQL query that we register (store) inside the database CREATE VIEW <name> AS <select statement>; DROP VIEW <name>; l Any query can be a view Prof. Num. Students is a view with schema (p. Number, CNT) CREATE VIEW Prof. Num. Students AS SELECT p. Number, count(*) AS CNT FROM Student GROUP BY p. Number; 3
Why Need a View: I l Frequent queries: query is used again and again l Complex queries: query written once and stored in the database CREATE VIEW Student. Info AS SELECT s. Number, s. Name FROM Student S, Registration R, Professor P Where <many conditions> Group By …. Having …; Write this complex query once And store it in the DB 4
Why Need a View: II l Logical data independence: the base table may change but the view is still the same Student (s. Number, s. Name, s. Address, s. Major) CREATE VIEW Student. Basic. Info AS SELECT s. Number, s. Name FROM Student; Alter table Student drop column s. Major; We need only s. Number and s. Name Code that queries table Student may need to change because the output schema has changed. Any code that queries the view will not change because the view output is still the same. 5
Why Need a View: III l Hide information (Security): allow users to see the view but not the original tables Student (s. Number, s. Name, s. Address, s. Major, SSN) CREATE VIEW Student. No_SSN AS SELECT s. Number, s. Name, s. Address, s. Major FROM Student; This view hides the SSN column 6
View Schema l Think of a view as a table, but it gets its data during runtime l l Only the definition is stored without data View Schema l Consists of the columns produced from the select statement CREATE VIEW Prof. Num. Students AS SELECT p. Number, count(*) AS CNT FROM Student GROUP BY p. Number; CREATE VIEW Student. Basic. Info AS SELECT s. Number, s. Name FROM Student; Prof. Num. Students(p. Number, CNT) Student. Basic. Info(s. Number, s. Name) 7
Example Customers who have accounts Customers who have loans 8
Example (Cont’d) In this example, we added an extra , ‘A’ as type column (constant) to differentiate between the two customer types , ‘L’ as type 9
Querying a View l Exactly as querying a table 10
View Execution: Naïve way 1 Execute the view and store the output in temporary table Temp table: all_customer(branch_name, customer_name) 2 Execute the user’s query Produce output Not the way the DB does it…. 3 Delete the temporary table 11
View Execution: Actual Way 1 Replace the view name with its definition Select customer_name From all_customer Where branch_name = ‘Perryridge’; 2 Optimize the query This condition can move inside the two select statements 12
View Execution: Actual Way 3 Re-write the query and execute Select customer_name From And branch_name = ‘Perryridge’) Conditions will even execute before the joins And branch_name = ‘Perryridge’) all_customer; 13
Summarizing Views 1 - Register query and store it in DB 2 - Only query definition is stored (not data) CREATE VIEW Student. Info AS SELECT s. Number, s. Name FROM Student S, Registration R, Professor P Where <many conditions> Group By …. Having …; 4 - Why need it: - Storing complex queries - Security (hide information) - Logical Independence 3 - Referenced in other queries like any other table in the DB 14
Roadmap l Views l Triggers l Assertions l Cursors l Stored Procedures
Triggers: Introduction l The application constraints need to be captured inside the database l Some constraints can be captured by: l Primary Keys, Foreign Keys, Unique, Not NULL, and domain constraints CREATE TABLE Students (sid: CHAR(20), name: CHAR(20) NOT NULL, login: CHAR(10), age: INTEGER, gpa: REAL Default 0, These constraints are defined in CREATE TABLE or ALTER TABLE Constraint pk Primary Key (sid), Constraint u 1 Unique (login), Constraint gpa. Max check (gpa <= 4. 0) ); 16
Triggers: Introduction l Some application constraints are complex l l Need for assertions and triggers Examples: l l l Sum of loans taken by a customer does not exceed 100, 000 Student cannot take the same course after getting a pass grade in it Age field is derived automatically from the Date-of-Birth field 17
Triggers l A procedure that runs automatically when a certain event occurs in the DBMS l The procedure performs some actions, e. g. , l l l 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) 18
Trigger Components l Three components l l 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 l When the Event occurs and Condition is true, execute the Action Lets see how to define these components 19
Trigger: Events l Three event types l l Two triggering times l l l Insert Update Delete Before the event After the event Two granularities l l Execute for each row Execute for each statement 20
1) Trigger: Event Trigger name Create Trigger <name> Before|After Insert|Update|Delete ON <tablename> That is the event …. l Example Create Trigger ABC Before Insert On Students …. This trigger is activated when an insert statement is issued, but before the new record is inserted Create Trigger XYZ After Update On Students …. This trigger is activated when an update statement is issued and after the update is executed 21
Granularity of Event l A single SQL statement may update, delete, or insert many records at the same time l l E. g. , Update student set gpa = gpa x 0. 8; Does the trigger execute for each updated or deleted record, or once for the entire statement ? l We define such granularity Create Trigger <name> Before| After Insert| Update| Delete For Each Row | For Each Statement …. This is the event This is the granularity 22
Example: Granularity of Event Create Trigger XYZ After Update ON <tablename> For each statement …. This trigger is activated once (per UPDATE statement) after all records are updated l Create Trigger XYZ Before Delete ON <tablename> For each row …. This trigger is activated before deleting each record In Oracle The Default is “For each Statement” l You do not need to write it 23
2) Trigger: Condition l l This component is optional Trigger executes only if the condition is true Create Trigger <name> Before| After Insert| Update| Delete On <table. Name> For Each Row | For Each Statement When <condition> … That is the condition If the employee salary > 150, 000 then some actions will be taken Create Trigger Emp. Sal After Insert or Update On Employee For Each Row When (new. salary >150, 000) … 24
3) Trigger: Action l Action depends on what you want to do, e. g. : l l l 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: l l The new values of inserted or updated records (: new) The old values of deleted or updated records (: old) 25
Trigger: Referencing Values l In the action, you may want to reference: l l The new values of inserted or updated records (: new) The old values of deleted or updated records (: old) Trigger body Create Trigger Emp. Sal After Insert or Update On Employee For Each Row When (new. salary >150, 000) Begin if (: new. salary < 100, 000) … End; Inside “When”, the “new” and “old” should not have “: ” Inside the trigger body, they should have “: ” 26
: New and : Old Variables Insert into R values (5, 10); Create Trigger Test Before Insert On R For Each Row Begin R A B 1 2 3 4 1 7 Rule 1: Do not use : Old variable (it does not exist) End; / Rule 2: : New variable gives you the new values to be inserted. : New. A 5 : New. B 10 27
: New and : Old Variables (cont’d) Delete From R Where A = 1; Create Trigger Test Before Delete On R For Each Row Begin R A B 1 2 3 4 1 7 Rule 1: Do not use : New variable (it does not exist) Rule 2: : Old variable gives you the old values to be deleted. End; / A B 1 2 3 4 1 7 1 st execution : Old. A 1 : Old. B 2 : Old. A 1 : Old. B 7 2 nd execution 28
: New and : Old Variables (cont’d) Update R Set B = B * 2 Where A= 3; Create Trigger Test Before Update On R For Each Row Begin End; / R A B 1 2 3 4 1 7 Rule 1: : Old gives you the old values before the update. : New gives you the new values after the update : Old. A 3 : New. A 3 : Old. B 4 : New. B 8 29
: New and : Old Variables (cont’d) Statement-Level Trigger Delete From R Where A = 1; Create Trigger Test Before Insert/Update/Delete On R For Each Statement Remember: In Oracle, Begin it is not written End; / R A B 1 2 3 4 1 7 Rule 1: Statement-level triggers have no access to : Old or : New 30
Trigger: Referencing Values (Cont’d) l Insert Event l Has only : new defined l Delete Event l Has only : old defined l Update Event l Has both : new and : old defined l Before triggering (for insert/update) l l l Can update the values in : new Changing : old values does not make sense After triggering l Should not change : new because the event is already done 31
Example 1 If the employee salary increased by more than 10%, make sure the ‘rank’ field is not empty and its value has changed, otherwise reject the update If the trigger exists, then drop it first Create or Replace Trigger Emp. Sal Before Update On Employee Compare the old and new salaries For Each Row Begin IF (: new. salary > (: old. salary * 1. 1)) Then IF (: new. rank is null or : new. rank = : old. rank) Then RAISE_APPLICATION_ERROR(-20004, 'rank field not correct'); End IF; End; / Make sure to have the “/” to run the command 32
Example 2 If the employee salary increased by more than 10%, then increment the rank field by 1. In the case of Update event only, we can specify which columns Create or Replace Trigger Emp. Sal Before Update Of salary On Employee For Each Row Begin IF (: new. salary > (: old. salary * 1. 1)) Then : new. rank : = : old. rank + 1; End IF; End; / We changed the new value of rank field The assignment operator has “: ” 33
Example 3: Using Temp Variable If the newly inserted record in employee has null hire. Date field, fill it in with the current date Create Trigger Emp. Date Before Insert On Employee For Each Row Declare temp date; Begin Select sysdate into temp from dual; IF (: new. hire. Date is null) Then : new. hire. Date : = temp; End IF; End; / Since we need to change values, then it must be “Before” event Declare section to define variables Oracle way to select the current date Updating the new value of hire. Date before inserting it 34
Example 4: Maintenance of Derived Attributes Keep the bonus attribute in Employee table always 3% of the salary attribute Create Trigger Emp. Bonus Before Insert Or Update On Employee For Each Row Begin : new. bonus : = : new. salary * 0. 03; End; / Indicate two events at the same time The bonus value is always computed automatically 35
Combining Multiple Events in One Trigger l If you combine multiple operations l Sometimes you need to know what is the current operation Create Trigger Emp. Bonus Before Insert Or Update On Employee For Each Row Begin IF (inserting) Then … End IF; IF (updating) Then … End IF; End; / Combine Insert and Update Can do something different under each operation
Before vs. After l Before Event l When checking certain conditions that may cause the operation to be cancelled l l When modifying values before the operation l l E. g. , if the name is null, do not insert E. g. , if the date is null, put the current date After Event l When taking other actions that will not affect the current operations l The insert in table X will cause an update in table Y Before Insert Trigger: : new. x : = …. After Insert Trigger: : new. x : = … //Changing value x that will be inserted //meaningless because the value is already inserted
Row-Level vs. Statement-Level Triggers l Example: Update emp set salary = 1. 1 * salary; l l Row-level triggers l l l Changes many rows (records) Check individual values and can update them Have access to : new and : old vectors Statement-level triggers l l l Do not have access to : new or : old vectors (only for row-level) Execute once for the entire statement regardless how many records are affected Used for verification before or after the statement 38
Example 5: Statement-level Trigger Store the count of employees having salary > 100, 000 in table R Indicate three events at the same time Create Trigger Emp. Bonus After Insert Or Update of salary Or Delete On Employee For Each Statement Remember: In Oracle, Begin it is not written delete from R; insert into R(cnt) Select count(*) from employee where salary > 100, 000; End; / Delete the existing record in R, and then insert the new count. 39
Order Of Trigger Firing Loop over each affected record Before Trigger (statement-level) Before Trigger (row-level) Event (rowlevel) After Trigger (row-level) After Trigger (statement-level) 40
Some Other Operations l Dropping Trigger SQL> Drop Trigger <trigger name>; l If creating trigger with errors SQL > Show errors; It displays the compilation errors 41
Key Points in Triggers l Must understand what type of trigger to create l l Before or After Under which operation: Insert, Update, or Delete Row-level or Statement-level : old and : new variables l l l Update both are available Insert Only : new is available Delete Only : old is available 42
Roadmap l Views l Triggers l Assertions l Cursors l Stored Procedures
Assertions l An expression that should be always true l When created, the expression must be true l DBMS checks the assertion after any change that may violate the expression Must return True or False 44
Example 1 Sum of loans taken by a customer does not exceed 100, 000 Create Assertion Sum. Loans Check ( 100, 000 >= ALL Select Sum(amount) From borrower B , loan L Where B. loan_number = L. loan_number Group By customer_name ); 45 Must return True or False (not a relation)
Example 2 Number of accounts for each customer in a given branch is at most two Create Assertion Num. Accounts Check ( 2 >= ALL Select count(*) From account A , depositor D Where A. account_number = D. account_number Group By customer_name, branch_name ); 46
Example 3 Customer city is always not null Create Assertion City. Check ( NOT EXISTS ( Select * From customer Where customer_city is null)); 47
Assertions vs. Triggers l Assertions do not modify the data, they only check certain conditions l Triggers are more powerful because the can check conditions and also modify the data l Assertions are not linked to specific tables in the database and not linked to specific events l Triggers are linked to specific tables and specific events
Assertions vs. Triggers (Cont’d) l All assertions can be implemented as triggers (one or more) l Not all triggers can be implemented as assertions l Oracle does not have assertions
Example: Trigger vs. Assertion All new customers opening an account must have opening balance >= $100. However, once the account is opened their balance can fall below that amount. We need triggers, assertions cannot be used Trigger Event: Before Insert Create Trigger Opening. Bal Before Insert On Customer For Each Row Begin IF (: new. balance is null or : new. balance < 100) Then RAISE_APPLICATION_ERROR(-20004, 'Balance should be >= $100 '); End IF; End;
Triggers & Assertions Any Questions 51
- Slides: 51