Trigger Instead of Insert/Update

Question

Trigger Instead of Insert/Update

rootfixxxer

SSCrazy

Points: 2918
More actions
January 29, 2014 at 7:51 am

#281656

Hi
I created a trigger to handle one business requirement in my company, the trigger checks if the insert or update statement adds/changes one field of the table to a predefined value, if the operation does that, i need to insert two rows to replace the original one, with different values for the field...
After getting the trigger "working", i noticed that the trigger doesn't work correctly when using the Insert Into .. Select with more than one row.
For example, if i use only Insert Into .. Values or Insert Into .. Select with only one row the trigger works fine. But when I'm trying to insert multiple rows, it has a odd behavior, if the last row in the list to insert has that predefined value, the trigger does the duplication job for every row... If the last row doesn't have the predefined value, even if the others rows have the predefined value, it doesn't do the duplication, it only inserts the rows...
It looks like it only "looks" to the values of the last row in the list. This is the normal behavior?!
How can i solve this situation?
Some test code:
USE AdventureWorks2008
GO
-- Drop Teste Table
DROP TABLE TesteTrigger
GO
-- Create Teste Table
CREATE TABLE TesteTrigger(
[ID] BIGINT IDENTITY(1,1) PRIMARY KEY,
Name NVARCHAR(30),
CheckValue TINYINT
)
GO
-- Insert some data
INSERT INTO dbo.TesteTrigger
( Name, CheckValue )
SELECT 'Original 10',1
UNION ALL
SELECT 'Original 20',2
UNION ALL
SELECT 'Original 30',3
UNION ALL
SELECT 'Original 40',4
UNION ALL
SELECT 'Original 50',5
UNION ALL
SELECT 'Original 11',1
GO
-- Creates the trigger
CREATE TRIGGER trgTesteTrigger ON dbo.TesteTrigger INSTEAD OF UPDATE,INSERT
AS
BEGIN
DECLARE @Operacao AS CHAR(1)
DECLARE @ID BIGINT
DECLARE @NAME NVARCHAR(10)
DECLARE @PREDF TINYINT
-- Identifies the operation type
SET @Operacao = 'N'
IF EXISTS(SELECT * FROM INSERTED) AND EXISTS(SELECT * FROM DELETED)
SET @Operacao = 'U'
IF EXISTS(SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM DELETED)
SET @Operacao = 'I'
IF NOT EXISTS(SELECT * FROM INSERTED) AND EXISTS(SELECT * FROM DELETED)
SET @Operacao = 'D'
IF NOT EXISTS(SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM DELETED)
SET @Operacao = 'X'
-- Get Vars
SELECT
@PREDF = CheckValue ,
@ID = ID,
@NAME = Name
FROM INSERTED
-- Update
IF @Operacao = 'U'
BEGIN
IF @PREDF = 2
BEGIN
-- Updates the original
UPDATE dbo.TesteTrigger
SET CheckValue = 80,
Name = I.Name
FROM dbo.TesteTrigger A INNER JOIN INSERTED I
ON A.ID = I.ID
-- Inserts a new row
INSERT INTO dbo.TesteTrigger
( Name, CheckValue )
SELECT Name ,81 FROM INSERTED
END
ELSE
UPDATE dbo.TesteTrigger
SET CheckValue = I.CheckValue,
Name = I.Name
FROM dbo.TesteTrigger A INNER JOIN INSERTED I
ON A.ID = I.ID
END
--- Insert
ELSE IF @Operacao = 'I'
BEGIN
IF @PREDF = 2
BEGIN
-- Insert one
INSERT INTO dbo.TesteTrigger
( Name, CheckValue )
SELECT Name ,90 FROM INSERTED
-- Insert two
INSERT INTO dbo.TesteTrigger
( Name, CheckValue )
SELECT Name ,91 FROM INSERTED
END
ELSE
INSERT INTO dbo.TesteTrigger
( Name, CheckValue )
SELECT Name ,CheckValue FROM INSERTED
END
END
--- Update Working 1 row
UPDATE dbo.TesteTrigger
SET Name = 'Updated Teste 5', CheckValue=2
WHERE CheckValue = 5
--- Update Working X rows
UPDATE dbo.TesteTrigger
SET Name = 'Updated Teste 1', CheckValue=2
WHERE CheckValue = 1
--- Insert Working 1 row VALUES
INSERT INTO dbo.TesteTrigger VALUES('InsertVal',2)
--- Insert Working 1 row SELECT
INSERT INTO dbo.TesteTrigger ( Name, CheckValue )
SELECT 'InsertSelect1',2
--- Insert NOT Working x rows Situation 1 (Duplicates everything)
INSERT INTO dbo.TesteTrigger
( Name, CheckValue )
SELECT 'InsertSelect20',20
UNION ALL
SELECT 'InsertSelect21',21
UNION ALL
SELECT 'InsertSelect22',22
UNION ALL
SELECT 'InsertSelect23',2
--- Insert NOT Working x rows Situation 1 (Doesnt duplicate anything)
INSERT INTO dbo.TesteTrigger
( Name, CheckValue )
SELECT 'InsertSelect30',2
UNION ALL
SELECT 'InsertSelect31',2
UNION ALL
SELECT 'InsertSelect32',2
UNION ALL
SELECT 'InsertSelect33',30
SELECT * FROM dbo.TesteTrigger

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

rootfixxxer (1/29/2014)
Hi
I created a trigger to handle one business requirement in my company, the trigger checks if the insert or update statement adds/changes one field of the table to a predefined value, if the operation does that, i need to insert two rows to replace the original one, with different values for the field...
After getting the trigger "working", i noticed that the trigger doesn't work correctly when using the Insert Into .. Select with more than one row.
For example, if i use only Insert Into .. Values or Insert Into .. Select with only one row the trigger works fine. But when I'm trying to insert multiple rows, it has a odd behavior, if the last row in the list to insert has that predefined value, the trigger does the duplication job for every row... If the last row doesn't have the predefined value, even if the others rows have the predefined value, it doesn't do the duplication, it only inserts the rows...
It looks like it only "looks" to the values of the last row in the list. This is the normal behavior?!
How can i solve this situation?
Some test code:
USE AdventureWorks2008
GO
-- Drop Teste Table
DROP TABLE TesteTrigger
GO
-- Create Teste Table
CREATE TABLE TesteTrigger(
[ID] BIGINT IDENTITY(1,1) PRIMARY KEY,
Name NVARCHAR(30),
CheckValue TINYINT
)
GO
-- Insert some data
INSERT INTO dbo.TesteTrigger
( Name, CheckValue )
SELECT 'Original 10',1
UNION ALL
SELECT 'Original 20',2
UNION ALL
SELECT 'Original 30',3
UNION ALL
SELECT 'Original 40',4
UNION ALL
SELECT 'Original 50',5
UNION ALL
SELECT 'Original 11',1
GO
-- Creates the trigger
CREATE TRIGGER trgTesteTrigger ON dbo.TesteTrigger INSTEAD OF UPDATE,INSERT
AS
BEGIN
DECLARE @Operacao AS CHAR(1)
DECLARE @ID BIGINT
DECLARE @NAME NVARCHAR(10)
DECLARE @PREDF TINYINT
-- Identifies the operation type
SET @Operacao = 'N'
IF EXISTS(SELECT * FROM INSERTED) AND EXISTS(SELECT * FROM DELETED)
SET @Operacao = 'U'
IF EXISTS(SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM DELETED)
SET @Operacao = 'I'
IF NOT EXISTS(SELECT * FROM INSERTED) AND EXISTS(SELECT * FROM DELETED)
SET @Operacao = 'D'
IF NOT EXISTS(SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM DELETED)
SET @Operacao = 'X'
-- Get Vars
SELECT
@PREDF = CheckValue ,
@ID = ID,
@NAME = Name
FROM INSERTED
-- Update
IF @Operacao = 'U'
BEGIN
IF @PREDF = 2
BEGIN
-- Updates the original
UPDATE dbo.TesteTrigger
SET CheckValue = 80,
Name = I.Name
FROM dbo.TesteTrigger A INNER JOIN INSERTED I
ON A.ID = I.ID
-- Inserts a new row
INSERT INTO dbo.TesteTrigger
( Name, CheckValue )
SELECT Name ,81 FROM INSERTED
END
ELSE
UPDATE dbo.TesteTrigger
SET CheckValue = I.CheckValue,
Name = I.Name
FROM dbo.TesteTrigger A INNER JOIN INSERTED I
ON A.ID = I.ID
END
--- Insert
ELSE IF @Operacao = 'I'
BEGIN
IF @PREDF = 2
BEGIN
-- Insert one
INSERT INTO dbo.TesteTrigger
( Name, CheckValue )
SELECT Name ,90 FROM INSERTED
-- Insert two
INSERT INTO dbo.TesteTrigger
( Name, CheckValue )
SELECT Name ,91 FROM INSERTED
END
ELSE
INSERT INTO dbo.TesteTrigger
( Name, CheckValue )
SELECT Name ,CheckValue FROM INSERTED
END
END
--- Update Working 1 row
UPDATE dbo.TesteTrigger
SET Name = 'Updated Teste 5', CheckValue=2
WHERE CheckValue = 5
--- Update Working X rows
UPDATE dbo.TesteTrigger
SET Name = 'Updated Teste 1', CheckValue=2
WHERE CheckValue = 1
--- Insert Working 1 row VALUES
INSERT INTO dbo.TesteTrigger VALUES('InsertVal',2)
--- Insert Working 1 row SELECT
INSERT INTO dbo.TesteTrigger ( Name, CheckValue )
SELECT 'InsertSelect1',2
--- Insert NOT Working x rows Situation 1 (Duplicates everything)
INSERT INTO dbo.TesteTrigger
( Name, CheckValue )
SELECT 'InsertSelect20',20
UNION ALL
SELECT 'InsertSelect21',21
UNION ALL
SELECT 'InsertSelect22',22
UNION ALL
SELECT 'InsertSelect23',2
--- Insert NOT Working x rows Situation 1 (Doesnt duplicate anything)
INSERT INTO dbo.TesteTrigger
( Name, CheckValue )
SELECT 'InsertSelect30',2
UNION ALL
SELECT 'InsertSelect31',2
UNION ALL
SELECT 'InsertSelect32',2
UNION ALL
SELECT 'InsertSelect33',30
SELECT * FROM dbo.TesteTrigger

What you describe is exactly how triggers work in sql server. They do NOT fire for each row, they fire once with the entire dataset.

BE CAREFUL HERE!!! Your trigger is an instead of trigger that will do inserts to the same table that caused the trigger to fire in the first place. INSTEAD OF triggers can be nested regardless of the nested triggers setting. Your trigger really should not do something to the same table that caused it to execute in the first place.

http://technet.microsoft.com/en-us/library/ms178101.aspx

The problem you have here is where you set and try to use variables based on row values.

SELECT
@PREDF = CheckValue ,
@ID = ID,
@NAME = Name
FROM INSERTED

When you are inserting 2 rows you don't know which values will be found here.

You can greatly simplify this logic and make it set based.

CREATE TRIGGER trgTesteTrigger ON dbo.TesteTrigger INSTEAD OF UPDATE,INSERT

AS

IF EXISTS(SELECT * FROM INSERTED) AND EXISTS(SELECT * FROM DELETED) BEGIN

-- Updates the original

UPDATE dbo.TesteTrigger

SET CheckValue = case when i.CheckValue = 2 then 80 else i.CheckValue end,

Name = I.Name

FROM dbo.TesteTrigger A INNER JOIN INSERTED I

ON A.ID = I.ID

-- Inserts a new row when CheckValue = 2

INSERT INTO dbo.TesteTrigger

( Name, CheckValue )

SELECT Name ,81 FROM INSERTED

where Inserted.CheckValue = 2

END

IF EXISTS(SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM DELETED)

-- Insert one

INSERT INTO dbo.TesteTrigger

( Name, CheckValue )

SELECT Name, case when i.CheckValue = 2 then 90 else i.CheckValue end FROM INSERTED

-- Insert two

INSERT INTO dbo.TesteTrigger

( Name, CheckValue )

SELECT Name ,91 FROM INSERTED

where Inserted.CheckValue = 2

END

That should do the same thing but will handle multiple row operations.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

rootfixxxer SSCrazy Points: 2918 More actions · Answer 2

The extra "lines", vars and everything was for testing purposes, trial and error... And looks like these lines were the responsible for the problems... 🙁

But anyway, thanks for the info and the solution.

Now it works fine.

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

rootfixxxer (1/29/2014)
The extra "lines", vars and everything was for testing purposes, trial and error... And looks like these lines were the responsible for the problems... 🙁
But anyway, thanks for the info and the solution.
Now it works fine.

Anytime you see variables being assigned values that come from either inserted or deleted it should immediately throw a red flag. It is a clear indication that the trigger is not set based.

I am glad you were able to get it working.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

rootfixxxer SSCrazy Points: 2918 More actions · Answer 4

Always learning...

And it's working with your help!

Like i wrote i used the vars to test if everything was working, and it isn't, so probably i would added more vars to test... 😀

ScottPletcher SSC Guru Points: 101085 More actions · Answer 5

Be sure to add a:

SET NOCOUNT ON

to the start of the trigger:

CREATE TRIGGER ... ON ... AFTER|INSTEAD OF ...

AS

SET NOCOUNT ON

...

And for a very minor efficiency gain you should add an ELSE between the two IFs, since only one of them will ever be true for a given trigger invocation.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

rootfixxxer SSCrazy Points: 2918 More actions · Answer 6

rootfixxxer

SSCrazy

Points: 2918

January 29, 2014 at 1:44 pm

#1684481

Thanks for the tips.