Transact-SQL Program Structures(Triggers&Procedures) - Chapter 8



    

  1. Triggers
A trigger is a special type of stored procedure that is executed automatically
as part of a SQL data modification statement. A trigger is created on a table
and associated with one or more data modification actions (insert, update, or 
delete). When one of those actions occurs, the trigger fires automatically.

Triggers can guarantee data integrity and consistency, promising that every
modification operates within the integrity requirements of the organization.


  2. Triggers can do all of the following:

. Compare before and after versions of data
. Read from other tables and other databases
. Modify other tables and other databases
. Execute local and remote stored procedures
. Roll back invalid work


  3. Benefits and Uses of Triggers
Now that declarative referential integrity (DRI) handles the brunt of
referential integrity checking, these are the most common applications of triggers:

. Complex defaults- A trigger can refer to the contents of other columns 
  or other tables to determine the default contents of columns in a table.
  In these cases, you also may want to consider an approach that uses a stored
  procedure.

. Complex column constraints-Advanced domain checking (including inter-row
  and multi-table lookups) is often best done in a trigger.

. Nonstandard referential integrity-If your application calls for alternative
  actions in response to RI violations, occasionally permits users to override
  protections, or requires unusual RI checking, a trigger is often the most
  efficient method available.

. Maintenance of dupicate and derived data- Updates to additional tables based
  on changes to one table can be distributed by using a trigger.


  4. When not to use Triggers
Complex or poorly written triggers can create havoc in a system requiring 
good throughput and update performance:

. Triggers hold open locks, prevent updates in place, and can create additional
  transaction log overhead.

. Triggers should be avoided when an application program or stored procedure
  can perform the same work with minimal I/O overhead.

. Triggers should never be used to handle simple data validation or simple DRI.

. In any case, triggers must be written to run quickly and return as soon as
  possible.


  5. Trigger Execution
To write effective triggers, you need to understand trigger execution.  Unlike
stored procedures, triggers can't be executed by name.  Triggers are executed
automatically by the SQL Server,  after all modifications are complete, within
the transaction of the statement that fired the trigger. A trigger is executed
only one time per statement, even if the statement modified several rows.

* A trigger is executed only one time per statement. A tigger needs to be ready
to deal with changes to zero, one, or many rows in the table.

Triggers are fired automatically when the appropriate statement is executed.
For example, if there were an update trigger on the titles table, the trigger 
would fire when a user executed the following query:

update titles
set price = price * 2
where type = "business"

Remember three critical points about this update statement:

. The statement might have modified zero, one, or many rows, depending on the
  data.
. The trigger fires one time, even if the original statement modifies one row,
  many rows, or no rows at all.
. The trigger fires as an integral part of the transaction within which the
  statement operates.


  6. Creating Triggers
You use the Manage Triggers window in the SQL-EM to maintain triggers on tables.
There are two ways to access the Manage Triggers window. The first is to select
Manage | Triggers from the main menu after highlighting the correct database
in the Server Manager window.

To go directly to the triggers on a specific table, right-click the table in the
Objects list. From the pop-up menu that appears, choose Triggers to open the 
window.

You can manage triggers from any SQL editing session (ISQL/w or the Query
Analyzer). The Manage Triggers window simplifies trigger management by allowing
you to see at a glance the triggers on a specific table and to quickly retrieve
the existing text of a trigger.

Because a trigger is a SQL code-based object, you need to know some Transact-SQL
syntax to create it. Here's the basic create trigger statement:

create trigger trigger_name
on table_name 
for {insert | update | delete [, ...]}
as
SQL-statements
[return]

There are a few things that you should notice about the statement itself:

. Triggers are objects and must have a unique name in the database.
. A trigger is created on a table for an action or set of actions. A trigger
  can respond to several actions (insert, update, and/or delete). A trigger
  cannot be placed on more than one table.
. While you can't replace a trigger with another having the same name without 
  dropping the original trigger first, you can replace a trigger with another
  performing the same action on the same table.

Here's the create trigger statement that reports the number of rows whenever
employees are deleted:

create trigger tr_employee_d on dbo.employee
FOR DELETE
AS
declare @msg varchar (50)
select @msg = str (@@rowcount) + " employees deleted by this statement"
print @msg
return

* The number of rows affected by the statement firing the trigger is available
when executing the trigger in the @@rowcount global variable.


  7. Dropping and Replacing Triggers
Triggers can't be modified in place. You need to drop an object before 
creating another another with the same name. To drop a trigger, use the drop
trigger command:
 

drop trigger trigger_name

* Triggers are automatically dropped when the referenced table is dropped.

When a new trigger, tr_titles_i2, is assigned to handle inserts, the first
trigger is automatically dropped because it isn't responsible for any action.

Tip: If you want to test the trigger without deleting employees, execute
a begin transaction statement before starting this work.  When you're finished
testing the trigger, execute a rollback transaction to undo all the deletions
and return the rows to the table.

Before dropping a trigger, always check the dependencies (Show Dependencies)
of which you might have been unaware.


  8. Special Trigger Syntax
Before starting to write triggers, you need to learn their special syntactic
capabilities:

. Access to versions of rows before and after modification with the INSERTED 
  and DELETED tables.

. Information about which columns were modified by a statement with the UPDATE()
  function


  9.  inserted and deleted tables
The inserted and deleted tables are actually views of the transaction log having the same structure as the table on which the trigger was created.  Consider 
this statement:

update titles
set price = $15.05
where type like "%cook"

When the statement is executed, a copy of the rows to be modified is recorded
in the log, along with a copy of the rows after modification.  These copies are 
available to the trigger in the deleted and inserted tables, respectively.

Tip: If you want to see the output displayed in the following examples, create 
a copy of the titles table, and then create a trigger like this one on your copy:

create trigger t1 on titles
for insert, update, delete
as
print "inserted"
select title_id, type, price from inserted
print "deleted"
select title_id, type, price from deleted
rollback transaction
return

Using this approach, you can execute a data modification statement and review
the contents of inserted and deleted without changing the contents of the table.


  10. Scope of INSERTED and DELETED tables
The INSERTED and DELETED tables are local to the current trigger. This means
that each trigger that's executed will materialize its own versions of those
tables from the contents of the log for the current statement in the current
context. INSERTED and DELETED aren't available if the trigger calls stored
procedure, and if the trigger executes an action causing another trigger to
fire, that trigger will see only its own INSERTED and DELETED tables.

You can't create a temporary table within a trigger, but, you can use a 
predefined local or global temporary table or a permanent worktable.

select *
into #publishers
from publishers
where 1 = 2

Notice that the temporary table must exist when the trigger is created, and
of course must exist when the trigger executes.

In this example, the trigger copies the contents of inserted into the temporary
#publishers table:

create trigger p
on publishers for update
as
insert #publishers
select * from inserted
return

To test the trigger, update the publishers table, in this case affecting every
row but making no actual change to the data:

update publishers
set city = city

After the trigger has run, the user has access to the temporary table with the
contents of the inserted table.

select * 
from #publishers


* For reliability, you may choose instead to use a permanent worktable for 
  all trigger executions.


  11. Trigger Restrictions
Triggers can't create or drop objects or perform work that affects the contents
of the transaction log, including any of these commands:

. create or drop objects
. select .. into
. alter table
. Alter database
. truncate table
. grant or revoke
. update statistics
. reconfigure
. load database or load transaction
. Disk commands (disk, init, disk mirror, etc.)


  12. Triggers and Transactions
One of the most common applications of triggers is to perform complex row
validations. If the trigger determines that the data modification statement 
that fired it was invalid, it can roll back the transaction. To do so, you
execute the rollback transaction within the trigger itself.
When a trigger executes rollback transaction:

. All work performed by the trigger is rolled back.
. All work performed by the statement and its entire transaction is rolled back.
. The trigger runs to completion.
. When the trigger returns, the batch continues processing.

Create a table with one or two columns for testing trigger behavior:

create table trigtable
(c1 int, c2 int)



Example, write an insert trigger that prints messages before and after you
roll back the transaction:

create trigger instring on trigtable for insert as
print "inside trigger before rollback"
rollback transaction
print "inside trigger after rollback"
return

Execute a batch that prints messages before and after the insert statement:

print "in batch before insert"
insert trigtable values (1,1)  
print "in batch after insert"

Here's the output from  the batch:

in batch before insert
inside trigger before rollback
inside trigger after rollback

(1 row(s) affected)

in batch after insert

What doesn't rollback transaction do? It doesn't send a print statement, 
an error message, or any warning at all to the user.  It's critical to raise
an error when executing a rollback, because your error message is the only
warning the user will receive that the transaction failed.

Here's an example of a trigger that rolls back transaction on Sunday:

create trigger tr_publishers_d
on publishers for delete
as
if datepart (dw, getdate()) = 1 /* Sunday */
begin
	raiserror ("Cannot delete publisher on Sunday", 16, 1)
	rollback tran
end


  13. Special RI: Cascade delete

Referenced Table	  Referencing Table
stores (pk:stor_id) ----> sales (fk:stor_id)

A deletion to a referenced table (stores) results in the deletion of all
related rows in referencing tables.

Here's the delete statement that should fire the trigger:

delete stores
where stor_id like "70%"

A cascade delete trigger is always placed on the referenced table and deletes
rows from the referencing table.

create trigger trigger-name
on referenced-table for delete
as
if @@rowcount = 0
   return
delete referencing-table
from deleted d, referencing-table r
where d.join-key = r.join-key
if @@error != 0
begin
   raiserror ("error encountered in trigger processing", 16, 1)
   rollback tran
   return
end
return


In this example of a cascade delete trigger, the stores table is referenced,
meaning that the trigger should cascade deletes from the store table to the
sales table.

create trigger tr_stores_d
on stores for delete
as
if @@rowcount = 0 
   return
delete sales
from deleted d, sales s 
where d.stor_id = s.stor_id
if @@error != 0
begin
   raiserror ("error encountered in trigger processing", 16, 1)
   rollback tran
   return
end
return


  14. A Detailed Look 

See pages 256 & 257


  15. Nested Triggers
When a trigger modifies another table, ordinarily the trigger on the second
table is fired. This is called nested triggers. By default, the server will
execute nested triggers up to 16 levels deep.


A system configuration setting determines whether SQL Server will execute
nested triggers. The sa can execute this statement to turn off nesting
(a value of 1 turns it back on):

sp_configure 'nested triggers', 0


  16. Special RI: Trickle insert
An insert to a referencing table is normally blocked if the foreign key can't
be found in the referenced table. In this example, the user needs to insert 
a new row on-the-fly into the referenced table. This is sometimes called
trickling an insert. These statements should fire the trigger:

insert sales (stor_id, ...)


insert sales (stor_id, ...)
select stor_id, ...
from #sales

A trickled insert trigger is placed on the referencing table and inserts
rows into the referenced table. 

create trigger trigger-name
on referencing-table for insert
as
declare @rows int
select @rows = @@rowcount
if @rows = 0 
   return
if @rows <> (select count(*)
   from inserted i, referenced-table r
   where i.join-key = r.join-key)
if @@error <> 0 
begin
   raiserror ...
   rollback tran
end
begin
   insert referenced-table (col-list)
   select distinct col-list
   from inserted
   where join-key not in
      (select join-key
       from referenced-table)
   if @@error <> 0 
   begin
      raiserror ...
      rollback tran
   end
end
return


One thing you will notice about this trigger is that there may be several
column values for the referenced table that are unavailable or will need
to use default values. For this reason, the trickle may be appropriate
only in extraordinary circumstances.

create trigger tr_sales_i 
on sales for insert
as
declare @rows int
select @rows = @@rowcount
if @rows = 0 
   return
if @rows <> (select count(*)
   from inserted i, stores s
   where i.stor_id = s.stor_id)
if @@error <> 0 
begin
   raiserror ("error in tr_sales_i", 16, 1)
   rollback tran
end
begin
   insert stores (stor_id)
   select distinct stor_id
   from inserted
   where stor_id not in
      (select stor_id
       from sales)
   if @@error <> 0 
   begin
      raiserror ("error in tr_sales_i", 16, 1)
      rollback tran
   end
end
return


  17. Override Trigger Validation

If an appication needs to permit only certain users to make modifications 
to data, using some sort of override key, you can use a trigger to permit 
those changes. In this example, a negative qty in the sales table represents 
a merchandise credit for a customer, which only selected users may perform.

This tigger allows negative amounts only if a value of "Y" is inserted in an
override column in the same row. Only certain users have been granted 
permission to update that column.

To set up this example, add the override column to the sales table, and then
grant the proper permissions on the table and column:

alter table sales
	add override char(1) default "N" null
revoke update on
	sales(override) from public
grant update on
	sales (override) to supervisors

create trigger tr_sales_u
on sales for update
as 
if @@rowcount = 0 
   return
if update(qty)
begin
   /* check for new rows with negative qty, no override */
   if exists (select * from inserted
      where qty < 0
      and override <> "Y")
   begin
   raiserror ("invalid update", 16, 1)
   rollback tran
   return
   end
update sales
set override = "N"
from sales s, inserted i
where s.stor_id = i.stor_id
and s.title_id = i.title_id
and s.ord_num = i.ord_num
and s.override = "Y"
end
/* if @@error <> 0 ... */
return


  18. Trigger Summary
Tiggers hold exclusive locks and are active in the transaction log.  They are
slow compared to other, non-SQL-based structures like constraints, rules, and
defaults.  But they're faster than many other mechanisms used to manage data
integrity (particularly client-based tools), and they provide nearly bulletproof
resistance to data corruption when written properly.

Triggers must be

. Fast

. Efficient

. Effective on the multi-row case

. Well tested

Triggers should not

. Try to wait for user input

. Select data for the user

. Sort out bad and good data

. Use cursors

Triggers can play a critical part in the implementation of a typical database
design.  Whether providing access to advanced referential integrity capabilities
or performing automatic maintenance of denormalized data, triggers allow you 
to implement the kinds of complex, real-life business rules that make systems
effective and robust.

Last update February 1, 1998.