We’ll look at some examples of how to work with inserted and deleted tables after we review the other special resource available to triggers, the UPDATE() function.

The UPDATE() Function

The UPDATE() function lets you check to see if a particular column was modified in the INSERT or UPDATE statement that causes a trigger to fire. With UPDATE(), you can avoid time-consuming data validation routines when a column value has not changed.

As with the inserted and deleted tables, the UPDATE() function is available only within a trigger. It cannot be used in a procedure or in an ordinary T-SQL batch.

Here’s a trigger on our table that checks to see if column c2 has been modified. If so, it ensures that the revised version of the column (the version in inserted) is not negative:

create    trigger    tr3
on   t   for  insert,   update
as
if    update(c2)
begin
   print    “testing   c2”
   if  exists   (select  *  from  inserted  where  c2  <  0)
  begin
      raiserror (“c2 must be positive or zero”, 16, 10)
      rollback transaction
     return
   end
end
return

Now we’re ready to test our trigger. First an INSERT:

insert  t  (c2,  c3)  values  (-10,   “abc”)

testing c2
Msg  50000,  Level  16,  State  10
c2  must be   positive  or   zero

The UPDATE() function is true, so our debugging message (testing c2) is displayed. The inserted value fails, SQL Server returns an error message, and the transaction rolls back (more on ROLLBACK TRANSACTION in a moment).

What, exactly, does the UPDATE() function test? With an INSERT statement, it looks at the SQL statement itself to determine if the column is specified. There are three ways for a column to be specified for an INSERT:

•  The column is included in the column list, or there is no column list (all columns included).

•  The column is not included but has a default specified.

•  The column is an identity column.

Let’s run an UPDATE statement now. First, we’ll leave the column untouched.

update  t
set c3  = “aaa”
where c2  between  10  and  20

(2   row(s)   affected)

Our debugging message is not displayed. The update() test returned false because the column, c2, is not included in the SET clause. SQL Server does not really try to determine if the column value changes; it only makes certain that the column is specified in the SET clause. Try this:

update    t
set  c2  =  c2
where  c2  between  10  and  20

testing  c2
(2  row(s)  affected)

The UPDATE() function is true because the column is named in the SET clause.

One more example allows us to discuss a vital programming point. Here is a list of values for c2 currently in the table:

c2
-----------
25
80
88
10
20

Let’s run an UPDATE statement that modifies all the rows but causes only one row to fail the trigger test:

update    t
set c2  =   c2  -  15

testing   c2
Msg   50000,   Level  16,  State  10
c2   must  be   positive  or   zero

The value that was originally 10 dropped below zero, failing the trigger test. The transaction was rolled back. Given the error message, you might be wondering:

•  How many rows failed to meet the criterion?

•  Which rows failed? What are their primary keys?

Here’s the crucial point: Triggers are not responsible for sorting through the data and letting users know which rows failed and which worked. They are responsible for managing data integrity and preventing invalid data from entering the system.

As a programmer, you will receive error messages from triggers. Those error messages will be frustratingly nonspecific. You will want to know more about the problem values and more about the precise error. Unfortunately, the trigger is not in a position to tell you that. Sure, you could write all sorts of fancy SQL to sort through rows one at a time, perhaps with a cursor. You could return individual values along with brilliantly formatted error messages. But all that time, locks would be held open (remember when triggers execute!), and users would be waiting. This kind of work performed in a trigger is not appropriate.

Always be aware of when triggers execute and what they are doing at the time. In the trigger, raise an error and get out. Leave the rest of the work to a client program.