• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 196
  • Last Modified:

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
0
saabStory
Asked:
saabStory
1 Solution
 
rafranciscoCommented:
Try this:

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)

          INSERT INTO dbo.sam_NextContact (entityID, groupID)
          SELECT I.entityID, I.groupID
          FROM Inserted I INNER JOIN Deleted D ON I.nextID = D.nextID WHERE D.completed = 0 AND I.completed = 1
     END
END
0
 
saabStoryAuthor Commented:
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!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now