By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,190 Members | 777 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,190 IT Pros & Developers. It's quick & easy.

Insert query firing Insert & Update trigger at the same time

P: 1
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
Dec 4 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

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