A Question about Triggers from a Newbie
-
Hello all, my names Jonah. I've just started getting into SQL 7 and have been presented with many problems.
My first problem is this.
I want a trigger to fire when there has been an update to a certain column within a table. If there has been an alteration within this Column it will automatically send an email to the relevant people.
I have set up a DTS to Import data from an AS400. When this data is imported it obviously goes within my named database. The DTS is scheduled to run every morning so I hope for the emails to come flowing through as soon as the DTS finishes executing the package.
My problem is creating the trigger. I am very new to SQL Server and have looked in a few books but still can't work out how to do it.
The furthest I got was this:
1) DTS Imports from an Access query
2) It then puts this query within a table in SQL Server.
3) The columns in the table are:
PartID-------Description--------Date---------Quantity
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?
Thanks
-
This is possible. However, instead of a trigger sending e-mail why not have the trigger send information to a send-email table and the last step in the package will be to send e-mail. This way the process will run smoother, faster, should have minimal locks, etc...
You should be able to research CREATE TRIGGER in BOL
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Good Thinkin.
I have been asking a few other people and they have come up with this code.
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
PS: GGOPDT is the date field within the AS400
The only thing is i get the following error message:
Error 156: Incorrect syntax near the word 'BEGIN'
Must pass parameter number 2 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form of '@name = value'.
Any suggestions?
-
I'm not sure if your logic is correct, but I do see a couple of syntax errors, you are missing a ')' after the word 'INSERTED' in your first while loop. You have also misspelled @message.
I also agree with AJ about saving the emails in a table and sending them out later. Let's say that there are 100 changes, 100 emails are going to be sent. That could be pretty annoying.
I don't have tons of experience with triggers, but I think you should make this as efficient as possible.
Hope this helps,
Kathi
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply