Checking for Column Updates

The UPDATE() function is available inside INSERT and UPDATE triggers. UPDATE() allows a trigger to determine whether a column was affected by the INSERT or UPDATE statement that fired the trigger. By testing whether a column was actually updated, you can avoid performing unnecessary work.

For example, perhaps a rule mandates that you cannot change the city for an author (a silly rule, I agree, but it will demonstrate a few key concepts). Listing 29.4 creates a trigger for both INSERT and UPDATE on the authors table in the Pubs database.

Listing 29.4 Using the UPDATE() Function in a Trigger

 CREATE TRIGGER tr_au_ins_upd ON authors FOR INSERT, UPDATE AS IF UPDATE(city)  BEGIN  RAISERROR ('You cannot change the city.', 15, 1)  ROLLBACK TRAN  END GO UPDATE authors SET city = city WHERE au_id = '172-32-1176' Server: Msg 50000, Level 15, State 1, Procedure  tr_au_ins_upd, Line 5 You cannot change the city. 

This is how you generally write triggers that verify the integrity of data. If the modification violates an integrity rule, an error message is returned to the client application and the modification is rolled back.

The UPDATE() function evaluates to TRUE if you reference the column in the UPDATE statement. You did not actually change the value for city, (setting it to itself), but you referenced the column in the query.

NOTE

If you created the tr_au_upd trigger on the authors table as part of the AFTER trigger example earlier in this chapter, then you might have also seen the TRIGGER OUTPUT: 1 rows were updated message. This trigger was set to be the first trigger to fire and executes in addition to the new ins_upd trigger that was added in the example from this section.

Now try a couple of INSERT s on the authors table:

 INSERT authors (au_id, au_lname, au_fname, city, contract)  VALUES('111-11-1111', 'White', 'Johnson','Menlo Park', 1) Server: Msg 50000, Level 15, State 1 You cannot change the city. 

The UPDATE() function evaluated to TRUE and displayed the error message. This is expected because the trigger was created for INSERT as well, and the IF UPDATE is evaluated for both inserts and updates.

Now change the INSERT statement so that it does not include the city column in the INSERT and see what happens:

 INSERT authors (au_id, au_lname, au_fname, contract)  VALUES('111-11-2222', 'White', 'Johnson', 1) Server: Msg 50000, Level 15, State 1 You cannot change the city. 

The error message is still displayed even though the INSERT was performed without the city column. This might seem counter-intuitive, but the IF UPDATE condition will always return a TRUE value for INSERT actions. This is because the columns have either explicit default values or implicit (NULL) values inserted even if they are not specified. The IF UPDATE conditions see this as a change and evaluate to TRUE .

If you change the tr_au_ins_upd trigger to be for UPDATE only (not INSERT and UPDATE ), then the inserts can take place without error.