Transcript Create Trigger

Triggers
The Generation of Indirect
Actions
Copyright © 2013 Curt Hill
Introduction
• A trigger is just what you might
expect – a condition that generates
an action
• The process goes like this:
– An action occurs
• Usually an insert/delete/update but may be
many other things as well
– A condition changes value
– An event handler is run to handle the
current situation
Copyright © 2013 Curt Hill
Reorder Point
• Consider a warehouse
• Bins of products
• Database that monitors
• Each product has a reorder point
• If the number drops below that threshold
the product should be reordered
• Any update that reduces the count
could trigger a reorder
– Most updates will not, but a few will
Copyright © 2013 Curt Hill
SQL
• The presence of triggers makes our
database an active database
– Active in that the update that got things
started changes the value, but makes
no provision for the trigger or its
actions
• First real standard was SQL 99
• Create Trigger is the SQL command
Copyright © 2013 Curt Hill
The Pieces
• Triggers conform to the EventCondition-Action model (ECA)
• The Event is what gets things rolling
– Usually an Update or similar statement
– The event specifies table and field
• The Condition is optional
– Is it any change or changes that
provide a certain result?
• The Action is what to do
– Stored procedure or other set of SQL
statements
Copyright © 2013 Curt Hill
SQL Server
• Has several separate triggers
• One for Insert, Update, Delete
– DML triggers
– These usually enforce some business
rule or integrity rule
• One for Create, Alter, Drop, Grant,
Deny, Revoke
– DDL triggers are usually oriented
towards security
• One for Logon
• Today we are only interested in the
Copyright © 2013 Curt Hill
first
Create Trigger
• Form:
Create Trigger name
On { table | view }
{ After | Instead of}
{[Insert], [Update],{Delete]}
As statements
• Next we look at what this means
Copyright © 2013 Curt Hill
In Detail
• The trigger needs to be named so
that it may be dropped or altered
– Normal SQL names
• We must specify either a table or
view name
• We may either do something after
the statement executes or instead of
the statement executing
• We must specify at least one type of
statement
– We may also specify any two or all
Copyright © 2013 Curt Hill
three
After What?
• When After is the option the
statement must successfully
complete
– All the integrity checks must have
finished
• Any cascaded things must also
complete
• If the statement or started cascaded
statements fail there is nothing to
trigger
Copyright © 2013 Curt Hill
Instead Of
• If this is given then the trigger fires
up a replacement
– The original statement is replaced by
what the trigger specifies
• Recursion is disallowed
– An instead of trigger cannot fire itself
Copyright © 2013 Curt Hill
The SQL Statements
• Most of SQL statements may be
used
• Typically a Begin End pair wraps
them
• To implement a condition an If is
usually the only statement
– The action is accomplished only if the if
condition is true
Copyright © 2013 Curt Hill
Conceptual tables
• When doing inserts and deletes you
may use logical tables
• There is a logical table inserted and
deleted
• Inside a trigger on inserts, then this
statement is legal:
Select * from inserted
• There is no updated, but an update
is like deleting the row and then
inserting the updated row
Copyright © 2013 Curt Hill
Example
• In the next screens is a trigger to
show the new value of an update to
the student balance
Copyright © 2013 Curt Hill
Example 1
create trigger bal_increase
on students
after update
as begin
declare @count int,
@bal decimal(8,2)
set @count = 0
declare curses Cursor for
select s_balance
from inserted
open curses
Copyright © 2013 Curt Hill
Example 2
fetch next from curses
into @bal
while (@@FETCH_STATUS >= 0)
begin
set @count = @count + 1
print cast
(@bal as varchar(20))
fetch next from curses
into @bal
end
deallocate curses
Copyright © 2013 Curt Hill
end
Demo
• Perhaps we should do a demo?
Copyright © 2013 Curt Hill