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)

1. Limit all salary increases to 50.
2. Enforce policy that salaries may never decrease.
3. Maintain TotalSalary in DEPARTMENT relation as
   employees and their salaries change.
4. Inform a supervisor whenever a supervisees
   salary becomes larger than the supervisors.
5. All new hires for a given job code get the same
   starting salary, which is available in the
   STARTING_PAY table.

2
The ProblemExample from BANK Database
Branch(BranchID, BranchName, BranchCity,
Assets) Customer(CustID, CustName, CustStreet,
CustCity) Account(AccountNo, BranchID,
Balance) AccountCustomer(AccountNo,
CustID) Loan(LoanNo, BranchID, Balance) LoanCustom
er(LoanID, CustID)

1. Branch Assets are maintained as
   sum(Account.Balance) for each branch.
2. 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.

3
The ProblemExample from TEMPERATURE Database
EXTREMES City High Temp High Date Low Temp Low Date
Denver 100 7/30 -10 1/20
Columbus 95 7/5 2 2/14
Anchorage 87 8/3 -10 2/16
TEMPS City Temp
Denver 25
Columbus 15
Anchorage -15

• 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.

4
What is Needed?The Event-Condition-Action Model
(ECA Model)

• Rules (or triggers) with three components
• Event triggering the rule. (insert, delete,
  update)
• E.g., an employees 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 Departments Total Salary.

5
What is Needed?The Event-Condition-Action Model
(ECA Model), continued

• Actions may apply before or after the triggering
  event is executed.
• An SQL statement may change several rows.
• Apply action once per SQL statement.
• Apply action for each row changed by SQL
  statement.

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.

7
The ProblemExamples from COMPANY Database
EMPLOYEE(Name, SSN, Salary, DNO, SupervisorSSN,
JobCode) DEPARTMENT(DNO, TotalSalary, ManagerSSN)
STARTING_PAY(JobCode, StartPay)

1. Limit all salary increases to 50.
2. Enforce policy that salaries may never decrease.
3. Maintain TotalSalary in DEPARTMENT relation as
   employees and their salaries change. (EN, Fig.
   23.2 (a))
4. Inform a supervisor whenever a supervisees
   salary becomes larger than the supervisors. (EN,
   Fig. 23.2 (b))
5. All new hires for a given job code get the same
   starting salary, which is available in the
   STARTING_PAY table.

8
1. COMPANY DatabaseLimit all salary increases to
50before trigger emp_salary_limit
EMPLOYEE(Name, SSN, Salary, DNO, SupervisorSSN,
JobCode)
create trigger emp_salary_limit before update of
EMPLOYEE for each row when (new.Salary gt 1.5
old.Salary) set new.Salary 1.5 old.Salary
old refers to the old tuple.
new refers to the new tuple.
9
2. COMPANY Database Enforce policy that salaries
may never decreasebefore trigger
emp_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
lt old.Salary) begin log the event signal
error condition end
Method depends on DBMS.
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
trigger emp_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)
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 trigger overdraft_trigger
12
7. BANK Database Overdrafts, continued after
trigger overdraft_trigger
Branch(BranchID, BranchName, BranchCity,
Assets) Customer(CustID, CustName, CustStreet,
CustCity) Account(AccountNo, BranchID,
Balance) AccountCustomer(AccountNo,
CustID) Loan(LoanNo, BranchID, Balance) LoanCustom
er(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.

13
7. BANK Database Overdrafts, continued after
trigger overdraft_trigger
create trigger overdraft_trigger after update on
Account for each row when new.balance lt
0 begin insert into Loan values (new.AccountID,
new.BranchID, -new.Balance) insert into
LoanCustomer ( select CustID,
AccountNo from AccountCustomer
AC where new.AccountID AC.AccountID) update
Account set Balance 0 where
Account.AccountNo new.AccountNo end
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
triggers HighTempUpdate 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.

15
8. TEMPS Database continued. High Tempearture
Update
TEMPS(City, Temp) EXTREMES(City, HighTemp,
HighDate, LowTemp, LowDate)
create trigger HighTempUpdate after update of
TEMPS for each row when (new.Temp
gt ( 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.
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.

17
8. TEMPS Database continued.Insert a new City
into TEMPSInsert City tuple into EXTREMES.Set
initial values of HighTemp, HighDate, LowTemp,
LowDate.
TEMPS(City, Temp) EXTREMES(City, HighTemp,
HighDate, LowTemp, LowDate)
create trigger NewCity after insert of TEMPS for
each row insert into EXTREMES(City, HighTemp,
HighDate, LowTemp, LowDate) values(ne
w.City, new.Temp, Current Date,
new.Temp, Current Date)
18
8. TEMPS Database continued.Delete a City from
TEMPSDelete 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
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.

20
Triggers The Problem -9. Another Example
fromCOMPANY Database
EMPLOYEE(Name, SSN, Salary, DNO, SupervisorSSN,
JobCode) DEPARTMENT(DNO, TotalSalary, ManagerSSN)
STARTING_PAY(JobCode, StartPay)

• 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, Span)
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 employees immediate
  supervisor.
• Create another update trigger to propagate the
  change in Span up through the rest of the
  hierarchy.

22
9. COMPANY DatabaseUpdate Span for the immediate
supervisor.
EMPLOYEE(Name, SSN, ..., SupervisorSSN,..., Span)
create trigger EmpHire after insert on EMPLOYEE
for each row update EMPLOYEE set Span Span
1 where SSN new.SupervisorSSN
create trigger EmpTransfer after update of
SupervisorSSN on EMPLOYEE for each
row begin update EMPLOYEE set Span Span -
1 where SSN old.SupervisorSSN update
EMPLOYEE set Span Span 1 where SSN
new.SupervisorSSN end
create trigger EmpQuit after delete on
EMPLOYEE for each row update EMPLOYEE set Span
Span - 1 where SSN old.SupervisorSSN
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 trigger EmpPropagate after update of
Span on EMPLOYEE for each row update
EMPLOYEE set Span Span (new.Span -
old.Span) where SSN new.SupervisorSSN
(new.Span - old.Span) decrease (4 - 5)
-1 increase (4 - 3) 1
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 trigger NewMother after insert on
PERSONS for each row update PERSONS set
NumDescendants NumDescendants 1 where
Name new.Mother
create trigger NewFather after insert on
PERSONS for each row update PERSONS set
NumDescendants NumDescendants 1 where
Name new.Father
Then update the maternal and paternal ancestors.
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 trigger MaternalAncestor after update of
NumDescendants on PERSONS for each row update
PERSONS set NumDescendants NumDescendants
new.NumDescendants -
old.NumDescendants where Name new.Mother
create trigger PaternalAncestor after update of
NumDescendants on PERSONS / Similar. Just
replace Mother with Father. /
At nth level of family tree, how many triggers?