SQLConfiguration SQLConfiguration


SQL-Server Triggers  «Prev  Next»
c
Lesson 3 Types of triggers
Objective Temporary tables used to verify accuracy of trigger

Describe how temporary tables can be used to verify the accuracy of a trigger.

There are three types of trigger:
  1. Insert triggers fire when data is added or inserted into a table.
  2. Delete triggers fire when data is removed or deleted from a table.
  3. Update triggers fire when data is changed or updated in a table. Alternatively, you can indicate specific columns that cause the trigger to fire.

Temporary trigger tables

SQL Server maintains two temporary tables for you to validate transactions before they are processed:
  1. Inserted: Populated with the data that was changed by the INSERT or UPDATE Transact-SQL statement.
  2. Deleted: Populated with the data that was changed by the DELETE or UPDATE Transact-SQL statement.
The following Slide Show demonstrates a process that you can use to verify the accuracy of data that is inserted, updated or deleted.

Trigger Types

The first ingredient in properly employing triggers is to understand the differences between AFTER and INSTEAD OF triggers. AFTER triggers are the same type of trigger that is available in previous versions of SQL Server.
They are also known as "FOR triggers" or even simply as "triggers" since they were the only type of trigger available prior to SQL Server 2000.
Let's first look at FOR triggers. You'll notice that the following trigger is created using the FOR keyword:

CREATE TRIGGER tr_Employees_U on Employees FOR UPDATE AS
   IF UPDATE(lastname)
   BEGIN
       RAISERROR ('cannot change lastname', 16, 1)
       ROLLBACK TRAN
       RETURN
   END
GO

It is important to know that your code cannot perform any inserts, updates, or deletes to or from the Inserted or Deleted tables. These tables can only be selected. If you want to get an idea of the data that is contained within the Inserted and Deleted special tables, you can select the data from each of these tables within the definition of the trigger itself. You must do it within the trigger because these tables are available only to the trigger, and not outside it. Also, make sure to remove these SELECT statements before you make your trigger live. You do not want your customers to see this. It is only for testing.
Why are temporary tables important? The rest of this lesson shows how these special tables are used to validate changes to your tables before they are made permanent by SQL Server.
In the next lesson, the enforcement of business rules with triggers will be discussed.