Programming the Trigger

It is a good idea, as mentioned earlier, to apply conditional or evaluation logic to your trigger and, if necessary, prevent certain operations from being run by the trigger if there is no longer a need for the trigger to complete. For example, there might not be a need to complete a trigger statement after an update, if nothing was updated.

You can use the UPDATE(column_name) and COLUMNS_UPDATED() statements respectively to check for the completion of updates that apply to certain columns.

You can use the IF UPDAIE(column_name) clause in your trigger code to determine if the DML (INSERT or UPDATE) statement that fired the trigger, or an earlier one, actually made any changes to write home about. This function will return true if indeed the column was assigned a value. The IF UPDATE() trigger will look like this:

 CREATE TRIGGER myTrigger  ON myTable  FOR INSERT   AS IF UPDATE(x)   EXEC DotNetMailer.Send 'Jshapiro', 'Column x updated'

Or you can use the IF COLUMNS_UPDATED() clause to check which columns in a table were updated by an INSERT or UPDATE statement. This clause makes use of an integer bitmask to specify the columns to test. And the COLUMN_UPDATED() trigger looks like this:

 CREATE TRIGGER myTrigger  ON myTable  FOR INSERT   AS IF (COLUMN_UPDATED() & 1 = 1)   EXEC DotNetMailer.Send 'Jshapiro', 'Column x updated'

Unfortunately, a limitation of both of the preceding functions is that they cannot test to see if a specific value in a field has been deleted; thus, neither function will return any result on a delete statement. However, you can check for row deletion using the @@ROWCOUNT function, which returns the number of rows affected by the last query.

The function @@ROWCOUNT returns the number or rows that were deleted as @@ROWCOUNT=X. Thus you can test for rows and make flow choices based on the results (see the stored procedure example in the next section). If, for example, the row count returns 0, you could exit the trigger or take some other action. If the row count is greater than 0, you could switch to a different code segment or even use a GOTO and code in a series of GOTO labels.