Link to home
Start Free TrialLog in
Avatar of saabStory
saabStoryFlag for United States of America

asked on

Help with inserting a record with a trigger after updating the table

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:

sam_NextContact
--------------------------
nextID (int) [pk]
entityID  (int) [fk]
groupID  (int)
nextDate  (dt)
nextMethod  (char)
nextSubject (char)
entityMngrID  (int)
completed      (bit)
completionDate  (dt)

trigger
------------------------------
CREATE TRIGGER tr_contactCompletedUpdate ON dbo.sam_NextContact
FOR UPDATE
AS
IF UPDATE( completed )
BEGIN
     IF ( EXISTS(SELECT * FROM Inserted I INNER JOIN Deleted D ON I.nextID = D.nextID WHERE D.completed = 0 AND I.completed = 1) )
     BEGIN
          UPDATE dbo.sam_NextContact
          SET completionDate = getDate()
          WHERE nextID IN (SELECT DISTINCT nextID FROM inserted)
     END
END
ASKER CERTIFIED SOLUTION
Avatar of rafrancisco
rafrancisco

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of saabStory

ASKER

Man - that's good.  I see it now but, not being familiar with triggers didn't know how to apply what I already had.  Thanks so much - works like a charm!