Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Welcome to my blog. I work as Database lead at Synaptic Digital. Hope you find some interesting stuff here.
Browse by Tags · View All
BRH 17
SQL Server 15
#SQL Server 11
#BI 10
#TSQL 8
TSQL 8
BI 7
SSRS 6
#SQLServer 6
SSRS 2008R2 5

Archive · View All
January 2011 6
December 2010 5
September 2012 4
May 2012 4
March 2011 4
November 2012 2
October 2012 2
January 2012 2
February 2011 2
November 2010 2

OUTPUT clause and triggers

Jan 2 2012 3:56PM by Chintak Chhapia   

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.

Tags: SQL 2008 R2, #SQL Server, SQL Server, sql 2008, output clause, sql 2005, triggers,


Chintak Chhapia
40 · 5% · 1470
5
 
0
Lifesaver
 
 
0
Learned
 
0
Incorrect



Submit

2  Comments  

  • This is nice. I have never tried to use the OUTPUT clause in this way because most of the times, I use it to insert data into an audit table within a trigger. Great learning. Thanks, Chintak!

    commented on Jan 2 2012 10:09AM
    Nakul Vachhrajani
    4 · 36% · 11533
  • Nice post, Chintak.

    Currently in my assignment, I need to capture how many records have been affected by the operation (Update or delete), and display the number to user. I am using the same pattern you have discribed in your Blog - OUTPUT with INTO to table variable then count(*) for number of records affected by the operation.

    I don't know about target table must not have enabled trigger when you are using it in OUTPUT clause. Thanks for sharing.

    commented on Jan 6 2012 1:31AM
    Hardik Doshi
    20 · 9% · 2845

Your Comment


Sign Up or Login to post a comment.

"OUTPUT clause and triggers" rated 5 out of 5 by 5 readers
OUTPUT clause and triggers , 5.0 out of 5 based on 5 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]