Urgent help needed with trigger!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Urgent help needed with trigger!

Author

Topic

JonahMk2
Starting Member

21 Posts

Posted - 2004-10-11 : 05:48:49

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 OR ELSE

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-10-11 : 06:04:29

CREATE TRIGGER NewPartIDUpdate ON PartIDUpdateNT
FOR UPDATE
AS
DECLARE @tblID table(Row int identity(1,1),Message nvarchar(4000)), @rowcount int, @currRow int
IF UPDATE (GGOPDT)
BEGIN

insert into @tblID
select 'Part ' + partid + 'Changed' from deleted
set @rowcount=@@rowcount + 1
set @currRow=0

while @currRow<@rowcount
begin
EXEC Master..xp_sendmail @recipients = 'me@meltd.com,someoneelse@meltd.com',
@subject = 'Part date change', @meggase = (select message from @tblid where row=@currRow)
set @currRow=@currRow+1
end

END

just edited the trigger you submitted, no idea on what your ddl is.

hope this helps...

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-11 : 06:30:05

(Select max(PartID) FROM Inserted) <-- that simple ?

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources