I am using SQL Server Management Studio v18 and I am trying to write a single trigger for insert delete and update.
For that, I am planning to use the inserted, deleted and updated views available.
But when I try to use them, it seems like none of the views are available. I have also checked the system views and could see the above views are not available there.
Can someone help please?
Thanks in advance!
Advertisement
Answer
In the context of a trigger you can refer the deleted and inserted logical (conceptual) tables.
They’re structurally similar to the table on which the trigger is
defined, that is, the table on which the user action is tried. The
deleted and inserted tables hold the old values or new values of the
rows that may be changed by the user action.
You can find more details here but the point is you need to create the trigger and refer them in its body. They will be visible only there and the SSMS is clever enough to autocomplete its columns (the same as the table columns the trigger is defined on).
The tables are not DMV/system views. That’s why you can find them.
Also, there is not updated view. If a record is deleted – its data lives in the deleted table and if a record is created its data lives in the inserted table. If there is an update, the record has data in both of the tables.