Trigger Example in SQL: 1999 create trigger overdraft-trigger after update on account referencing new row as nrow for each row when nrow balance <0 begin atomic insert into borrower (select customer-name, account-number from depositor where nrow account-number= depositor account-number insert into loan values (n row. account-number, nrow branch-name, nrow balance) update account set balance =0 where account account-number nrow account number end Database System Concepts 6.16 OSilberschatz. Korth and Sudarshan
Database System Concepts 6.16 ©Silberschatz, Korth and Sudarshan Trigger Example in SQL:1999 create trigger overdraft-trigger after update on account referencing new row as nrow for each row when nrow.balance < 0 begin atomic insert into borrower (select customer-name, account-number from depositor where nrow.account-number = depositor.account-number); insert into loan values (n.row.account-number, nrow.branch-name, – nrow.balance); update account set balance = 0 where account.account-number = nrow.accountnumber end
Triggering Events and Actions in SQL riggering event can be insert, delete or update Triggers on update can be restricted to specific attributes E.g. create trigger overdraft-trigger after update of balance on account Values of attributes before and after an update can be referenced referencing old row as for deletes and updates referencing new row as for inserts and updates Triggers can be activated before an event, which can serve as extra constraints. E.g. convert blanks to null create trigger setnull-trigger before update onr referencing new row as nrow for each row when nrow phone-number Database System Concepts set nrow phone-number= null OSilberschatz. Korth and Sudarshan
Database System Concepts 6.17 ©Silberschatz, Korth and Sudarshan Triggering Events and Actions in SQL Triggering event can be insert, delete or update Triggers on update can be restricted to specific attributes E.g. create trigger overdraft-trigger after update of balance on account Values of attributes before and after an update can be referenced referencing old row as : for deletes and updates referencing new row as : for inserts and updates Triggers can be activated before an event, which can serve as extra constraints. E.g. convert blanks to null. create trigger setnull-trigger before update on r referencing new row as nrow for each row when nrow.phone-number = ‘ ‘ set nrow.phone-number = null
Triggers in MS-SQLServer Syntax create trigger overdraft-trigger on account for update as if inserted balance <0 begin insert into borrower (select customer-name, account-number from depositor, inserted where inserted, account-number depositor account-number) nsert into loan values (inserted account-number, inserted branch-name inserted balance) update account set balance =0 from account inserted where account account-number= inserted. account-number end Database System Concepts 6.18 OSilberschatz. Korth and Sudarshan
Database System Concepts 6.18 ©Silberschatz, Korth and Sudarshan Triggers in MS-SQLServer Syntax create trigger overdraft-trigger on account for update as if inserted.balance < 0 begin insert into borrower (select customer-name,account-number from depositor, inserted where inserted.account-number = depositor.account-number) insert into loan values (inserted.account-number, inserted.branch-name, – inserted.balance) update account set balance = 0 from account, inserted where account.account-number = inserted.account-number end