Trigger not working as expected

Question

Trigger not working as expected

bjohnson-660281

Grasshopper

Points: 15
More actions
January 20, 2010 at 9:03 am

#215743

Hi everyone.
I have a table called "casemain". This table includes permit number, permit status, and other permit information. I set a trigger on this table which places the both the permit status into a separate table called "wc_status_log" whenever the case status changes.
Once the trigger fires, if any permit is updated, the values are added to the "wc_status_log" table. Unfortunately, I ONLY want the values added when the permit status changes. I don't care about any other changes.
The trigger I currently have set on the "casemain" table looks like this:
CREATE TRIGGER casemain_statuslog ON [dbo].[casemain]
FOR UPDATE
AS
SET NOCOUNT ON
IF UPDATE (csm_status)
BEGIN
INSERT wc_status_log
SELECT csm_caseno, csm_status, csm_updateby, csm_updated
FROM inserted
END
I'm new at creating triggers, so I'm not sure if it's something obvious I'm missing.
Thanks.

Viewing 4 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323546 More actions · Answer 1

I'm a little weak on the UPDATE function, but i thought that if the column is included in the update statement, even if it is still the same value, the UPDATE() returns true; someone can smack me down if i'm wrong.

instead, i would compare INSERTED to DELETED values for csm_status to check for a change; here i'm assuming the column csm_caseno is the PK which would identify a unique row.

CREATE TRIGGER casemain_statuslog ON [dbo].[casemain]

FOR UPDATE

AS

SET NOCOUNT ON

INSERT wc_status_log

SELECT

[INSERTED].csm_caseno,

[INSERTED].csm_status,

[INSERTED].csm_updateby,

[INSERTED].csm_updated

FROM [INSERTED]

LEFT OUTER JOIN [DELETED]

ON [INSERTED].csm_caseno = [DELETED].csm_caseno

WHERE [INSERTED].csm_status <> [DELETED].csm_status

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 2

Lowell (1/20/2010)
I'm a little weak on the UPDATE function, but i thought that if the column is included in the update statement, even if it is still the same value, the UPDATE() returns true;

Absolutely correct. Same with the Columns_Updated function.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

bjohnson-660281 Grasshopper Points: 15 More actions · Answer 3

Ahhh - good to know for future reference. Thanks so much for the assistance!