Why is this happening?
-
I have the following Trigger below. It works however, I get 16 records in the AuditAddress Table when I UPDATE an ADDRESSID. Why do I get 16 records when I'm only updating the AddressID once only?
IF (COLUMNS_UPDATED()) > 0
BEGIN
-- Audit OLD record.
INSERT INTO AuditAddress (AuditTypeID, RecordType, AddressID, AddressLine1, AddressLine2,
AddressLine3, AddressLine4, StreetAddress1, StreetAddress2, Suburb, City, State, Postcode, Country,
WorkstationID, UserName)
SELECT 'U', 'O', AddressID, AddressLine1, AddressLine2, AddressLine3, AddressLine4, StreetAddress1,
StreetAddress2, Suburb, City, State, Postcode, Country, HOST_NAME(), SYSTEM_USER
FROM Deleted D
-- Audit NEW record.
INSERT INTO AuditAddress (AuditTypeID, RecordType, AddressID, AddressLine1, AddressLine2,
AddressLine3, AddressLine4, StreetAddress1, StreetAddress2, Suburb, City, State, Postcode, Country,
WorkstationID, UserName)
SELECT 'U', 'N', AddressID, AddressLine1, AddressLine2, AddressLine3, AddressLine4, StreetAddress1,
StreetAddress2, Suburb, City, State, Postcode, Country, HOST_NAME(), SYSTEM_USER
FROM Inserted I
END
-
16 duplicates - on insert and on delete?
Is it a coincidence that you are inserting exactly 16 fields? (I hope so!)
Have you got other triggers that may be causing some sort of cascading update?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
The trigger doesn't deal with multiple records affected, it just pours the entire contents of inserted and deleted into the audit table. So...... what does the update triggering this look like? And how many rows does it actually update?
Sample DDL (table) few rows of data that when updated actually behaves like this is required to be able to do more than just guess.
/Kenneth
-
From the information Provided is difficult to infer the reason but this is something you need to think about:
1. You are creating two records per updated records on that table
2. Do you have other triggers on it or on child tables that also insert on the Target table?
3. You need to be certain about the commands being sent to the server (Don't Trust UI) Run a trace or send the command through QA
4. Make sure that the amount of records that are affected are exactly the one you expect (Watch out for douplicates, etc)
Cheers
* Noel -
Ok. I have sorted it out!
Firstly, I checked everything that each of you people posted and having done that, it led me to the answer.
I didn't mention to you people that I had the "CASCADE UPDATE RELATED FIELDS" SET to ON for the FOREIGN KEY Relationship between the Address Table and the AccountAddress Table.
I also should have metioned the Database Design structure for the Address info.
Basically, we have an Account Table, an AccountAddress Table and an Address Table. An Account will have 1 or more Addresses, hence the AccountAddress Table.
-
What is the solution u found for the problem.. Will please explain me.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply