Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
The Effect of Triggers
Triggers are powerful code modules that give you access to the INSERTED and DELETED tables that are not accessible from anywhere else. In addition, you don't have to call triggers explicitly—they execute whenever you INSERT, UPDATE, or DELETE rows in the table on which the trigger is built. However, occasionally you'll spend days and even weeks optimizing a stored procedure when the real problem is the trigger that fires in the background.
Another trick with triggers is that they become a part of an INSERT, UPDATE, or DELETE transaction that fired the trigger. Therefore, the value of the @@TRANCOUNT global variable inside the trigger is always 1 as soon as you enter the trigger. That means that the transaction that fired the trigger can't be committed until the trigger is done executing. Furthermore, if you can't commit the transaction, SQL Server can't release the locks on the appropriate resources. Therefore, enclosing complex business rules inside a trigger can cause serious performance problems.
Although the INSERTED and DELETED tables are not available outside the trigger code, you can gather the same information prior to executing an INSERT, UPDATE, or DELETE statement. For instance, suppose I want to execute the following UPDATE:
UPDATE authors SET zip = '12345' WHERE state = 'ca'
Now suppose you have a requirement to log all changes that occur in the authors table. You need to record author ID, last name, column that was changed, and time of change. Instead of using a trigger, you could stick all the affected records in the table variable as follows:
DECLARE @temp_authors TABLE ( au_id CHAR(11), au_lname VARCHAR(40)) INSERT @temp_authors ( au_id, au_lname ) SELECT au_id, au_lname FROM authors WHERE state = 'ca'
Now that you have all records that will be affected by your update, you can log them in the desired manner:
INSERT log_table (
au_id,
au_lname,
changed_column,
change_datetime
)
SELECT
au_id,
au_lname,
'state',
GETDATE()
FROM @temp_authors