When an INSERT, UPDATE or DELETE statement is executed, rows are added to the trigger table and to the inserted or deleted table(s) at the same time. Deleted and inserted tables can be examined by the trigger to determine whether or how the trigger action(s) should be carried out. Deleted tables are used with DELETE and UPDATE; inserted tables are used with INSERT and UPDATE. Thus, the rows in the inserted table are always duplicates of one or more rows in the trigger table.
Triggers are often used to roll back that transaction if an error is detected.
A trigger is activated only once per command or once per single data modification statement (even if it affects multiple rows). A complex query containing a WHILE loop can repeat an UPDATE or INSERT many times, and the trigger is activated each time.
If a desired result (such as a summary value) depends on the number of rows affected by data modification, use the global variable @@ROWCOUNT to test for modification to multiple rows (an INSERT, DELETE, or UPDATE based on a SELECT statement) and take appropriate action. Any Transact-SQL statement that does not return rows (such as an IF statement) sets @@ROWCOUNT to 0, so be sure to test with @@ROWCOUNT at the beginning of a trigger.
An UPDATE or INSERT trigger can use the IF UPDATE statement. It tests whether the UPDATE or INSERT action changed a particular column. IF UPDATE (column_name) is true for an INSERT statement whenever the column is assigned a value in the select list or in the VALUES clause. IF UPDATE (column_name) is true for an UPDATE statement if the column is assigned a value in the SET clause. An explicit NULL or a default assigns a value to a column and thus activates the trigger. An implicit NULL, however, does not assign a value to a column and does not activate the trigger.