Trigger Creates Duplicates Dispite Equality Check
-
Hello,
I have a table called DailyAccountsDownload_test that is repopulated every day. There are four columns in that table that have to be tracked for data changes; Zip, TC, SlsRep, and TieCode. These changes are recorded in another table called DailyAccountsDownload_Tracking. I have a Trigger on DailyAccountsDownload_test that records the changes to DailyAccountsDownload_Tracking.
DailyAccountsDownload_Tracking looks like this;
CREATE TABLE [dbo].[DailyAccountsDownload_Tracking](
[MMS] [char](8) NOT NULL,
[AcctCode] [varchar](16) NOT NULL DEFAULT ('NA'),
[Zip] [char](9) NOT NULL DEFAULT (''),
[TC] [char](2) NOT NULL DEFAULT (''),
[SlsRep] [char](8) NOT NULL DEFAULT (''),
[TieCode] [char](10) NOT NULL DEFAULT (''),
[TrackingDate] [datetime] NOT NULL DEFAULT (GETDATE()),
CONSTRAINT [PK_DailyAccountsDownload_Tracking_NonClust] PRIMARY KEY NONCLUSTERED
(
[MMS] ASC,
[TrackingDate] ASC
) ON [PRIMARY]
) ON [PRIMARY]
**********************
The MMS column in DailyAccountsDownload_test contains unique values.
The following TRIGGER needs to do this;
1. Insert records into DailyAccountsDownload_Tracking if DailyAccountsDownload_test contains records that don't exist in DailyAccountsDownload_Tracking (new records).
2. If any one of the four column values inserted into DailyAccountsDownload_test is different than the MAX(TrackingDate) record on the same column value in DailyAccountsDownload_tracking, insert a new record. In this way, if the column value changes back to an equivalent historical value that is older than MAX(TrackingDate), the record will be created.
Before I show the code, this is what the TRIGGER can and cannot do;
1. The initial insert of the records into the tracking table works fine.
2. When an update to any of the four fields occurs at the source, the tracking table gets a new record.
3. When any of the four fields is updated to a former value that is older than the MAX(TrackingDate) value, a record gets created.
The problem I am having is that when subsequent repopulations of DailyAccountsDownload_test occur, duplicate records are written for every record in DailyAccountsDownload_tracking whether a change occurs or not.
The following is the Trigger code;
******************************
CREATE TRIGGER [dbo].[InsertTracking] ON [dbo].[DailyAccountsDownload_test]
FOR INSERT, UPDATE
AS
--Insert new records into tracking. The first record will have all of the current values
BEGIN
INSERT DailyAccountsDownload_Tracking
(MMS, AcctCode, Zip, TC, SlsRep, TieCode)
SELECT
Id.MMS#, RTRIM(ISNULL(Id.AcctCode,'')) AS AcctCode, ISNULL(Id.Zip,'') AS Zip, ISNULL(Id.TC,'') AS TC, ISNULL(Id.SlsRep,'') AS SlsRep, ISNULL(Id.TieCode,'') AS TieCode
FROM Inserted Id
LEFT OUTER JOIN DailyAccountsDownload_Tracking hs
ON Id.MMS# = hs.MMS
WHERE
hs.MMS IS NULL
END
--Check for differences in Zip, TC, SlsRep, TieCode. Insert a new record
BEGIN
INSERT DailyAccountsDownload_Tracking
(MMS, AcctCode, Zip, TC, SlsRep, TieCode)
SELECT
Id.MMS#, RTRIM(ISNULL(Id.AcctCode,'')) AS AcctCode, ISNULL(Id.Zip,'') AS Zip,
ISNULL(Id.TC,'') AS TC, ISNULL(Id.SlsRep,'') AS SlsRep, ISNULL(Id.TieCode,'') AS TieCode
FROM
Inserted Id
WHERE
Id.MMS# IN (SELECT MMS FROM DailyAccountsDownload_Tracking)
AND
(
--Get the record with the latest date in case a value changes back to something
--already recorded.
(
Id.Zip NOT IN(SELECT hs.ZIP FROM DailyAccountsDownload_Tracking hs
Inner Join Inserted id ON id.MMS# = hs.MMS
WHERE TrackingDate IN (SELECT MAX(TrackingDate) AS MaxTracking FROM DailyAccountsDownload_Tracking
 
) 
OR
(
Id.TC NOT IN(SELECT hs.TC FROM DailyAccountsDownload_Tracking hs
Inner Join Inserted id ON id.MMS# = hs.MMS
WHERE TrackingDate IN (SELECT MAX(TrackingDate) AS MaxTracking FROM DailyAccountsDownload_Tracking
 
) 
OR
(
Id.SlsRep NOT IN(SELECT hs.SlsRep FROM DailyAccountsDownload_Tracking hs
Inner Join Inserted id ON id.MMS# = hs.MMS
WHERE TrackingDate IN (SELECT MAX(TrackingDate) AS MaxTracking FROM DailyAccountsDownload_Tracking
 
) 
OR
(
Id.TieCode NOT IN(SELECT hs.TieCode FROM DailyAccountsDownload_Tracking hs
Inner Join Inserted id ON id.MMS# = hs.MMS
WHERE TrackingDate IN (SELECT MAX(TrackingDate) AS MaxTracking FROM DailyAccountsDownload_Tracking
 
) 
 
END
*******************************************
If the initial INSERT in the Trigger has a LEFT JOIN from Inserted to joined table where MMS in the joined table IS NULL, then why do I get the duplicate records? One other note; This Trigger causes the insert to run very slow. Should I consider writing this some other way?
Thank you for your help!
CSDunn
-
This was removed by the editor as SPAM
-
I think I've isolated the problem. I'll need to post again on the specific issue.
Thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply