Output clause has been added to SQL 2005 which can mainly used to grab the identity values. This has been really helpful addition to SQL 2005. You can get more information regarding output clause here.
I have faced couple of incidents in last six months involving use of output clause and trigger which are not very well-known, at least I was not aware of those things.
Case-1 If table on which we are performing DML operation has trigger for that action, it’s mandatory to use OUTPUT INTO for that DML operation.
For example,
use tempdb
if OBJECT_ID('dbo.table1') is not null
drop table dbo.table1
go
CREATE TABLE dbo.table1
(
id INT,
employee VARCHAR(32)
)
go
INSERT INTO dbo.table1 VALUES
(1, 'Fred')
,(2, 'Tom')
,(3, 'Sally')
,(4, 'Alice');
GO
SELECT * FROM dbo.table1;
DELETE FROM dbo.table1
OUTPUT DELETED.*
WHERE id = 4
--So everything works fine here
--But now, if we create a trigger for DELETE
go
CREATE TRIGGER tr_table1_delete on table1 after delete as select 1
GO
DELETE FROM dbo.table1
OUTPUT DELETED.*
WHERE id = 4
/*
Here we ger below error:
Msg 334, Level 16, State 1, Line 1
The target table 'dbo.table1' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.
*/
--Update statement will work as trigger is only for delete
update dbo.table1 set employee = 'Chintak' where id = 1
There is defiantly a simple workaround it to declare table variable and use into statement as demonstrated below. But, this can really cause an issue if we create the trigger in Production environment to keep track of something and we have code that uses output clause.
--Simple Workaround
DECLARE @MyTableVar TABLE
(
id INT,
employee VARCHAR(32)
);
DELETE FROM dbo.table1
OUTPUT DELETED.* into @MyTableVar
WHERE id = 4
Case-2 If target table of OUTPUT clause (in which we are inserting rows) should not have any triggers.
I was involved in some data transfer task form one system to another system using SSIS, but at some places where data volume is not much, we are tempted to use queries sometimes in place of proven SSIS. Output clause can be used vary easily to load data into some parent-child tables.
For Example (this is not an real life example, but I have tried to put in some simple example),
use tempdb
go
if OBJECT_ID('dbo.parentTablePeople') is not null
Drop table dbo.parentTablePeople
go
Create table dbo.parentTablePeople
(
id int identity(1,1),
firstName nvarchar(100) ,
dummyColumn date
)
go
if OBJECT_ID('dbo.childTablePeopleDetails') is not null
Drop table dbo.childTablePeopleDetails
go
Create table dbo.childTablePeopleDetails
(
id int identity(1,1),
birthdate date
)
go
if OBJECT_ID('dbo.stageTable') is not null
Drop table dbo.stageTable
go
Create table dbo.stageTable
(
firstName nvarchar(100),
birthDate date
)
go
insert into stageTable (firstName,birthDate)
select 'Tom','19901222'
union all
select 'Alice','19921201'
go
Create trigger trg_childTablePeopleDetails_insert on childTablePeopleDetails after insert as set nocount on; select 1
go
insert into parentTablePeople (firstName,dummyColumn)
output inserted.id,inserted.dummyColumn into childTablePeopleDetails(id,birthdate)
select firstName,birthDate from dbo.stageTable
/*
Here we get an error:
Msg 331, Level 16, State 1, Line 2
The target table 'childTablePeopleDetails' of the OUTPUT INTO clause cannot have any enabled triggers.
*/
So, then I tried the really great trick mentioned in this post
insert into childTablePeopleDetails(id, birthdate)
select id,dummyColumn
from
(insert into parentTablePeople (firstName,dummyColumn)
output inserted.id,inserted.dummyColumn
select firstName,birthDate from dbo.stageTable
) as a
/*
Here we get an error:
Msg 355, Level 16, State 1, Line 2
The target table 'childTablePeopleDetails' of the INSERT statement cannot have any enabled triggers when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement.
*/
Now only way left is to use the temporary table variable and INTO clause.
So, all in all best practice for Output clause is to always use table variables and INTO clause.