Triggers : The Problem - Examples from COMPANY Database

1. Triggers: The Problem -Examples from COMPANY Database EMPLOYEE(Name, SSN, Salary, DNO, SupervisorSSN, JobCode) DEPARTMENT(DNO, TotalSalary, ManagerSSN) STARTING_PAY(JobCode, StartPay) • Limit all salary increases to 50%. • Enforce policy that salaries may never decrease. • Maintain TotalSalary in DEPARTMENT relation as employees and their salaries change. • Inform a supervisor whenever a supervisee’s salary becomes larger than the supervisor’s. • All new hires for a given job code get the same starting salary, which is available in the STARTING_PAY table. CIS 671

2. The Problem:Example from BANK Database Branch(BranchID, BranchName, BranchCity, Assets) Customer(CustID, CustName, CustStreet, CustCity) Account(AccountNo, BranchID, Balance) AccountCustomer(AccountNo, CustID) Loan(LoanNo, BranchID, Balance) LoanCustomer(LoanID, CustID) • Branch Assets are maintained as sum(Account.Balance) for each branch. • Overdrafts do not produce a negative balance. Instead they are treated as a loan. The account balance is set to 0 and a loan is created for the amount of the overdraft. CIS 671

3. The Problem:Example from TEMPERATURE Database • Given table of temperatures TEMPS, that is periodically updated, keep the table of extreme temperatures EXTREMES up to date. • Create the EXTREMES table and populate with all the cities in the TEMPS table, setting the other attributes to null. CIS 671

4. What is Needed?The Event-Condition-Action Model (ECA Model) • Rules (or triggers) with three components: • Eventtriggering the rule. (insert, delete, update) • E.g., an employee’s salary changes. • Condition to determine if rule action should be executed. • E.g., is new Temp in City higher than HighTemp for that City? • Action to be taken. • E.g., update the Department’s Total Salary. CIS 671

5. What is Needed?The Event-Condition-Action Model (ECA Model), continued • Actions may apply before or after thetriggeringevent is executed. • An SQL statement may change several rows. • Apply actiononce per SQL statement. • Apply actionfor each row changed by SQL statement. CIS 671

6. Availability • Triggers included in SQL 1999 (SQL 3) • Not in earlier standards. • Included much earlier in most products: • Oracle, Sybase, DB2 • As a consequence syntax may differ from the standard. CIS 671

7. The Problem:Examples from COMPANY Database EMPLOYEE(Name, SSN, Salary, DNO, SupervisorSSN, JobCode) DEPARTMENT(DNO, TotalSalary, ManagerSSN) STARTING_PAY(JobCode, StartPay) • Limit all salary increases to 50%. • Enforce policy that salaries may never decrease. • Maintain TotalSalary in DEPARTMENT relation as employees and their salaries change. (EN, Fig. 23.2 (a)) • Inform a supervisor whenever a supervisee’s salary becomes larger than the supervisor’s. (EN, Fig. 23.2 (b)) • All new hires for a given job code get the same starting salary, which is available in the STARTING_PAY table. CIS 671

8. 1. COMPANY DatabaseLimit all salary increases to 50%before triggeremp_salary_limit EMPLOYEE(Name, SSN, Salary, DNO, SupervisorSSN, JobCode) create trigger emp_salary_limit before update of EMPLOYEE for each row when(new.Salary > 1.5 * old.Salary) set new.Salary = 1.5 * old.Salary; “old” refers to the old tuple. “new” refers to the new tuple. CIS 671

9. 2. COMPANY DatabaseEnforce policy that salaries may never decreasebefore triggeremp_salary_no_decrease EMPLOYEE(Name, SSN, Salary, DNO, SupervisorSSN, JobCode) create trigger emp_salary_no_decrease before update of EMPLOYEE for each row when(new.Salary < old.Salary) begin log the event; signal error condition; end Method depends on DBMS. CIS 671

10. 5. COMPANY Database: All new hires for a given job code get the same starting salary, which is available in the STARTING_PAY table. before triggeremp_start_pay EMPLOYEE(Name, SSN, Salary, DNO, SupervisorSSN, JobCode) STARTING_PAY(JobCode, StartPay) create trigger emp_start_pay before insert on EMPLOYEE for each row set Salary = ( select StartPay from STARTING_PAY where JobCode = new.JobCode) CIS 671

11. 7. BANK Database: Overdrafts do not produce a negative balance. Instead they are treated as a loan. The account balance is set to 0 and a loan is created for the amount of the overdraft.after triggeroverdraft_trigger CIS 671

12. 7. BANK Database: Overdrafts, continuedafter triggeroverdraft_trigger Branch(BranchID, BranchName, BranchCity, Assets) Customer(CustID, CustName, CustStreet, CustCity) Account(AccountNo, BranchID, Balance) AccountCustomer(AccountNo, CustID) Loan(LoanNo, BranchID, Balance) LoanCustomer(LoanID, CustID) • Insert a new tuple in the Loan relation, using same branch as the account. Make LoanNo the same as the AccountNo and the Balance the amount of the overdraft. • Insert a new tuple in the LoanCustomer relation relating the new loan to this customer. • Set the Balance of the Account tuple to 0. CIS 671

13. 7. BANK Database: Overdrafts, continuedafter triggeroverdraft_trigger create trigger overdraft_trigger after update on Account for each row when new.balance < 0 begin insert into Loanvalues (new.AccountID, new.BranchID, -new.Balance); insert intoLoanCustomer ( select CustID, AccountNo from AccountCustomer AC where new.AccountID = AC.AccountID); updateAccount set Balance = 0 whereAccount.AccountNo = new.AccountNo; end; CIS 671

14. 8. TEMPS Database: Create the EXTREMES table and populate with all the cities in the TEMPS table, setting the other attributes to null.after triggersHighTempUpdate and LowTempUpdate TEMPS(City, Temp) EXTREMES(City, HighTemp, HighDate, LowTemp, LowDate) • Need two after triggers • one for updating the high temperature (HighTempUpdate), • the other for updating the low temperature (LowTempUpdate). • They will be very similar. CIS 671

15. 8. TEMPS Database: continued. High Tempearture Update TEMPS(City, Temp) EXTREMES(City, HighTemp, HighDate, LowTemp, LowDate) create triggerHighTempUpdate after update of TEMPS for each row when(new.Temp > ( select HighTemp from EXTREMES where City= new.City) or ( select HighTemp from EXTREMES where City = new.City) is null ) ) update EXTREMES set HighTemp= new.Temp, HighDate = current date where City = new.City; Normal situation. Initially HighTemp is null. Low Temperature similar. CIS 671

16. 8. TEMPS Database: continued. Other Problems • Insert a new City into TEMPS. • Must also insert into EXTREMES. • Initial values of HighTemp, HighDate, LowTemp, LowDate must be set. • Delete a City from TEMPS. • Leave City in EXTREMES. • Delete City from EXTREMES. CIS 671

17. 8. TEMPS Database: continued.Insert a new City into TEMPS:Insert City tuple into EXTREMES.Set initial values of HighTemp, HighDate, LowTemp, LowDate. TEMPS(City, Temp) EXTREMES(City, HighTemp, HighDate, LowTemp, LowDate) create triggerNewCity after insert of TEMPS for each row insert into EXTREMES(City, HighTemp, HighDate, LowTemp, LowDate) values(new.City, new.Temp, Current Date, new.Temp, Current Date); CIS 671

18. 8. TEMPS Database: continued.Delete a City from TEMPS:Delete City tuple from EXTREMES.Add Foreign Key constraint for EXTREMES. TEMPS(City, Temp) EXTREMES(City, HighTemp, HighDate, LowTemp, LowDate) Alter table EXTREMES add constraint fk foreign key(City) references TEMPS on delete cascade; CIS 671

19. Problems with Use of Triggers • How to guarantee set of triggers is consistent? • Recursion is allowed. • How to guarantee termination? • Tools are still needed to help address these problems. CIS 671

20. Triggers: The Problem -9. Another Example fromCOMPANY Database • Add a new field, Span, to the EMPLOYEE relation. • For each employee, Span is the number of employees supervised. • Initialize Span appropriately. • Keep Span correct as the database changes. EMPLOYEE(Name, SSN, Salary, DNO, SupervisorSSN, JobCode) DEPARTMENT(DNO, TotalSalary, ManagerSSN) STARTING_PAY(JobCode, StartPay) EMPLOYEE(Name, SSN, Salary, DNO, SupervisorSSN, JobCode, Span) CIS 671

21. 9. COMPANY DatabaseFor each employee, Span is the number of employees supervised. EMPLOYEE(Name, SSN, Salary, DNO, SupervisorSSN, JobCode, Span) • Idea • Add the Span attribute to EMPLOYEE. • Initialize the values of Span based on the current database. • Create insert, delete and update triggers to keep Span up to date for an employee’s immediate supervisor. • Create another update trigger to propagate the change in Span up through the rest of the hierarchy. CIS 671

22. 9. COMPANY DatabaseUpdate Span for the immediate supervisor. EMPLOYEE(Name, SSN, ..., SupervisorSSN,..., Span) create triggerEmpHire after insert on EMPLOYEE for each row update EMPLOYEE set Span = Span + 1 whereSSN = new.SupervisorSSN; • create triggerEmpTransfer • after update of SupervisorSSN on EMPLOYEE • for each row • begin • update EMPLOYEE • set Span = Span - 1 • whereSSN = • old.SupervisorSSN; • update EMPLOYEE • set Span = Span + 1 • whereSSN = • new.SupervisorSSN; • end; create triggerEmpQuit after delete on EMPLOYEE for each row update EMPLOYEE set Span = Span - 1 whereSSN = old.SupervisorSSN; CIS 671

23. 9. COMPANY DatabasePropagate Span up the supervisor tree. EMPLOYEE(Name, SSN, Salary, DNO, SupervisorSSN, JobCode, Span) A 123 456 4 B 456 789 8 new value Supervisor create triggerEmpPropagate after update of Span on EMPLOYEE for each row updateEMPLOYEE set Span = Span + (new.Span - old.Span) whereSSN = new.SupervisorSSN; +(new.Span - old.Span) decrease (4 - 5) = -1 increase (4 - 3) = +1 CIS 671

24. 10. For each PERSON, record their mother, father and number of descendants. PERSONS(Name, Mother, Father, NumDescendants) After insert, update the mother and father. create triggerNewMother after insert on PERSONS for each row update PERSONS setNumDescendants = NumDescendants + 1 whereName = new.Mother; create triggerNewFather after insert on PERSONS for each row update PERSONS setNumDescendants = NumDescendants + 1 whereName = new.Father; Then update the maternal and paternal ancestors. CIS 671

25. 10. For each PERSON, record their mother, father and number of descendants. PERSONS(Name, Mother, Father, NumDescendants) Update the maternal and paternal ancestors. create triggerMaternalAncestor after update of NumDescendants on PERSONS for each row update PERSONS setNumDescendants = NumDescendants + new.NumDescendants - old.NumDescendants whereName = new.Mother; create trigger PaternalAncestorafter update of NumDescendants on PERSONS /* Similar. Just replace “Mother” with “Father.” */ At nth level of family tree, how many triggers? CIS 671