November 23, 2015 at 2:32 pm
DECLARE @DML CHAR(6) =
CASE WHEN
EXISTS(SELECT 1 FROM inserted)
AND EXISTS(SELECT 1 FROM deleted)
THEN 'Update'
WHEN
EXISTS(SELECT 1 FROM inserted)
THEN 'Insert'
WHEN
EXISTS(SELECT 1 FROM deleted)
THEN 'Delete'
ELSE NULL
END
IF @DML = 'Delete'
BEGIN
DELETE ...
FROM ...
JOIN deleted d ...
RETURN
END
IF @DML = 'Insert'
BEGIN
INSERT ...
SELECT ...
FROM inserted
RETURN
END
IF @DML = 'Update'
BEGIN
UPDATE ...
FROM inserted i
JOIN ...
RETURN
END
November 23, 2015 at 2:48 pm
The biggest issue with your trigger is that it is relying on scalar values. This is bug red flag because in sql server, triggers fire once per operation and scalar variable indicate the code expects there to be only a single value. Your code seems to be suffering from this quite a bit. Triggers MUST be set based to function correctly.
We can help you here we need some more details. First and foremost, what is this trigger attempting to do? Any additional information like schema, sample data will go a long way towards getting assistance.
_______________________________________________________________
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/
November 23, 2015 at 2:50 pm
Looking a bit closer you could save yourself a LOT of headaches by breaking this into three triggers just to start. One each for INSERT, DELETE, UPDATE. At least that way you don't all your code piled on top of each other.
_______________________________________________________________
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/
Viewing 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply