Let’s walk through the steps, understanding what resources are in use and what is occurring as SQL Server processes the statement:

•  Begin transaction —This implicit step acquires a transaction marker for the session. This marker will be used to identify any work performed by this session until a COMMIT TRANSACTION or ROLLBACK TRANSACTION operation (implicit or explicit) takes place.
Transactions guarantee data integrity through the use of locks. Every data modification statement automatically locks affected pages. The locks are held open until a COMMIT or ROLLBACK occurs.

•  Delete data (steps 2 to 5) —The first part of an update is to remove the old version of the rows, along with any index references to those rows. (Notice that all operations are performed at the row level, not the column level.) Deleted data is first logged. Later you will see that a view of the deleted rows is available during the trigger.

---

Note:  SQL Server supports three different update modes: deferred updates, direct updates, and direct updates in place. The direct updates in place are by far the most efficient because rows are not deleted and then updated; instead, the change is made to the row where it sits on the data page and a single modification record is written to the log.

Unfortunately, one of the rules for the direct update in place is that the table must not have an update trigger. An update on a table with an update trigger is always handled as a DELETE followed by an INSERT.

---

•  Insert data (steps 6 to 9) —SQL Server inserts the now-modified rows into the table, along with related index values. Before the inserts occur, the inserted rows are logged. A view of these inserted rows is also available to the trigger.

•  Execute trigger —If there is an UPDATE trigger on the table, it is executed now, after the rows are modified, but while the transaction is still active.

•  Commit (or roll back) transaction —These operations signal the end of a transaction. SQL Server writes changes to the transaction log to disk and releases any locks.

If all of this seems enormously complex and slow, it can be; you have good reason to be concerned. Although it is complex, it’s usually very fast because all the work prior to an actual COMMIT operation takes place in memory.

From a performance standpoint, the most important thing to keep in mind is the locking. When your trigger is running, there are almost always exclusive, blocking locks in place. Other users will end up waiting while your trigger runs. This means that trigger processing has to be minimal (do as little as possible) and very efficient (write your very best SQL). We’ll look at some specific examples of this later today.

Trigger Resources

Triggers have resources other SQL constructions don’t. They can roll back transactions. They can look at the old and new versions of rows modified by a statement. And they can determine which columns were modified. We’ll write some simple examples to show how these resources are used; a little later, we’ll use them in a more complex and realistic context.

Inserted and Deleted Tables

Earlier, I mentioned that the server writes all inserts and deletes to the transaction log prior to writing them to the table. These logged rows are available to a trigger as the inserted and deleted tables.

---

Note:  Sybase, whose SQL Server product is very similar to Microsoft SQL Server, has for years referred to the inserted and deleted tables in class materials as “magic tables.” Others have come up with equally goofy names, believing that a clear description of these tables would overwhelm a SQL novice. Let’s find out.

Actually, inserted and deleted tables are highly restricted views of the transaction log, visible only within a trigger, localized for your session, and displaying only those rows modified by your session and current statement.

Is everybody feeling OK? Phew!

---

Within the trigger code, you can use SELECT with inserted and deleted tables just as you would with any ordinary table. For example, to get a better feeling for what these tables are, you can display their contents inside the trigger code. Go ahead and create this trigger:

create  trigger  tr2
on  t  for  insert,  update
as
print  “============  inserted  table  ==============”
select  *  from  inserted
print  “============  deleted  table  ==============”
select *  from  deleted
return

---

Note:  As usual, when you create an object, SQL Server returns no response if you get it right.

---

insert t (c2, c3) values (88, “jkl”)

============  inserted table  ==============
c1          c2            c3
----------  ----------  ----
7            88           jkl
============  deleted  table  ==============
c1          c2             c3
-----------  -----------  ----

The trigger code shows you the contents of the inserted and deleted tables. The deleted table is empty; no rows are deleted as the result of an insert. The inserted table shows the contents of the row we added.

Notice that the inserted and deleted tables have the same structure as the table itself.

Let’s update some rows now:

update t
set c3 = “qqq”
where c2 between 80 and 90

============  inserted  table  ==============
c1           c2            c3
-----------  -----------  ----
4            80            qqq
5            88            qqq
============  deleted  table  ==============
c1          c2             c3
-----------  -----------  ----
4           80             abc
5           88             jkl

There’s no “updated” table, right? Instead, the deleted table shows you the rows before the update, and inserted shows you the rows after the update.

What kinds of things can you do with the inserted and deleted tables? You can validate column values and row consistency, check referential integrity, determine whether rows are ready for archive, maintain an archive or update derived values, or keep an audit trail, just to name a few. Inserted and deleted tables allow you to look at just the rows that were modified, without having to scan the entire table.

What can’t you do with them? For one thing, you can’t access them from any place other than a trigger. For instance, if you were to execute a stored procedure from within a trigger, the procedure would not be able to access inserted or deleted. You also can’t modify them because they are read-only.