My Trigger Naming Convention

There are lots of ways to name triggers. Before you adopt my conventions, find out if there is a naming convention in your company. If there isn’t, here’s one approach that has worked well for me.

A trigger name consists of four parts: a trigger signifier, the name of the affected table, the table action(s), and a version identifier. For example, version 1A of an insert and delete trigger on the publishers table would have this name:

tr_publishers_id_1A

When you have more than one trigger on a table, each trigger will have a different name. The name component _id_ stands for (i)nsert (d)elete. Another trigger will have a different set of actions.

If you follow this or a similar naming convention, you will know many things about the trigger just from its name, and you can scan objects more quickly when you need to find triggers referencing a particular table.

Executing Triggers

Let’s execute the trigger. You can’t execute it directly, so the only way to make the trigger run is to modify the table. We’ll start with an INSERT statement.

insert  t  (c2,  c3)  values  (80,  “abc”)

1  rows  modified  (trigger  message)
(1  row(s)  affected)

The first line is our trigger message; the second is the standard row count information from ISQL/w. If you look at the output, it seems as if the trigger fired before our row was inserted, but that’s not the case. The last line (1 row(s) affected) is generated not by SQL Server, but by ISQL/w. The trigger actually fired after the INSERT was processed.

When Do Triggers Fire?

To get a clearer picture of when the trigger actually executes, we’ll execute an UPDATE statement. First, let’s look at the contents of the table:

select  *
    from t

c1             c2             c3
-----------     ----------     -----
1              10             xxxx
2              25             yyyy
3               20             (null
4              80             abc

update  t
set  c3  =  “rstu”
where c2 between 20 and 25

2  rows  modified  (trigger  message)
(2  row(s)   affected)

Two rows were modified by the UPDATE statement. The trigger executes just once, when it reports the number of rows modified.

This is the key point: No matter how many rows were modified by the trigger, the trigger executes only one time.

What if no rows are modified by a statement? The trigger still executes:

update t
set c3 = “rstu”
where c2 < 0

0  rows  modified  (trigger  message)
(0  row(s)  affected)

Even when a query affects no rows, the trigger executes. This is such an important point that it bears repeating. No matter how many rows are affected by a query, a dependent trigger executes once and only once. When you start writing triggers, you’ll need to take into account all of the possibilities:

•  One row was affected.

•  Many rows were affected.

•  No rows were affected.

Of course, dealing with all of these cases usually makes your trigger code a little more complex.

How Are Triggers Executed?

In order to help you write better triggers, I’ll go over what happens when a trigger is fired. Once you understand its context and the mechanisms that are used to manage and execute triggers, you’ll be able to predict system behavior and write better SQL. Let’s look closely at an UPDATE statement and a related update trigger. This is the most complex action performed by the server, and from its behavior you can guess how inserts and deletes are handled.

Figure 19.1 lays out the steps performed while a trigger is executed. SQL Server treats updates as a DELETE (steps 2 through 5) followed by an INSERT (steps 6 through 9). After all this work is completed, but before the transaction is committed, the server fires the trigger (step 10).

Figure 19.1.  All updates are treated as a DELETE followed by an INSERT.

---

Note:  Let me be very careful here. This statement has caused some confusion in classes I’ve taught. In terms of the physical manipulation of the data, an UPDATE is treated as a DELETE followed by an INSERT. In terms of the logical operation, the DELETE and INSERT are not visible to the user. For instance, an INSERT trigger is not fired as a result of an UPDATE statement.

---