Trigger Question

Question

Trigger Question

Paul_Harvey

Hall of Fame

Points: 3822
More actions
March 24, 2011 at 4:31 am

#235581

Hi all,
I have an SSIS package that feeds a Contact table in my database. This contains normal forename, surname columns, and also email address and a "email_bounced" boolean field.
A separate process sets the email_bounced flag when there is a problem with the email address.
What I need is a trigger (or alternative?) which will clear the email_bounced flag when the email address changes. I have made some attampts via the SSIS package itself, but am having problems.
Could someone please point me in the right direction? Can you fire triggers at field level, or only row level?
Thanks,
Paul

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

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

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 1

You should be able to use an update trigger to do what you want. You'd use the IF UPDATE(<column_name>) syntax to only set the flag if the email column was being updated.

See here.

Paul_Harvey Hall of Fame Points: 3822 More actions · Answer 2

Great. Thanks for the pointer. I'll try to get the trigger working now.

Wish me luck!

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 3

Paul_Harvey (3/24/2011)
Great. Thanks for the pointer. I'll try to get the trigger working now.
Wish me luck!

Luck's got nothing to do with it. Armed with BOL and these forums, you'll never need to rely on luck again!

Cheers,

Duncan

Paul_Harvey Hall of Fame Points: 3822 More actions · Answer 4

I have made some attempts. The following does <more or less> what I need:

ALTER TRIGGER [dbo].[trClearBounced]

ON [dbo].[Contact]

AFTER UPDATE

AS

IF ( UPDATE (Email) )

BEGIN

Update contact

SET bounced = 0

FROM contact INNER JOIN Inserted ON contact.id= Inserted.id

However this clears my flag even if the update contains the original value. This is expected behaviour of course, as the Update has still occurred. I have tried to add a line:

where contact.email <> inserted.email

However this stops the trigger from working at all. This must be becuase the trigger is fired AFTER UPDATE, and so by this time inserted.email and contact.email are the same.

Frustrating!

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 5

Paul_Harvey (3/24/2011)
I have made some attempts. The following does <more or less> what I need:
ALTER TRIGGER [dbo].[trClearBounced]
ON [dbo].[Contact]
AFTER UPDATE
AS
IF ( UPDATE (Email) )
BEGIN
Update contact
SET bounced = 0
FROM contact INNER JOIN Inserted ON contact.id= Inserted.id
However this clears my flag even if the update contains the original value. This is expected behaviour of course, as the Update has still occurred. I have tried to add a line:
where contact.email <> inserted.email
However this stops the trigger from working at all. This must be becuase the trigger is fired AFTER UPDATE, and so by this time inserted.email and contact.email are the same.
Frustrating!

You're absolutely correct in your assumption.

What you need to do is use the DELETED table (which is like the INSERTED table, but contains the pre-updated values) to find out what the value was before the change and then not change the flag if it is the same. So try joining to the DELETED table instead of the INSERTED one and see how you get on.

Paul_Harvey Hall of Fame Points: 3822 More actions · Answer 6

Oh that's brillant! And it works perfectly.

Thanks very much for your help on this.

🙂

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 7

Paul_Harvey (3/24/2011)
Oh that's brillant! And it works perfectly.
Thanks very much for your help on this.
🙂

No problem, glad to help 🙂