Help On Trigger

Question

Help On Trigger

Aashish Patel

SSC Rookie

Points: 36
More actions
August 20, 2007 at 7:12 am

#197353

Hi,
I am using the following trigger but it seems to do the second update and not the first update.
CREATE TRIGGER [PREPSTATUSCOMPLETE] ON [dbo].[PrepLog]
FOR UPDATE
AS
IF EXISTS(SELECT FINISHTIME FROM PREPLOG WHERE FINISHTIME = '---')
UPDATE RMJOB
SET PSTATUS = 'PREPPING' FROM RMJOB,PREPLOG WHERE PREPLOG.FINISHTIME = '---'
AND RMJOB.RMJOBID = PREPLOG.RMJOBID
IF EXISTS(SELECT FINISHTIME FROM PREPLOG WHERE FINISHTIME != '---')
UPDATE RMJOB
SET PSTATUS = 'PREPPING COMPLETE' FROM RMJOB,PREPLOG WHERE PREPLOG.FINISHTIME != '---'
AND RMJOB.RMJOBID = PREPLOG.RMJOBID
The first update does not get triggered but the second update gets triggered as soon as the if condition is meant.
Can somebody please help me on this?
A.

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

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

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

Can't see any reason, unless there are no rows that satisfy the conditions "FINISHTIME = '---'" and 'RMJOB.RMJOBID = PREPLOG.RMJOBID'

If you do a select for those conditions, what do you get back? Maybe try a print inside that if, to see if you get in there, and a select inside there as well, just for testing purposes.

Another point, there should be no need to check the entire table for the condition when you're doing the update. I would imagine you just need to check the updated records.

If that is the case, you can change your IF EXISTS to read from the inserted table (which contains the new version of the rows been updated)

Also, since you only seem to be concerned with the row FinishTime, you could also wrap the entire code in IF UPDATE(FinishTime). Then updates that don't affect that column won't execute the rest of the trigger

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

Aashish Patel SSC Rookie Points: 36 More actions · Answer 2

Yes I do get back the required rows which I need to update when I do the Select statement.

I tried the IF UPDATE but it did not work. I am not updating the whole table. I am only updating the rows which meet the IF EXISTS condition.

Any idea.

A.

-=JLK=- SSCertifiable Points: 5315 More actions · Answer 3

What is the data type of the column FINISHTIME? Since it doesn't appear to be a syntax error, posting the definition of the entire table the trigger is assigned to along with the ENTIRE trigger definition would help others better evaluate the problem. Without seeing the whole picture we can't be sure your not simply resetting the "FINISHTIME" value earlier in the statement.

Is it possible that what your testing for has not actually reached the table until AFTER your trigger? That might explain why you can manually test for the value but the TRIGGER doesn't find it.

James.

Aashish Patel SSC Rookie Points: 36 More actions · Answer 4

Aashish Patel

SSC Rookie

Points: 36

August 20, 2007 at 8:57 am

#728002

The datatype for FINISHTIME IS nvarchar(50).

-=JLK=- SSCertifiable Points: 5315 More actions · Answer 5

Well nvarchar(50) shouldn't make a difference. Unless your willing to post the DDL (Data Definition Language) for the Table and Trigger I doubt you'll get much more help.

The best way to get help is to post the definitions with some sample data and the Update/Insert statement that you believe should produce both results in the trigger, from that we can see if it is a SQL problem, Data problem, Design Problem, Logic Problem or something else.

James.