Transcript Triggers

Triggers
Why Triggers ?
• Suppose a warehouse wishes to maintain a minimum
inventory of each item.
• Number of items kept in items table
Items(name, number, ...)
• Orders for less items kept in orders table and
Orders(name, number, ...)
• If number of any item is less than a level; a row containing
name of the item must be added to orders table
• How to define this process? (When to check? What to
check? What to do?)
• Execute the process in DB Application side or db-sever
side ? Why?
Trigger
• A statement that the system executes
(triggers) automatically as a side effect of a
modification to the database.
Trigger
• To design a Trigger we need to specify:
– when a trigger is to be executed. This is broken up into:
• An event that causes the trigger to be checked
• A condition that must be satisfied for trigger
execution to proceed.
– The actions to be taken when the trigger executes.
• Above model of triggers is referred to as the
event-condition-action
Triggers
• Entering a trigger into the database, the DB
system is responsible for executing it whenever
the specified event occurs and the corresponding
condition is satisfied.
• The events that fire a trigger in general
– DML statements(INSERT, UPDATE, DELETE)
– DDL statements
– System events (start-up, shut-down, and error
messages)
– User events such as logon and logoff
A Trigger Example
• Suppose that, a bank offers accounts and
loans
account(account-number, branch-name, balance)
borrower(customer-name, loan-number)
depositor(customer-name, account-number)
loan(loan-number, branch-name, amount)
A Trigger Example
• The banks does not allows negative
account balances
• Instead bank
– creates a loan in the amount of the overdraft.
– sets the account balance to zero
– gives this loan a loan number same as the
account number
A Trigger Example
What is the trigger's event?
What is the trigger's condition?
What is the trigger's action?
A Trigger Example
• Event is update to the account relation
• Condition is that update results in a negative
balance value.
• Action is
– Insert a new tuple s in the loan relation with s[loannumber] = t[account-number] s[branch-name] =
t[branch-name] s[amount] = -t[balance]
– Insert a new tuple u in the borrower relation with
u[customer -name ] = "Jones" u[loan-number] =
t[account-number]
– Set t[balance] to 0
A Trigger Example
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
(nrow.account-number, nrow.branch-name,-nrow.balance);
update account set balance = 0
where account.account-number = nrow.account-number
end
<<SQL:1999 syntax for triggers>>
Other Forms of Event
• other events are insert or delete
– E.g. if a new depositor is inserted, the triggered action
could be to send a welcome letter to the depositor
• the trigger can specify columns whose update causes
the trigger to execute.
• The referencing old row as clause can be used to
create a variable storing the old value of an updated
or deleted row.
• The referencing new row as clause can be used
with inserts in addition to updates.
Other Forms of Event
• Triggers can be activated before the event
(insert/delete/update) instead of after the
event.
✔
✔
can prevent invalid updates.
defining inserting into views(Instead of
Triggers)
• E.g. if we wish not to permit overdrafts, we
can create a before trigger that rolls back
the transaction if the new balance is
negative.
Other Forms Of Action
• carry out an action for each affected row
✔
Like the above example
• carry out a single action for the entire
SQL statement that caused the event
• To do so, use the for each statement
clause instead of the for each row
clause
Other Forms Of Action
• Then the clauses referencing old table as or
referencing new table as can then be used to refer
to temporary tables (called transition tables)
containing all the old and affected rows
• Transition tables cannot be used with before
triggers
• But can be used with after triggers, regardless of
whether they are statement triggers or row triggers
• A single SQL statement can then be used to carry
out multiple actions beased on transition tables
When Triggers Are Used
• Using triggers to
– Automatically generate derived column
values
– Prevent invalid transactions
– Enforce complex security authorizations
– Enforce referential integrity across nodes
in a distributed database
– Enforce complex business rules
When Triggers Are Used
– When a required referential integrity
rule cannot be enforced using the
following integrity constraints:
✔
NOT NULL, UNIQUE key
✔
PRIMARY KEY
✔
FOREIGN KEY
✔
CHECK
✔
DELETE CASCADE
✔
DELETE SET NULL
When Triggers Are Used
– Provide transparent event logging
– Provide sophisticated auditing
– Maintain synchronous table replicates
– Gather statistics on table access
– Modify table data when DML statements
are issued against views
– Publish information about database
events, user events, and SQL statements
to subscribing applications
Past Use Of Triggers
• To maintain summary data.
– E.g. they used triggers on insert/delete/update of a employee
relation containing salary and dept attributes to maintain the
total salary of each department.
– Today solution: materialized views
• For replicating databases
– they used triggers on insert/delete/update of each relation to
record the changes in relations called delta relations.
– A separate process copied and the system executed the
changes on the replica.
– Modern DB systems, provide built-in facilities for database
replication, so no need to triggers
Past Use Of Triggers
• In fact, many trigger applications, including our
example overdraft-trigger, can be substituted by
"encapsulation" (introduced in SQL:1999)
– Encapsulation used to ensure that updates to
the balance attribute of account are done only
through a special procedure.
Use Triggers Carefully
• Furthermore, the action of one trigger can set off another
trigger. In the worst case, this could even lead to an
infinite chain of triggering
• Write triggers with great care, since a trigger error
detected at run time causes the failure of the DML
statement that set off the trigger.
• Use triggers only when necessary
• Use triggers only when the operation can't be expressed
using DBMS other features
• Excessive use of triggers can result in complex
interdependencies. E.g.
– When a trigger fires, a SQL statement within its trigger
action potentially can fire other triggers, resulting in
cascading triggers
So Triggers provide a
highly customized
DBMS