SQL Server Code,Tips and Tricks, Performance Tuning

It has been a while since I wrote some of my best practices posts. I decided to revisit these posts again to see if anything has changed, I also wanted to see if I could add some additional info.

Today I decided to revisit the post about coding triggers for multi-row operations

There are many forum posts and questions on stackoverflow where people have trigger code. However these triggers are coded incorrectly because they don't account for multi-row operations.

The one thing you have to remember is that a trigger fires per batch not per row, if you are lucky you will get an error...if you are not lucky you will not get an error but it might take a while before you notice that you are missing a whole bunch of data

Let's take a look at exactly what happens, first create these two tables

CREATE TABLE Test(id int identity not null primary key, 
 SomeDate datetime not null)
GO
CREATE TABLE TestHistory(id int not null, 
 InsertedDate datetime not null)
GO

Now create the following trigger.

CREATE TRIGGER trTest
 ON Test
 FOR INSERT
 AS
 
 IF @@ROWCOUNT =0
 RETURN
 
 DECLARE @id int
 SET @id = (SELECT id 
 FROM inserted)
 
 INSERT TestHistory (id,InsertedDate)
 SELECT @id, getdate()
 
 GO

The trigger you just created is very simple, it basically inserts a row into the history table every time an insert happens in the test table


Run this insert statement which only inserts one row

INSERT Test(SomeDate) values(getdate())

Now run this to see what is in the history table

SELECT * FROM TestHistory

12017年10月14日 08:49:16.227


That all works fine, what happens when we try to insert 2 rows?

INSERT Test(SomeDate)
SELECT getdate()
UNION ALL
SELECT dateadd(dd,1,getdate() )

Here is the error.

Server: Msg 512, Level 16, State 1, Procedure trTest, Line 11
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.


As you can see the trigger blew up with an error. Let's try something else.
What would happen if you coded the trigger in this way

ALTER TRIGGER trTest
 ON Test
 FOR INSERT
 AS
 
 IF @@ROWCOUNT =0
 RETURN
 
 DECLARE @id int
 SELECT @id = id 
 FROM inserted
 
 INSERT TestHistory (id,InsertedDate)
 SELECT @id, getdate()
 
 GO

Now insert one row

INSERT Test(SomeDate) VALUES (getdate())

We look again what is in the history table, as you can see we have id 1 and 4, this is because id 2 and 3 failed and were rolled back when we did the insert earlier

SELECT * FROM TestHistory

12017年10月14日 08:49:16.227
42017年10月14日 08:50:37.647


Here is where it gets interesting, run this code

INSERT Test(SomeDate)
SELECT getdate()
UNION ALL
SELECT dateadd(dd,1,getdate() )

That runs fine but when we look now we are missing one of the rows in the history table

SELECT * FROM TestHistory

12017年10月14日 08:49:16.227
42017年10月14日 08:50:37.647
52017年10月14日 08:51:06.270


let's try that same insert statement again

INSERT Test(SomeDate)
SELECT getdate()
UNION ALL
SELECT dateadd(dd,1,getdate() )

Now we are again missing a row in the history table

SELECT * FROM TestHistory

12017年10月14日 08:49:16.227
42017年10月14日 08:50:37.647
52017年10月14日 08:51:06.270
72017年10月14日 08:52:09.447


The problem is with this line of code

SELECT @id = id FROM inserted

@id will only hold the value for one of the rows that was returned in the result set


Here is how you would change the trigger to work correctly

ALTER TRIGGER trTest
 ON Test
 FOR INSERT
 AS
 
 IF @@ROWCOUNT =0
 RETURN
 
 
 INSERT TestHistory (id,InsertedDate)
 SELECT id, getdate()
 FROM inserted
 
GO

Now run the single insert statement again

INSERT Test(SomeDate) VALUES (getdate())

That row was inserted, we can check the history table to see what is there now

SELECT * FROM TestHistory

12017年10月14日 08:49:16.227
42017年10月14日 08:50:37.647
52017年10月14日 08:51:06.270
72017年10月14日 08:52:09.447
92017年10月14日 08:52:57.990


Finally, we can again test with the insert statement that will insert 2 rows

INSERT Test(SomeDate)
SELECT getdate()
UNION ALL
SELECT dateadd(dd,1,getdate() )

Let's check the history table again

SELECT * FROM TestHistory

12017年10月14日 08:49:16.227
42017年10月14日 08:50:37.647
52017年10月14日 08:51:06.270
72017年10月14日 08:52:09.447
92017年10月14日 08:52:57.990
112017年10月14日 08:53:40.693
102017年10月14日 08:53:40.693

And as you can see both rows were inserted into the history table

So what is worse in this case? The error message or the fact that the code didn't blow up but that the insert wasn't working correctly? I'll take an error message any time over the other problem.

SQL Server 2005 has added an optional OUTPUT clause to UPDATE, INSERT and DELETE commands, this enables you to accomplish almost the same task as if you would have used a after trigger in SQL Server 2000
Let's take a closer look, let's start with the syntax


The syntax for an insert is like this
INSERT INTO TableNAme
OUTPUT Inserted
VALUES (....)


The syntax for an update is like this
UPDATE TableNAme
SET ......
OUTPUT Deleted,Inserted
WHERE ....

The syntax for a delete is like this
DELETE TableName
OUTPUT Deleted
WHERE ....

So the OUTPUT comes right before the WHERE or the VALUES part of the statement
Also as you can see Insert has Inserted as part of the OUTPUT Clause, Update has Inserted and Deleted and Delete has Deleted as part of the OUTPUT Clause, this is very simmilar to the inserted and deleted pseudo-tables in triggers

Let's test it out, first we have to create a table
CREATE TABLE TestOutput
(
ID INT NOT NULL,
Description VARCHAR(50) NOT NULL,
)

INSERT INTO TestOutput (ID, Description) VALUES (1, 'Desc1')
INSERT INTO TestOutput (ID, Description) VALUES (2, 'Desc2')
INSERT INTO TestOutput (ID, Description) VALUES (3, 'Desc3')


Let's show what we just inserted
INSERT INTO TestOutput (ID, Description)
OUTPUT Inserted.ID AS ID,Inserted.Description AS Description
VALUES (4, 'Desc4')


The * wildcard works also in this case
INSERT INTO TestOutput (ID, Description)
OUTPUT Inserted.*
VALUES (5, 'Desc5')


Let's try it with a delete statement
DELETE TestOutput
OUTPUT Deleted.*
WHERE ID = 4

Let's try to use Inserted here
DELETE TestOutput
OUTPUT Inserted.*
WHERE ID = 4

The message that is returned is this
Server: Msg 107, Level 15, State 1, Line 1
The column prefix 'Inserted' does not match with a table name or alias name used in the query.

So as you can see Inserted can not be used with a DELETE command

Let's try using deleted with an insert command
INSERT INTO TestOutput (ID, Description)
OUTPUT Deleted.*
VALUES (5, 'Desc5')

Same story, Deleted can not be used with an INSERT command
Server: Msg 107, Level 15, State 1, Line 1
The column prefix 'Deleted' does not match with a table name or alias name used in the query.


Let's take a look at the update statement
UPDATE TestOutput
SET ID = 4
OUTPUT DELETED.ID AS OldId, DELETED.Description AS oldDescription ,
Inserted.ID AS New_ID , Inserted.Description AS Newdescription
WHERE ID = 5

Create a table where we will insert our output results into
CREATE TABLE #hist (OldID INT,OldDesc VARCHAR(50),
New_ID INT,NewDesc VARCHAR(50),UpdatedTime DATETIME)

Let's insert the old and new values into a temp table (audit trail..kind of)
UPDATE TestOutput
SET ID = 666
OUTPUT DELETED.ID AS OldId, DELETED.Description AS oldDescription ,
Inserted.ID AS New_ID , Inserted.Description AS Newdescription,GETDATE() INTO #hist
WHERE ID = 3

let's see what we inserted
SELECT * FROM #hist


Remember this message?
Server: Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= ,>,>= or when the subquery is used as an expression.

You would get this in a trigger when a trigger would fire after a multi row statement and you tried to assign a value to a variable. Since you can't assign values to variable with the OUTPUT clause you won't have this problem


How does identity work with the output clause?
CREATE TABLE TestOutput2
(
ID INT NOT NULL IDENTITY,
Description VARCHAR(50) NOT NULL,
)

INSERT INTO TestOutput2 (Description)
OUTPUT Inserted.*
SELECT 'Desc1' UNION ALL
SELECT 'Desc2'

We can not use SCOPE_IDENTITY() or @@IDENTITY, the value for those will both be 2, run the following example to see this
INSERT INTO TestOutput2 (Description)
OUTPUT SCOPE_IDENTITY(),@@IDENTITY,Inserted.*
SELECT 'Desc3' UNION ALL
SELECT 'Desc4'


The bottom line.
The OUTPUT clause is very useful but I don't think it's a replacement for triggers. A trigger can protect your table for any user (unless you execute a alter table..disable trigger statement)
If you use OUTPUT exclusively, then if someone uses a tool like Enterprise Manager to insert a row into your table there is nothing to fire if you want to create an audit trail for example
So it has it's uses but won't replace the trigger