December 17, 2002 at 8:20 am
I have a trigger that simply copies the value in one field to another as such:
Create TRIGGER tMaintainEmail ON [dbo].[pmx_shopper]
FOR INSERT, UPDATE
AS
Declare @Count int
Select @Count = count(*) from Inserted
IF @Count > 0
Begin
UPDATE s
SET s.email = s.user_id
From pmx_shopper s
Join Inserted i
on i.user_id = s.user_id
Where i.user_id <> '<a string>'
End
This code Works fine. But I need to be more spicific whith what rows I actually touch.
So I added more to the where clause below.
Create TRIGGER tMaintainEmail ON [dbo].[pmx_shopper]
FOR INSERT, UPDATE
AS
Declare @Count int
Select @Count = count(*) from Inserted
IF @Count > 0
Begin
UPDATE s
SET s.email = s.user_id
From pmx_shopper s
Join Inserted i
on i.user_id = s.user_id
Where (i.user_id <> '<a string>'
AND i.mail_ln2 <> '<a string>'
AND i.email <> '<a string>')
End
This last code does not work. All i did was further qualify my sql but it somehow brakes the trigger. I have tried various ways of qualifying for example swapped out != for <> etc.. But no luck. Any Ideas??
Thanks
Dennis
dennisl
December 17, 2002 at 9:49 am
When you say breaks it, what do you mean? Is it that you cannot compile the trigger, the trigger now errors, or it works but doesn't do the update correctly?
Syntactically, it's correct. I wouldn't expect the first two. If it's that it doesn't properly update, I would look at the data involved and see what about it doesn't match the where clause to correct it. Also, check for nulls in the data, as null should not be evaluated by comparison operators (=, <>, etc.) but by Is Null or Is Not Null.
December 17, 2002 at 10:26 am
I agree with Scorpion_66, your code appears to be syntactically correct.
However, in terms of performance, why are you getting the count of rows from the inserted table before performing the UPDATE?
1. The trigger is only going to fire when an update or insert is performed, both of these are going to populate the inserted table.
2. The UPDATE contains an INNER JOIN which is only going to carry out actions on records with exist in BOTH tables forming part of the join.
Clearly you will improve the performance of your trigger by removing the @Count elements.
CREATE TRIGGER tMaintainEmail on dbo.pmx_shopper
FOR INSERT, UPDATE
AS
BEGIN
UPDATE s
SET s.email = s.user_id
FROM pmx_shopper s INNER JOIN inserted i
ON i.user_id = s.user_id
WHERE (i.user_id <> '<a string>' and
i.mail_ln2 <> '<a string>' and
i.email <> '<a string>')
END
December 17, 2002 at 10:45 am
Dennis,
It is a good idea to check for row count before proceeeding with the trigger but the way to do it is not count(*). Do this at the start of ANY trigger:
IF @@ROWCOUNT = 0 RETURN
Other than that, your code is "okay". Something else must be happening that we do not see here. You could replace multiple <> statements with one NOT IN, just make sure there is never NULL in the NOT IN list.
Also you could try to move the WHERE clause to the ON clause like this:
UPDATE s
SET s.email = s.user_id
From pmx_shopper s
Join Inserted i
on i.user_id = s.user_id
AND i.user_id NOT IN ( '<a string>' , '<a string>', '<a string>' )
Michael
December 19, 2002 at 6:12 am
quote:
UPDATE sSET s.email = s.user_id
From pmx_shopper s
Join Inserted i
on i.user_id = s.user_id
AND i.user_id NOT IN ( '<a string>' , '<a string>', '<a string>' )
I'd have gone for
and '<a string>' not in (i.user_id, i.mail_ln2, i.email)
I reckon that'll sort you out, if '<a string>' is the same string in all three cases
other wise, change the ands to ors and give that a shot.
Edited by - spowell_2 on 12/19/2002 06:14:22 AM
December 19, 2002 at 9:09 am
This is probably not exactly what you wanted, but why not make the email a computed column? It looks like that is what you are trying to do except with some conditions. As for testing if there are rows in the inserted, there will always be as you have defined your trigger as UPDATE, INSERT, the only table that has a chance of being empty is the DELETED table when an insert occurs, so there is no need to check.
Tim C.
//Will write code for food
Tim C //Will code for food
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply