Output Trigger

Deepak Middha

14y1.5kVisual Basic .NET

What is output trigger?also give an example.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Akkiraju Ivaturi14y ago
Accepted 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 Ivaturi14y ago
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 Middha14y ago
Thanks mr. Akkiraju !please give an example of output trigger.
Satyapriya Nayak14y ago
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

Output Trigger

Triggers