Trigger to run on individual records in SQL 2000 ???

[sndkick]

How do I write an update trigger that will run for each record, rather than for the entire set of updated records? Is there any way to access individual records of the UPDATED table?

I have a table called CLASSES with a STATUS field amongst others. This status field contains a value of "open" or "closed" or "pending". When this field changes from "open" to "closed" (but not from pending to closed) I need to run some SQL statements that update another table containing information related to that class.

The problem is that I'm building onto a database that already sits under another application. I cannot guarantee that they'll never do a mass update of the table. But I need to know if they updated this STATUS field.

Is there any way to do this in a trigger? I can't do just a generic trigger because if they do a mass update for all classes, like bump all of the start times by an hour, I don't want to update my other table for every class, just for the ones that are changing from "open" to "closed".

Right now I'm anticipating something like

----------------------------------------------
CREATE TRIGGER trg_updateAvail ON dbo.classes
FOR INSERT, UPDATE
AS

DELCARE
@class_id int,
@cur_status char(8),
@new_status char(8),
@tmp char(5)

SET @class_id = (SELECT class_id FROM updated)

SET @cur_status = (SELECT status FROM classes WHERE class_id = @class_id)

SET @new_status = (SELECT status FROM updated)


IF @cur_status = "open" AND @new_status = "closed"
BEGIN
SET @tmp = (SELECT field1 FROM tbl_one WHERE class_id = @class_id)

UPDATE tbl_two SET
field2 = @tmp
WHERE class_id = @class_id
END

----------------------------------------------



Thanks,
ST

 

[sndkick]

I just wanted to point out one thing, the above attempt fails because if multiple rows are updated I'd be attempting to SET "class_id" = multiple records of the (SELECT class_id FROM updated) query, which of course doesn't work.

I'm looking for a way to get around this.

Thanks,
ST

 

[MeanGreen]

I think this will do what you are trying to do:

CREATE TRIGGER trg_updateAvail ON dbo.classes
FOR INSERT, UPDATE
AS


update tbl_two
set field2 = field1
from
tbl_one join inserted ins
on tbl_one.class_id = ins.class_id
join classes cls
on cls.class_id = ins.class_id
where cls.status = 'open' and ins.status = 'closed'
END


Hope this helps.