Trigger Issue - SQL Server Forums

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)

Trigger Issue

Author

Topic

chaz
Starting Member

4 Posts

Posted - 2006-06-23 : 17:15:55

I'm trying to run a trigger off an UPDATE on a specfic table. I would like the trigger to run WHEN the value of a certain column is updated to a spefic value. IOW, if the value is 4 and the UPDATE sets the value to 1, I want the trigger to send an email and update a datetime value on another table.

The trigger work, but it works any time there is an update on the table. Doesn't seem to matter what the new value is.

The strigger script is below. I want it to run ONLY when @PTStatusID is set to 1


CREATE TRIGGER trgUpdateStatusToReady 

ON [dbo].[tblPTStatusLkp] 

AFTER UPDATE

AS

DECLARE @PTFileNumber varchar(50)
DECLARE @PTStatusID int
DECLARE @fld datetime

IF UPDATE(PTStatusID) 
BEGIN

SELECT @PTFileNumber = FileNumber, @PTStatusID = PTStatusID FROM inserted

IF @PTStatusID = 1

EXEC sp_SendUpdateNotification @PTFileNumber
SET @fld = dbo.fnFirstLegalDate(GetDate())
UPDATE tblFirstLegalDates SET FirstLegalDate = @fld WHERE FileNumber = @PTFileNumber
END

Any help will be appreciated
thanx

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-23 : 17:56:46

IF UPDATE(PTStatusID)
BEGIN

SELECT @PTFileNumber = FileNumber, @PTStatusID = PTStatusID FROM inserted

IF @PTStatusID = 1
begin
EXEC sp_SendUpdateNotification @PTFileNumber
SET @fld = dbo.fnFirstLegalDate(GetDate())
UPDATE tblFirstLegalDates SET FirstLegalDate = @fld WHERE FileNumber = @PTFileNumber
end
END

Note this only works for single row updates and will also run if the value is updated but not changed.
It's not a good idea to send emails from triggers - put it in a table and send via a scheduled job.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

chaz
Starting Member

4 Posts

Posted - 2006-06-23 : 18:53:04

Thanx!

I understand about the email, but in this solution, it needs to go out immediatly. can't wait for a sceduled. This trigger is used in a solution that aids the mortgage foreclosure process. In this scenario time is definitely money.

I'd I had access to the app that acually changes the Db values, I'd have installed this mess in the app code, not the Db. I dislike triggers intensly.

The problem was solved in the interim

thx

quote:
Originally posted by nr

IF UPDATE(PTStatusID)
BEGIN

SELECT @PTFileNumber = FileNumber, @PTStatusID = PTStatusID FROM inserted

IF @PTStatusID = 1
begin
EXEC sp_SendUpdateNotification @PTFileNumber
SET @fld = dbo.fnFirstLegalDate(GetDate())
UPDATE tblFirstLegalDates SET FirstLegalDate = @fld WHERE FileNumber = @PTFileNumber
end
END

Note this only works for single row updates and will also run if the value is updated but not changed.
It's not a good idea to send emails from triggers - put it in a table and send via a scheduled job.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-23 : 19:37:34

>> I understand about the email, but in this solution, it needs to go out immediatly. can't wait for a sceduled.

Not even a few seconds?
Might be that you need the system to fail if the email fails though.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources