Using Trigger Resources

Triggers have access to resources and information that other T-SQL statements don't. They can roll back transactions, undoing an action. They can look at both the old and new versions of rows that are being modified by a statement. And they can determine which columns were modified. To see how all this works, we will write some simple examples to show how these resources are used.

Accessing the INSERTED and DELETED Tables

In the section where I described the program flow of an UPDATE statement, you saw that all the deletes and inserts are written to a log file. Those logged rows are available to a trigger as the INSERTED and DELETED tables. SQL Server 2000 automatically creates and manages these two tables. You can use the temporary tables to test the effects of certain changes and to set the condition for trigger actions; however, you cannot change the data in the tables directly.

The DELETED table stores copies of the affected rows from the UPDATE and DELETE statements. During the execution of either of these statements, the rows are deleted from the trigger table and written to the DELETED table.

The INSERTED table stores copies of the affected rows from the UPDATE and INSERT statements. During an insert or update transaction, new rows are added simultaneously to both the trigger table and the INSERTED table.

Within the trigger code, you can use a SELECT statement with the INSERTED and DELETED tables just like you can any other table in the database. For example, to get a better idea of what these tables are, you can display their contents when a trigger fires. Create the following trigger on the Customers table:

Use Northwind
Go
Create Trigger tr_Table_Demo
On Customers
For Insert, Update
as
Select Customerid, city, country from Inserted
print '****Deleted Table Info****'
Select Customerid, city, country from Deleted

Now, execute the following simple update on the Customers table:

update customers
set city = 'New York'
Where city = 'Paris'

Results:

****Inserted Table Info****
Customerid city            country
---------- --------------- ---------------
PARIS      New York        France
SPECD      New York        France

(2 row(s) affected)

****Deleted Table Info****
Customerid city            country
---------- --------------- ---------------
PARIS      Paris           France
SPECD      Paris           France

(2 row(s) affected)

The trigger will display the contents of the INSERTED and DELETED tables. If the requested action was an insert, the INSERTED table would have data in it and the DELETED table would be empty. You should also notice that the INSERTED and DELETED tables have the same structure as the table you are working with, right down to the column names.

Using these tables in a trigger, you can validate column values and row consistency and determine whether rows are ready for archiving, maintain an archive, or update derived values, or even create an audit trail of any modifications to a table.

Using the UPDATE() Function

The UPDATE() function allows you to check whether a particular column was modified in the triggering INSERT or UPDATE statement. As with the INSERTED and DELETED tables, the UPDATE() function is available only within the scope of a trigger. Listing 18.1 shows an example of a trigger on the Customers table that uses the UPDATE() function to determine whether the City column was updated.

Code Listing 18.1. Creating a Trigger on the Customers Table

01:Create Trigger tr_CustTable_Demo
02:On customers
03:For Insert, Update
04:as
05:If Update(city)
06:begin
07:    If (Select Distinct country from Inserted) <> 'USA'
08:    Begin
09:         Raiserror('Cannot update International Cities',16,10)
10:         Rollback Transaction
11:    end
12:end

To test this new trigger, execute the following SQL statement:

update customers
set city = 'New York'
Where city = 'Paris'

Results:

Validating the City Input
Server: Msg 50000, Level 16, State 10, Procedure tr_Table_Demo, Line 11
Cannot update International Cities

You can see that the UPDATE() function is true, so the message stating that the city input was being validated is printed. Because the rows selected do not have 'USA' in the Country column, the update is cancelled using the ROLLBACK TRANSACTION statement (you will learn more about that statement in the next section).

Triggers do not sort through the data and inform you which rows failed and which actually worked. They are responsible for managing data integrity and preventing invalid data from entering the system.

Using Rollback in a Trigger

You already know how to use the ROLLBACK TRANSACTION statement as well as the COMMIT TRANSACTION statement from Day 13, "Programming with Transactions." As you know, when ROLLBACK TRANSACTION is executed, the server will reverse all the work performed since the outermost BEGIN TRANSACTION statement. If there was no explicit BEGIN TRANSACTION statement, the ROLLBACK TRANSACTION will reverse all the work since the process was started.

You have already seen how this works in Listing 18.1 where we updated the Customers table. But what if you had written a transaction in which you performed three separate updates to the table as shown in the following SQL code?

Select City, Country From Customers Where Country = 'USA'
Begin Transaction
    Update Customers Set City = 'New York' Where City = 'Portland'
    Update Customers Set City = 'Albany' Where City = 'Seattle'
    Update Customers Set City = 'New York' Where City = 'Paris'
Commit Transaction
Go
Select City, Country From Customers Where Country = 'USA'

When you execute this transaction, you would see the following output:

City            Country
--------------- ---------------
Eugene          USA
Elgin           USA
Walla Walla     USA
San Francisco   USA
Portland        USA
Anchorage       USA
Albuquerque     USA
Boise           USA
Lander          USA
Portland        USA
Butte           USA
Kirkland        USA
Seattle         USA

(13 row(s) affected)

Validating the City Input

(2 row(s) affected)

Validating the City Input

(1 row(s) affected)

Validating the City Input
Server: Msg 50000, Level 16, State 10, Procedure tr_Table_Demo, Line 11
Cannot update International Cities
City            Country
--------------- ---------------
Eugene          USA
Elgin           USA
Walla Walla     USA
San Francisco   USA
Portland        USA
Anchorage       USA
Albuquerque     USA
Boise           USA
Lander          USA
Portland        USA
Butte           USA
Kirkland        USA
Seattle         USA

(13 row(s) affected)

If you take a close look, you will notice that none of the updates were actually committed to the table. The reason for this is that the last update was invalid and the trigger executed a ROLLBACK TRANSACTION statement. This in effect cancelled all the updates because they were not yet committed. To prevent this from happening, you must be very careful how you code transactions that affect tables with triggers. The following modified SQL code would perform what you expected from the preceding code:

Select City, Country From Customers Where Country = 'USA'
Begin Transaction
    Update Customers Set City = 'New York'Where City = 'Portland'
    Commit Transaction
    Update Customers Set City = 'Albany'Where City = 'Seattle'
    Commit Transaction
    Update Customers Set City = 'New York'Where City = 'Paris'
    Commit Transaction
go
Select City, Country From Customers Where Country = 'USA'

This is just one of the possible ways to write transactions with tables that have triggers.

Another way is to change the trigger to commit the update if it is validated correctly, as shown in the following example:

Create Trigger tr_CustTable_Demo
On customers
For Insert, Update
as
If Update(city)
begin
    If (Select Distinct country from Inserted) <> 'USA'
    Begin
         Raiserror('Cannot update International Cities',16,10)
         Rollback Transaction
         Return
    end
    Commit Transaction
end

Make sure that whatever method you use, you handle any errors that might roll back a transaction without having any 'good'actions cancelled at the same time.