Output Trigger

Question

What is output trigger?also give an example.

Akkiraju Ivaturi · Answer

Follow the URL from MSDN: http://msdn.microsoft.com/en-us/library/ms177564.aspx Triggers Columns returned from OUTPUT reflect the data as it is after the INSERT, UPDATE, or DELETE statement has completed but before triggers are executed. For INSTEAD OF triggers, the returned results are generated as if the INSERT, UPDATE, or DELETE had actually occurred, even if no modifications take place as the result of the trigger operation. If a statement that includes an OUTPUT clause is used inside the body of a trigger, table aliases must be used to reference the trigger inserted and deleted tables to avoid duplicating column references with the INSERTED and DELETED tables associated with OUTPUT. If the OUTPUT clause is specified without also specifying the INTO keyword, the target of the DML operation cannot have any enabled trigger defined on it for the given DML action. For example, if the OUTPUT clause is defined in an UPDATE statement, the target table cannot have any enabled UPDATE triggers. If the sp_configure option disallow results from triggers is set, an OUTPUT clause without an INTO clause causes the statement to fail when it is invoked from within a trigger.

Akkiraju Ivaturi · Answer

I have a table with two columns ID and Name and the requirement is whenever I insert data into the table I need to insert in audit table too. For this we need to write a Instead of Trigger. Audit table has 3 columns ID, Name and Action. Action column needs action performed on the mytable. If it is insert then 'I', Delete 'D' and Update 'U'. create trigger myinserttrigger on mytable as begin insert into mytable(ID,Name) OUTPUT inserted.ID, inserted.Name, 'I' into audit values(1,'Name') end Similarly for update create trigger myupdatetrigger on mytable as begin Update mytable set name = 'Red' output inserted.id, inserted.name,'U' into audit where id = 1 end For delete operation Create trigger mydeletetrigger on mytable as begin delete from mytable output deleted.id, deleted.name, 'D' into audit where id = 1 Note: Here inserted, deleted are called magical tables in sql server. They store the values of data in that context to help writing queries that can work on the changed values. For example, update operation means a combination of delete and insert and if you need to capture both then we can change our update trigger as below: if audit has four columns id_new, name_new, id_old, name_old create trigger myupdatetrigger on mytable as begin Update mytable set name = 'Red' output inserted.id, inserted.name, deleted.id, deleted.name into audit where id = 1 end These are just simple examples for understanding. In realtime, you may have to deal with complex code. Hope this is clear. Let me know if you have any questions.

Deepak Middha · Answer

Thanks mr. Akkiraju !please give an example of output trigger.

Satyapriya Nayak · Answer

Hi Deepak, Please refer the below link http://www.mssqltips.com/sqlservertip/1381/sql-server-trigger-alternatives-with-the-output-clause/ Thanks If this post helps you mark it as answer