Active Databases - PowerPoint by malj

VIEWS: 9 PAGES: 25

									           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 supervisee’s salary
      becomes larger than the supervisor’s.
   5. All new hires for a given job code get the same starting
      salary, which is available in the STARTING_PAY table.
   Active Databases            CIS 671                           1
                  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)
6. Branch Assets are maintained as sum(Account.Balance) for
   each branch.
7. 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.

Active Databases             CIS 671                             2
              The Problem:
  Example from TEMPERATURE Database
                             EXTREMES   City        High   High Low     Low
TEMPS    City         Temp                          Temp   Date Temp    Date
         Denver         25              Denver      100    7/30   -10   1/20

         Columbus       15              Columbus     95     7/5     2   2/14

         Anchorage     -15              Anchorage    87     8/3   -10   2/16


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

   Active Databases                CIS 671                              3
              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 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.


Active Databases                    CIS 671                              4
              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.

Active Databases        CIS 671                   5
                   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.



Active Databases           CIS 671                   6
                   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. (EN, Fig. 23.2 (a))
   4.    Inform a supervisor whenever a supervisee’s salary
         becomes larger than the supervisor’s. (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.

   Active Databases              CIS 671                             7
                       1. COMPANY Database
             Limit all salary increases to 50%
                      before 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 > 1.5 * old.Salary)
                 set new.Salary = 1.5 * old.Salary;

   “new” refers to                                      “old” refers to
    the new tuple.                                       the old tuple.
   Active Databases                CIS 671                         8
             2. COMPANY Database
   Enforce policy that salaries may never decrease
                before 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
                                                Method depends on
      when (new.Salary < old.Salary)
                                                     DBMS.
         begin
               log the event;
               signal error condition;
          end




   Active Databases                CIS 671                     9
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)

   Active Databases              CIS 671                   10
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




   Active Databases                  CIS 671            11
     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)
  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.
  Active Databases                CIS 671                       12
     7. BANK Database: Overdrafts, continued
                      after trigger overdraft_trigger

create trigger overdraft_trigger after update on Account
for each row
when new.balance < 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;

   Active Databases                CIS 671                 13
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.



    Active Databases                CIS 671                             14
8. TEMPS Database: continued. High Tempearture Update
TEMPS(City, Temp)
EXTREMES(City, HighTemp, HighDate, LowTemp, LowDate)

create trigger HighTempUpdate
after update of TEMPS                          Normal
for each row                                  situation.
   when (new.Temp >
              ( select HighTemp
                from     EXTREMES
                                                         Initially
                where    City= new.City)               HighTemp
     or ( select HighTemp                                 is null.
           from EXTREMES
           where City = new.City)        is null ) )
   update EXTREMES
      set HighTemp       = new.Temp,            Low Temperature
         HighDate        = current date         similar.
      where City = new.City;
 Active Databases              CIS 671                         15
                   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.

Active Databases               CIS 671                      16
             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 trigger NewCity
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);
 Active Databases                      CIS 671                              17
           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;


 Active Databases             CIS 671                  18
      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.




  Active Databases         CIS 671                    19
                       Triggers: The Problem -
                       9. Another Example from
                         COMPANY Database
EMPLOYEE(Name, SSN, Salary, DNO, SupervisorSSN, JobCode)
DEPARTMENT(DNO, TotalSalary, ManagerSSN)
STARTING_PAY(JobCode, StartPay)
    1. Add a new field, Span, to the EMPLOYEE relation.
            •     For each employee, Span is the number of employees
                  supervised.
EMPLOYEE(Name, SSN, Salary, DNO, SupervisorSSN, JobCode, Span)

    2. Initialize Span appropriately.
    3. Keep Span correct as the database changes.

    Active Databases                   CIS 671                         20
                       9. COMPANY Database
          For 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.
    Active Databases              CIS 671                          21
              9. COMPANY Database
     Update Span for the immediate supervisor.
    EMPLOYEE(Name, SSN, ..., SupervisorSSN,..., Span)

create trigger EmpHire
                                 create trigger EmpTransfer
   after insert on EMPLOYEE
                                     after update of SupervisorSSN on
for each row
                                 EMPLOYEE
   update EMPLOYEE
                                 for each row
      set Span = Span + 1
                                     begin
      where SSN =
                                       update EMPLOYEE
            new.SupervisorSSN;
                                           set Span = Span - 1
create trigger EmpQuit                     where SSN =
   after delete on EMPLOYEE                        old.SupervisorSSN;
for each row                           update EMPLOYEE
   update EMPLOYEE                         set Span = Span + 1
      set Span = Span - 1                  where SSN =
      where SSN =                                  new.SupervisorSSN;
           old.SupervisorSSN;        end;
    Active Databases             CIS 671                          22
                9. COMPANY Database
          Propagate Span up the supervisor tree.
EMPLOYEE(Name, SSN, Salary, DNO, SupervisorSSN, JobCode, Span)
           A      123                   456                          4
                                                   new value
           B      456                   789                          8


Supervisor

   create trigger EmpPropagate
      after update of Span on EMPLOYEE
   for each row                                  +(new.Span - old.Span)
      update EMPLOYEE                            decrease (4 - 5) = -1
         set Span = Span +                       increase (4 - 3) = +1
              (new.Span - old.Span)
         where SSN =
              new.SupervisorSSN;

   Active Databases               CIS 671                           23
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                    create trigger NewFather
   after insert on PERSONS                     after insert on PERSONS
for each row                                for each row
   update PERSONS                              update PERSONS
      set NumDescendants                          set NumDescendants
           = NumDescendants + 1                        = NumDescendants + 1
      where Name = new.Mother;                    where Name = new.Father;




Then update the maternal and paternal ancestors.
  Active Databases                CIS 671                               24
 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             create trigger
  on PERSONS                                 PaternalAncestor after
for each row                                 update of NumDescendants
  update PERSONS                             on PERSONS
     set NumDescendants
          = NumDescendants                   /* Similar.
            + new.NumDescendants             Just replace “Mother”
            - old.NumDescendants             with “Father.” */
     where Name = new.Mother;


       At nth level of family tree, how many triggers?
   Active Databases                CIS 671                      25

								
To top