I have written previously about the basics of triggers in MS-SQL. Today, I am going to look at using cursors as the proper way to access data in a trigger. (See my previous blog about using cursors.)
It is considered proper to use cursors in triggers, as there may be more than one record inserted, updated, or deleted, at a time. SQL may batch multiple inserts, updates, or deletes, together when it executes. So, even though you have, separate insert, update, or delete, statements, it is possible that SQL will batch several of those inserts, updates, or deletes, together when it actually performs its processing. Therefore the use of a cursor is called for in order to step through all of the records that were inserted, updated, or deleted.
By using a cursor, on the appropriate table, you make sure that each record is processed. Which table to use? For insert triggers, the table name is INSERTED. For delete triggers it is DELETED. The interesting one is an update triggers, as it uses both the INSERTED and DELETED tables. During updates, the INSERTED table contains the new data, and the DELETED table contains the old data.
The INSERTED, or DELETED, table has an identical schema to the table that the trigger is on. So, if you define an insert trigger for your INVOICES table, the INSERTED table will have the exact same column names and definitions as the INVOICES table. So, if you wanted to process for each TICKET_NO that was inserted, the cursor declaration would be:
DECLARE DATA_CURSOR CURSOR for SELECT TICKET_NO FROM INSERTED
Also, since the INSERTED and DELETED tables are just that, tables, you can perform joins, order by, where, and just about any other filtering that you would do on any table.
So that is the short-and-sweet explanation for using cursors to process the data in triggers.