thelke
MIS
- Apr 9, 2002
- 84
I have an insert trigger. I would like an Update and a Delete. I can get the Update to work, but it also fires on Insert. but I cannot get the Delete to work, and have never written a Delete trigger. Please Help.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Trigger Help
- Thread starter thelke
- Start date
- Status
tlbroadbent
MIS
If the update also fires on insert, check the trigger to make sure if doesn't say For Insert, Update. A single tigger can fire on one, two or three actions. Sounds like you want each action to have its own trigger.
Creating a delete trigger is the same process as creating insert or update triggers. Trigger syntax is well documented in SQL Books Online.
Terry L. Broadbent - DBA
Computing Links:
faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.
Creating a delete trigger is the same process as creating insert or update triggers. Trigger syntax is well documented in SQL Books Online.
Terry L. Broadbent - DBA
Computing Links:
faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.
I am having the same issue with the Update/Insert triggers. I have double checked my code and I have two different triggers one for Insert and one for Update.
This is the insert trigger....
create trigger tr_ins_Contact on DskContact for Insert as
Begin
Begin
UPDATE DskContact
SET SysAddDatetime = getdate()
FROM inserted
WHERE DskContact.ContactID = inserted.ContactID
End
end
The update trigger is as follows......
create trigger tr_ins_Contact on DskContact for Insert as
Begin
Begin
UPDATE DskContact
SET SysAddDatetime = getdate()
FROM inserted
WHERE DskContact.ContactID = inserted.ContactID
End
end
Each time I insert I get (1 row(s) affected) twice....one for the insert and then one for the update.....
This is the insert trigger....
create trigger tr_ins_Contact on DskContact for Insert as
Begin
Begin
UPDATE DskContact
SET SysAddDatetime = getdate()
FROM inserted
WHERE DskContact.ContactID = inserted.ContactID
End
end
The update trigger is as follows......
create trigger tr_ins_Contact on DskContact for Insert as
Begin
Begin
UPDATE DskContact
SET SysAddDatetime = getdate()
FROM inserted
WHERE DskContact.ContactID = inserted.ContactID
End
end
Each time I insert I get (1 row(s) affected) twice....one for the insert and then one for the update.....
Judie,
You are calling an update statement inside your insert trigger, so of course the update trigger fires. Perhaps a better way to handle this would be to set a default value on SYSADDDate as getdate(). This would handle the insert without a trigger which would be faster and then you only need the update trigger.
You are calling an update statement inside your insert trigger, so of course the update trigger fires. Perhaps a better way to handle this would be to set a default value on SYSADDDate as getdate(). This would handle the insert without a trigger which would be faster and then you only need the update trigger.
tlbroadbent
MIS
If you don't want a trigger to fire on an update performed by a trigger, you can set the Nested Triggers option OFF. Terry L. Broadbent - DBA
Computing Links:
faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.
Computing Links:
faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.
- Status
Similar threads
- Locked
- Question