Urgent help required in Trigger
-
My Manager is getting well annoyed with me about a trigger which I need to create.
I want a trigger to fire when there has been an update to a certain column within a table. If there has been a change within this Column it will automatically send an email to the relevant people.
My problem is creating the trigger. I am very new to SQL Server and have looked in a few books and on BOL but still can't work out how to do it.
The trigger will fire when there has been a change in the date to a particular PartID from the previous DTS Import. Well, thats what I want to happen. Is this possible within SQL Server? I have been to other forums and they have come up with this code? The only thing is it does not execute properly.
CREATE TRIGGER NewPartIDUpdate ON PartIDUpdateNT
FOR UPDATE
AS
DECLARE @PartID int, @msg varchar(100)
IF UPDATE (GGOPDT)
BEGIN
SELECT @PartID = 0
WHILE @PartID < (Select max(PartID) FROM Inserted
BEGIN
SELECT @PartID = MIN(PartID) FROM Inserted WHERE PartID > @PartID
IF EXISTS (SELECT * FROM Inserted i, Deleted d WHERE i.PartID = d.PartID AND
i.PartID = @PartID AND i.GGOPDT <> d.GGOPDT)
BEGIN
SELECT @msg = 'Part' + convert(varchar(20),@PartID) + 'Changed'
EXEC Master..xp_sendmail @recipients = 'me@meltd.com,someoneelse@meltd.com', @subject = 'Part
date change', @meggase = @msg
END
END
END
Any suggestions? My boss wants to see this trigger in action by tomorrow
-
Some thoughts....
You really shouldn't attempt to send mail from within the trigger - it'll only annoy your boss even more, sooner rather than later. You never want to do external calls from a trigger.
What you can do is to create a queue table, and have the trigger insert into that if the date changes - then set up a job that goes through that table and send the mails on the side (well away from the transaction firing the trigger)
IF UPDATE(column) doesn't quite work as one might expect. It doesn't fire only if data has changed - it fires whenever (column) is mentioned in the statement, so it's in reality often pretty useless. You need to check the previous value against the new value, and only if they differ trigger a mail. So you can skip that in the trigger as well.
Here's an example of a trigger that will insert the partID's that have their dates changed into a queuetable. The trigger will work even if several rows are updated in the same batch.
CREATE TRIGGER my_trig
ON my_table
FOR update
AS
set nocount on
if exists( select * from inserted i join deleted d on i.partID = d.partID and i.crDate <> d.crDate )
begin
insert mailqueuetable ( partID )
select partID from inserted i join deleted d on i.partID = d.partID and i.crDate <> d.crDate
end
GO
To send mail, process mailqueuetable separately at some convenient interval.
/Kenneth
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply