472,342 Members | 1,429 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

Insert query firing Insert & Update trigger at the same time

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
0 2180

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

Similar topics

4
by: DTB | last post by:
I am having trouble creating an INSTEAD OF trigger in SQL Server to replicate a BEFORE UPDATE trigger from ORACLE. Here is a sample of the ORACLE...
1
by: shottarum | last post by:
I currently have 2 tables as follows: CREATE TABLE . ( mhan8 int, mhac02 varchar(5), mhmot varchar(5), mhupmj int )
9
by: Martin | last post by:
Hello, I'm new with triggers and I can not find any good example on how to do the following: I have two tables WO and PM with the following...
16
by: Philip Boonzaaier | last post by:
I want to be able to generate SQL statements that will go through a list of data, effectively row by row, enquire on the database if this exists in...
4
by: Chris Kratz | last post by:
Hello all, We have run into what appears to be a problem with rules and subselects in postgres 7.4.1. We have boiled it down to the following...
6
by: Tom Allison | last post by:
I seemed to remember being able to do this but I can't find the docs. Can I run a sql query to insert new or update existing rows in one query? ...
1
by: aj70000 | last post by:
hi, Here's the scenario 1) I am running a DTS job to fetch some rows from Oracle 2) The job populates the Table A as step 1 3) Then it fires...
3
by: teddysnips | last post by:
I need a trigger (well, I don't *need* one, but it would be optimal!) but I can't get it to work because it references ntext fields. Is there any...
1
by: abhi81 | last post by:
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...
0
by: concettolabs | last post by:
In today's business world, businesses are increasingly turning to PowerApps to develop custom business applications. PowerApps is a powerful tool...
0
better678
by: better678 | last post by:
Question: Discuss your understanding of the Java platform. Is the statement "Java is interpreted" correct? Answer: Java is an object-oriented...
0
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
0
by: CD Tom | last post by:
This only shows up in access runtime. When a user select a report from my report menu when they close the report they get a menu I've called Add-ins...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
0
jalbright99669
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
2
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
0
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and...

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.