Help with inserting a record with a trigger after updating the table
Posted on 2005-05-13
I need some help getting this thing finished. What I have is a web app - this part allows people to marks items on a task list as complete. When they submit the record to update the database, one of the fields is updated and a trigger is fired to insert the current datetime as a timestamp for the event. With the current record complete and closed, I now need to add a new one to the database. I can do this in ASP but thought it would be good experience to try something different.
The trigger works so far for updating with the timestamp, but I don't have the insert routine in there yet. The problem I'm having is that I must load two values - the entityID and the groupID into the new record (these fields won't take nulls) and can't figure out how to get those values from the record I just updated and then turn around and insert the new record. It could be that this is not the best way to do things - that a complete ASP solution would be best - that's okay too - I'm just exploring new options here and figured that as long as I'm at the server, I could same a couple of round trips by modifying the trigger.
The schema of the table is:
nextID (int) [pk]
entityID (int) [fk]
CREATE TRIGGER tr_contactCompletedUpdate ON dbo.sam_NextContact
IF UPDATE( completed )
IF ( EXISTS(SELECT * FROM Inserted I INNER JOIN Deleted D ON I.nextID = D.nextID WHERE D.completed = 0 AND I.completed = 1) )
SET completionDate = getDate()
WHERE nextID IN (SELECT DISTINCT nextID FROM inserted)