saabStory
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER