September 15, 2005 at 11:30 pm
I have 3 Tables Account, AccountAddress and Address.
I have a C#.NET Application. On the Account Form, I INSERT AccountName, TradingName etc, as well as Address information. This data is stored in 3 different Tables as mentioned above.
I have written an Audit Trigger to capture any UPDATES to the Account Table. The Trigger code is below.
The problem I have is, I cannot capture any changes made to the AddressLine1. Of course AddressLine1 is stored in the Address Table. I though that by joining on the Address Table, I would capture the AddressLine1 from the DELETED virtual Table from the Trigger.
Is there a way to do this? Or do I have to create sperate triggers on the AccountAddress & Address tables?
IF (COLUMNS_UPDATED()) > 0
BEGIN
-- Audit OLD record.
INSERT INTO AuditAccount (AuditTypeID, AccountID, RecordType, AccountName, ABN, AccountTypeID, AccountStatusTypeID, BuildingID,
SalesRegionID, IndustryID, WorkstationID, UserName, AddressLine1)
SELECT 'U', D.AccountID, 'O', D.AccountName, D.ABN, D.AccountTypeID, D.AccountStatusTypeID, D.BuildingID, D.SalesRegionID, D.IndustryID,
HOST_NAME(), SYSTEM_USER, A.AddressLine1
FROM Deleted D INNER JOIN AccountAddress AD ON D.AccountID = AD.AccountId
INNER JOIN Address A ON AD.AddressID = A.AddressID
-- Audit NEW record.
INSERT INTO AuditAccount (AuditTypeID, AccountID, RecordType, AccountName, ABN, AccountTypeID, AccountStatusTypeID, BuildingID,
SalesRegionID, IndustryID, WorkstationID, UserName, AddressLine1)
SELECT 'U', I.AccountID, 'N', I.AccountName, I.ABN, I.AccountTypeID, I.AccountStatusTypeID, I.BuildingID, I.SalesRegionID, I.IndustryID,
HOST_NAME(), SYSTEM_USER, A.AddressLine1
FROM Inserted I INNER JOIN AccountAddress AD ON I.AccountID = AD.AccountId
INNER JOIN Address A ON AD.AddressID = A.AddressID
END
September 16, 2005 at 7:54 am
As you said, you need to create separate triggers on those tow tables. The trigger on the table Account can't capture changes on other tables
September 18, 2005 at 5:01 pm
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply