August 13, 2014 at 9:48 am
Hi all -
I'm working on a trigger and I'm having some problems. Basically what I want this trigger to do is: if Users.Primary_ProductID is updated, I want to insert a new row into the Analysts table (writing Analysts.Product_ID and Analysts.User_ID from Users.Primary_ProductID and Users.ID).
However, I only want this row to be inserted if this combination of Analysts.Users_ID and Analysts.Product_ID does not exist already. The way the trigger is now, it seems to be ignoring the "where not exists" section, and it's creating new rows every time I update Users.Primary_ProductID. Here is the current trigger:
ALTER trigger [dbo].[UpdateAnalystTable]
on [dbo].[Users]
after update
as begin
if UPDATE (Primary_ProductID)
insert into dbo.Analysts (Product_ID, User_ID)
SELECT Primary_ProductID, ID from Users
where not exists
(select * from Analysts where Users.Primary_ProductID = Analysts.Product_ID and Users.ID = Analysts.User_ID)
and
Users.SecurityGroup_ID = 4
end
Does anyone have any idea what I'm doing wrong here, and why it's ignoring the "where not exists" section? Thanks!
James
August 13, 2014 at 9:59 am
james.williams 57297 (8/13/2014)
Hi all -I'm working on a trigger and I'm having some problems. Basically what I want this trigger to do is: if Users.Primary_ProductID is updated, I want to insert a new row into the Analysts table (writing Analysts.Product_ID and Analysts.User_ID from Users.Primary_ProductID and Users.ID).
However, I only want this row to be inserted if this combination of Analysts.Users_ID and Analysts.Product_ID does not exist already. The way the trigger is now, it seems to be ignoring the "where not exists" section, and it's creating new rows every time I update Users.Primary_ProductID. Here is the current trigger:
ALTER trigger [dbo].[UpdateAnalystTable]
on [dbo].[Users]
after update
as begin
if UPDATE (Primary_ProductID)
insert into dbo.Analysts (Product_ID, User_ID)
SELECT Primary_ProductID, ID from Users
where not exists
(select * from Analysts where Users.Primary_ProductID = Analysts.Product_ID and Users.ID = Analysts.User_ID)
and Users.SecurityGroup_ID = 4
end
Does anyone have any idea what I'm doing wrong here, and why it's ignoring the "where not exists" section? Thanks!
James
Read Books Online about triggers and the INSERTED and DELETED tables. They contain the actual data that was updated by the batch that fired the trigger, and you need to reference them in your code to get at the modified row(s)' values.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 13, 2014 at 10:00 am
Not completely sure what you are trying to do here but shouldn't you be referencing INSERTED somewhere in there?
_______________________________________________________________
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/
August 13, 2014 at 10:16 am
Kevin and Sean both hit on one part of your problem, you are never referencing the virtual inserted or deleted table so you are getting every row in Users where SecurityGroup_ID = 4 and the User/Product combination doesn't exist every time the trigger fires. The second part of the problem is that you are expecting UPDATE(Primary_ProductID) to only return true when Primary_ProductID is changed and this is not how the UPDATE function works. The UPDATE function returns true whenever the column referenced is included in the SET clause of the UPDATE statement so doing this:
UPDATE dbo.Users
SET Primary_ProductID = Primary_ProductID
Causes the IF UPDATE(Primary_ProductID) to return true. I believe this is the code you need:
ALTER TRIGGER [dbo].[UpdateAnalystTable] ON [dbo].[Users]
AFTER UPDATE
AS
BEGIN
IF UPDATE(Primary_ProductID)
INSERT INTO dbo.Analysts
(
Product_ID,
User_ID
)
SELECT
I.Primary_ProductID,
I.ID
FROM
Inserted AS I JOIN
Deleted AS D ON
I.PrimaryKey = D.PrimaryKey AND
/* This actually enforces that the Primary_ProductID was changed! IF UPDATE(Primary_ProductID)
only verifies that Primary_ProductID was included in the UPDATE statement */
I.Primary_ProductID != D.Primary_ProductID
WHERE
NOT EXISTS ( SELECT
*
FROM
Analysts
WHERE
I.Primary_ProductID = Analysts.Product_ID AND
I.ID = Analysts.User_ID ) AND
I.SecurityGroup_ID = 4
END
You could code the JOIN to Deleted as an Exists in the Where clause as well.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 13, 2014 at 10:29 am
Jack Corbett (8/13/2014)
Kevin and Sean both hit on one part of your problem, you are never referencing the virtual inserted or deleted table so you are getting every row in Users where SecurityGroup_ID = 4 and the User/Product combination doesn't exist every time the trigger fires. The second part of the problem is that you are expecting UPDATE(Primary_ProductID) to only return true when Primary_ProductID is changed and this is not how the UPDATE function works. The UPDATE function returns true whenever the column referenced is included in the SET clause of the UPDATE statement so doing this:
UPDATE dbo.UsersSET Primary_ProductID = Primary_ProductID
Causes the IF UPDATE(Primary_ProductID) to return true. I believe this is the code you need:
ALTER TRIGGER [dbo].[UpdateAnalystTable] ON [dbo].[Users]AFTER UPDATE
AS
BEGIN
IF UPDATE(Primary_ProductID)
INSERT INTO dbo.Analysts
(
Product_ID,
User_ID
)
SELECT
I.Primary_ProductID,
I.ID
FROM
Inserted AS I JOIN
Deleted AS D ON
I.PrimaryKey = D.PrimaryKey AND
/* This actually enforces that the Primary_ProductID was changed! IF UPDATE(Primary_ProductID)
only verifies that Primary_ProductID was included in the UPDATE statement */
I.Primary_ProductID != D.Primary_ProductID
WHERE
NOT EXISTS ( SELECT
*
FROM
Analysts
WHERE
I.Primary_ProductID = Analysts.Product_ID AND
I.ID = Analysts.User_ID ) AND
I.SecurityGroup_ID = 4
END
You could code the JOIN to Deleted as an Exists in the Where clause as well.
That is working! Thank you Jack! I've been working on this all day and you just saved my butt! 🙂 Cheers!
August 13, 2014 at 11:23 am
Glad I could help. That UPDATE() function is a pain because it doesn't do what you think it should.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply