Michael Dawson


I have the need to use triggers in my database, but am a little foggy on something that reading the official msdn help did not quite clarify. If for example there is a trigger which is triggered when a certain record is updated (DDL ) Is it possible to see what the values were before and after the update I need to create a trail of updates such that the information can be rolled back if need be.

Any help with this would be greatly appreciated. Thank you :)



Re: trigger question

Robert Davis


DDL triggers modify schema and not data. If any data in a column or table does not conform under the a new schema, the DDL statement will will fail. Data can be lost if DDL drop commands are executed however. There is no DDL mechanism similar to the inserted and deleted virtual tables DML triggers have.

If you meant to say for DML triggers, there are the deleted and inserted virtual tables that you can access via the trigger. For insert commands, new rows are in the inserted table, deleted is empty. For delete commands, the deleted rows are in the deleted table, inserted is empty. For updates, the new version of the rows are in the inserted table and the old version of the rows are in the deleted column.

 






Re: trigger question

nippysaurus

Thank you robert :)

Someone else gave me a bit of sample code, but their explanation was not as fleshed out as yours. So between the two I understand what I need to.

Thank you for your help :)