Triggers

Description

Triggers are like stored procedures except that they dont take parameters and they are executed--triggered--by one of three events: Insert, Update, and or Delete. Triggers can be used to enforce referential integrity rules (though Primary Key and Foreign Key constraints are a better way.). They can also be used to enforce restrictions on the values entered into a field (though again Check constraints are a better option). Probably the most important use of triggers is to enforce business rules that cannot be built into the referential integrity rules of the database.

In SQL Server2005 there are several kinds of Triggers

DDL triggers are triggered by events like Create Table, Creat Procedure, Create User, etc. We are not going to look at that kind of trigger

FOR triggers and AFTER triggers are actually synonymous. Both execute after the triggering action is completed. INSTEAD OF triggers execute instead of the triggering command.The syntax for creating a trigger is similar to a procedure:

CREATE TRIGGER [trigger name] ON [tablename]
[FOR, AFTER or INSTEAD OF] --that is one of these
[UPDATE, INSERT, DELETE] --one or more of these
AS
[SQL statements]

To make changes to the Trigger change the CREATE to ALTER or DROP the trigger and run the CREATE again.

Here is an example using the SimpleInventory database of an After trigger that checks the InventoryInStock field after an update and returns a message if the number in inventory is below a certain number.

Create trigger StockNotice on Inventory
After update
As
Declare @InStock int
Declare @InventoryItem varchar(40)
Declare @InventoryID int
Select @InventoryID=InventoryID
from Inserted
Select @inventoryItem=inventoryitem, @instock=InventoryInStock 
from Inventory
where InventoryID=@InventoryID
If (@inStock < 3)
Begin
Print 'Order more of this item'
print @inventoryitem
end

Inserted is a temporary table created by SQL Server for every Insertion and Update. Only triggers can access this temporary table.

If you save the trigger and run the following code you can trigger the trigger and see how it works.

Update Inventory
Set InventoryInStock=2
where InventoryID=1000

Just as INSERT and UPDATE have a temporary table Inserted, DELETE has a temporary table called Deleted. The Following trigger uses the deleted table to create a table to store deleted items for later. I have added comments to explain the code logic.

Create trigger tr_SaveDelete on Customer
For Delete --exicutes after the delete
As
--check to see if the custtemp table exists
--if not create it
if  not exists (select name from sys.Tables where name='CustTemp')
Begin
Create Table CustTemp
(
  CustomerID int,
  Lastname varchar(20),
  Firstname varchar(20)
)
End
Insert into custTemp (CustomerID, Lastname, firstname)
Select CustomerID, Lastname, firstname from Deleted

To test this trigger, first insert an new customer into the customer table and then delete him or her. (You may not be able to delete the existing customers because of relational integrity constraints).

Select from the customer table to see if the deletion has occurred. Then select from the CustTemp table to see the deleted row. Insert another customer and delete him or her again to see the trigger operate with the custTemp table already in existence.

Instead of triggers

As mentioned before, Instead of triggers occur instead of the triggering event. Here is a simple example.

Create trigger tr_NoDelete on Inventory
Instead of Delete
AS
Print 'You cannot delete Inventory Items'

Attempt to delete anything from the table. (The trigger will intercept DELETE actions before any referential integrity violations could occur)