471,114 Members | 1,422 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,114 software developers and data experts.

Insert query firing Insert & Update trigger at the same time.

3
Hello All,
I have a table on which I have created a insert,Update and a Delete trigger. All these triggers write a entry to another audit table with the unique key for each table and the timestamp.

Insert and Update trigger work fine when i have only one of them defined.

However when I have all the 3 triggers in place and when i try to fire a insert query on the statement. It triggers both insert and update trigger at the same time and has the same timestamp in the audit table.


Insert trigger goes as
CREATE TRIGGER InsRecord ON [dbo].[tableA]
AFTER INSERT
AS
insert Audit(change_id,change_table,change_type,date_chan ge)
select uniqueid, srctable,'Insert',GetDate() from inserted

Update trigger goes as
CREATE TRIGGER UpdRecord ON [dbo].[tableA]
FOR UPDATE
AS
insert Audit(change_id,change_table,change_type,date_chan ge)
select uniqueid, srctable,'Update',GetDate() from inserted

Delete Trigger goes as
CREATE TRIGGER delRecord ON [dbo].[tableA]
FOR DELETE
AS
insert Audit(change_id,change_table,change_type,date_chan ge)
select uniqueid, srctable,'Delete',GetDate() from deleted

Note:This tableA has relations with 2 other tables on 1 field each from each table but i don't think it should matter.

Please advise how to prevent it.
Dec 28 '06 #1
1 6016
navamnk
15
CREATE TRIGGER alteredRecord ON [dbo].[tableA]
FOR INSERT, UPDATE, DELETE
AS
BEGIN

...declare lngIns & lngDel

SELECT lngIns=count(col1)
from inserted

select lngDel=count(col1)
from deleted

IF lngIns>0 and lngDel=0
....inserted
else if lngIns>0 and lngDel>0
...updated
else if lngIns=0 and lngDel>0
...deleted
end

END
Jan 3 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

9 posts views Thread by Martin | last post: by
16 posts views Thread by Philip Boonzaaier | last post: by
6 posts views Thread by Tom Allison | last post: by
1 post views Thread by aj70000 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.