Showing posts with label Trigger. Show all posts
Showing posts with label Trigger. Show all posts

Saturday, October 14, 2017

Coding SQL Server triggers for multi-row operations

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


1 2017-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

1 2017-10-14 08:49:16.227
4 2017-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

1 2017-10-14 08:49:16.227
4 2017-10-14 08:50:37.647
5 2017-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

1 2017-10-14 08:49:16.227
4 2017-10-14 08:50:37.647
5 2017-10-14 08:51:06.270
7 2017-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

1 2017-10-14 08:49:16.227
4 2017-10-14 08:50:37.647
5 2017-10-14 08:51:06.270
7 2017-10-14 08:52:09.447
9 2017-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

1 2017-10-14 08:49:16.227
4 2017-10-14 08:50:37.647
5 2017-10-14 08:51:06.270
7 2017-10-14 08:52:09.447
9 2017-10-14 08:52:57.990
11 2017-10-14 08:53:40.693
10 2017-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.

Monday, October 16, 2006

Hello OUTPUT See You Later Trigger Or Perhaps Not?

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