i created a trigger that will record the changes made to a table .everything works fine except the insert query.whenerever i try to insert a record it fires insert and update triger at the same time which means i hav 2 record for every insert operation.
any help appreciated.
thank u
herez teh code i tried.
ALTER TRIGGER trg_ContactAddress_Audit
ON Address
FOR DELETE, INSERT, UPDATE
AS
BEGIN
DECLARE @ExecStr varchar(50), @Qry nvarchar(255)
CREATE TABLE #inputbuffer
(
EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(255)
)
SET @ExecStr = 'DBCC INPUTBUFFER(' + STR(@@SPID) + ')'
INSERT INTO #inputbuffer EXEC (@ExecStr)
SET @Qry = (SELECT EventInfo FROM #inputbuffer)
-- Inserting information to a EventLog table
--Handling DELETE Operation
IF (Select Count(*) from deleted )>= 1 and (Select Count(*) from inserted)<> 1
BEGIN
INSERT INTO [MAILmanNFPTest].[dbo].[ContactAddress_Audit]
(
[old_AddressLine1],
[old_AddressLine2],
[old_AddressLine3],
[old_Town],
[old_State],
[old_PostalCode],
[old_DoNotSell],
[Old_DoNotmarket],
[old_PrimaryAddress],
[Event_Type],
[Command_Text],
Event_Time,
ServerName,
DatabaseName,
OLD_ENTRY
)
(SELECT
DELETED.AddressLine1,
DELETED.AddressLine2,
DELETED.AddressLine3,
DELETED.Town,
DELETED.State,
DELETED.PostalCode,
DELETED.DoNotSell,
DELETED.DoNotmarket,
DELETED.PrimaryAddress,
'DELETE',
@Qry,
getdate(),
@@SERVERNAME,
db_name()+' '+SYSTEM_USER,
DELETED.ROWGUID
FROM DELETED )
END
--Handling INSERT operation
IF (Select Count(*) from Inserted )>= 1 and (Select Count(*) from deleted)<> 1
BEGIN
INSERT INTO [MAILmanNFPTest].[dbo].[ContactAddress_Audit]
(
[new_AddressLine1],
[new_AddressLine2],
[new_AddressLine3],
[new_Town],
[new_State],
[new_PostalCode],
[new_DoNotSell],
[new_DoNotMarket],
[new_PrimaryAddress],
[Event_Type],
[Command_Text],
Event_Time,
ServerName,
DatabaseName,
NEW_ENTRY
)
(SELECT
INSERTED.AddressLine1,
INSERTED.AddressLine2,
INSERTED.AddressLine3,
INSERTED.Town,
INSERTED.State,
INSERTED.PostalCode,
INSERTED.DoNotSell,
INSERTED.DoNotMarket,
INSERTED.PrimaryAddress,
'INSERT',
@qRY,
getdate(),
@@SERVERNAME,
db_name()+' '+SYSTEM_USER,
INSERTED.ROWGUID
FROM INSERTED
)
--delete from ContactAddress_Audit where old_entry= new_entry
END
--Handling UPDATE operation
IF update(AddressLine1)
or update(AddressLine2)
or update(AddressLine3)
or update(Town)
or update(State)
or update(PostalCode)
or update(DoNotMarket)
or update(DoNotSell)
or update(PrimaryAddress)
BEGIN
INSERT INTO [MAILmanNFPTest].[dbo].[ContactAddress_Audit]
(
[old_AddressLine1],
[old_AddressLine2],
[old_AddressLine3],
[old_Town],
[old_State],
[old_PostalCode],
[old_DoNotSell],
[Old_DoNotmarket],
[old_PrimaryAddress],
[new_AddressLine1],
[new_AddressLine2],
[new_AddressLine3],
[new_Town],
[new_State],
[new_PostalCode],
[new_DoNotSell],
[new_DoNotMarket],
[new_PrimaryAddress],
[Event_Type],
[Command_Text],
Event_Time,
ServerName,
DatabaseName,
OLD_ENTRY,
NEW_ENTRY
)
(SELECT
DELETED.AddressLine1,
DELETED.AddressLine2,
DELETED.AddressLine3,
DELETED.Town,
DELETED.State,
DELETED.PostalCode,
DELETED.DoNotSell,
DELETED.DoNotmarket,
DELETED.PrimaryAddress,
INSERTED.AddressLine1,
INSERTED.AddressLine2,
INSERTED.AddressLine3,
INSERTED.Town,
INSERTED.State,
INSERTED.PostalCode,
INSERTED.DoNotSell,
INSERTED.DoNotMarket,
INSERTED.PrimaryAddress,
'UPDATE',
@Qry,
getdate(),
@@SERVERNAME,
db_name()+' '+SYSTEM_USER,
DELETED.ROWGUID,
INSERTED.ROWGUID
FROM INSERTED,deleted
)
END
END
SET ANSI_NULLS ON