October 20, 2010 at 11:36 am
Hey all, looking for a little help on an AFTER INSERT TRIGGER if possible. What I am trying to do is check an existing table for a record that is identical to record that is about to be inserted into the table. If it is, I need to insert that record into a logging table, and prevent the duplicate record from being inserted.
In the code below I am referencing 3 objects, the logical inserted table, dbo.weblead, and dbo.EQ_Audit (Logging table). What the trigger will need to do is fire on insert of a record to the weblead table, and if the inserted record matches a previous record within the last 30 days I need the inserted record to insert into my logging table and NOT the weblead table. Hopefully this makes sense. As you look remember that in order for the inserted record to be considered a duplicate it has to match on only CID, LeadType, and the inserted matching record in the weblead table has to have come in within 30 days (this is msgdate). I have incldued the DDL for dbo.weblead. and dbo.EQ_Audit
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE TRIGGER [EquityLeadDeDup]
ON [dbo].[WebLead]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @EQ_CID INT,
@EQ_MsgDate SMALLDATETIME,
@EQ_ToAddress VARCHAR(64),
@EQ_LeadType INT,
SELECT CASE WHEN EXISTS(
SELECT
@EQ_CID, @EQ_MsgDate, W.LeadADF, GETDATE()
FROM INSERTED AS I
INNER JOIN [dbo].[WebLead] AS W
ON I.[CID] = W.[CID]
WHERE I.[LeadType] = 3
AND W.[LeadType] = 3
AND W.[Msgdate]
BETWEEN I.[MsgDate] AND I.[MsgDate] - 30 )
--THEN
--BEGIN
--INSERT INTO [dbo].[EQ_Audit]
-- ([EQ_CID] , [EQ_MsgDate] , [EQ_LeadADF] ,[EQ_CreateDate])
--SELECT
-- @EQ_CID, @EQ_MsgDate, W.LeadADF, GETDATE()
-- FROM [dbo].[WebLead] W
--END
USE [ABBCS]
GO
/****** Object: Table [dbo].[EQ_Audit] Script Date: 10/20/2010 13:35:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EQ_Audit](
[EQ_AuditID] [int] IDENTITY(1,1) NOT NULL,
[EQ_CID] [int] NULL,
[EQ_MsgDate] [smalldatetime] NULL,
[EQ_LeadADF] [text] NULL,
[EQ_CreateDate] [smalldatetime] NULL,
CONSTRAINT [PK_EQ_Audit] PRIMARY KEY CLUSTERED
(
[EQ_AuditID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[EQ_Audit] ADD CONSTRAINT [DF_EQ_Audit_EQ_CreateDate] DEFAULT (getdate()) FOR [EQ_CreateDate]
GO
USE [ABBCS]
GO
/****** Object: Table [dbo].[WebLead] Script Date: 10/20/2010 13:35:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[WebLead](
[LeadID] [int] IDENTITY(1,1) NOT NULL,
[OwnerID] [smallint] NULL,
[CID] [int] NULL,
[LeadType] [smallint] NULL,
[EntryDate] [datetime] NULL,
[EntryTime] [datetime] NULL,
[MsgDate] [datetime] NULL,
[MsgTime] [datetime] NULL,
[AssignTo] [smallint] NULL,
[AssignDate] [datetime] NULL,
[AssignTime] [datetime] NULL,
[Source] [smallint] NULL,
[SourceURL] [varchar](101) NULL,
[Status] [char](1) NULL,
[AutoResponse] [bit] NULL,
[Response1Type] [char](1) NULL,
[Response1Date] [datetime] NULL,
[Response1Time] [datetime] NULL,
[Response2Type] [char](1) NULL,
[Response2Date] [datetime] NULL,
[Response2Time] [datetime] NULL,
[ToAddress] [varchar](60) NULL,
[DealerNumber] [int] NULL,
[Response1Reported] [bit] NULL,
[Response2Reported] [bit] NULL,
[ResponseComplete] [bit] NULL,
[ProspectID] [varchar](40) NULL,
[MinutesElapsed1] [int] NULL,
[BusMinElapsed1] [int] NULL,
[MinutesElapsed2] [int] NULL,
[BusMinElapsed2] [int] NULL,
[MfrDealerID] [varchar](10) NULL,
[NewLeadReported] [bit] NULL,
[BatchID] [varchar](50) NULL,
[GMLeadID] [varchar](50) NULL,
[OEMLeadIndicator] [varchar](50) NULL,
[StatusChangedTimeStamp] [datetime] NULL,
[Stage] [smallint] NULL,
[LeadADF] [text] NULL,
[RuleID] [int] NULL,
[LockTimeStamp] [datetime] NULL,
[UsedNew] [int] NULL,
[ModelYear] [smallint] NULL,
[Make] [varchar](20) NULL,
[Model] [varchar](40) NULL,
[PartyId] [varchar](50) NULL,
[PolkScore] [int] NULL,
[CentralServerLeadId] [int] NULL,
[SendDemoDrive] [bit] NULL,
[DemoDriveDispoSent] [bit] NULL,
[BadLeadDispoSent] [bit] NULL,
[NewLeadDispoSent] [bit] NULL,
[FirstAssignedDispoSent] [bit] NULL,
[AutoResponseDispoSent] [bit] NULL,
[PolkId] [varchar](50) NULL,
[LeadScoringInsert] [datetime] NULL,
[GMScore] [varchar](25) NULL,
[DupOfWebLeadId] [int] NULL,
[ReasonInvalid] [varchar](80) NULL,
[ASID] [int] NULL,
[InitialAssignTo] [smallint] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
October 20, 2010 at 12:15 pm
You should probably use an instead of trigger. If you do this after the insert...the record already exists because it was just inserted. Then inside your trigger you can decide which table you want to insert to.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 20, 2010 at 12:29 pm
i may have skimmed over your post too quickly and missed something, but it looks like you can get rid of all the variables, and simply fix the trigger to handle muti rows:
CREATE TRIGGER [EquityLeadDeDup]
ON [dbo].[WebLead]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [dbo].[EQ_Audit]
([EQ_CID] , [EQ_MsgDate] , [EQ_LeadADF] ,[EQ_CreateDate])
--this SQL finds all the records that d
SELECT
INSERTED.EQ_CID, INSERTED.EQ_MsgDate, W.LeadADF, GETDATE()
FROM INSERTED AS I
INNER JOIN [dbo].[WebLead] AS W
ON I.[CID] = W.[CID]
WHERE I.[LeadType] = 3
AND W.[LeadType] = 3
AND W.[Msgdate]
BETWEEN I.[MsgDate] AND I.[MsgDate] - 30
END
Lowell
October 20, 2010 at 1:11 pm
"Case" doesn't work that way in SQL. It does in VB, but not in SQL. "Case" is a select option in SQL, not a flow-control statement.
E.g.:
select case when MyColumn = 1 then 'Yes' else 'No' end from My Table;
Is valid SQL.
Case when @MyVariable = 1 then Delete from MyTable;
Is not valid SQL, even though it looks "right" to VB devs. (Might work similarly in other languages. VB is the one I'm familiar with in this regard.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 20, 2010 at 2:40 pm
GSquared (10/20/2010)
"Case" doesn't work that way in SQL. It does in VB, but not in SQL. "Case" is a select option in SQL, not a flow-control statement.E.g.:
select case when MyColumn = 1 then 'Yes' else 'No' end from My Table;Is valid SQL.
Case when @MyVariable = 1 then Delete from MyTable;Is not valid SQL, even though it looks "right" to VB devs. (Might work similarly in other languages. VB is the one I'm familiar with in this regard.)
Lol, funny you mention that because when I was researching my syntax for this statement I found a resource that I bookmarked. When I went back its a VB resource, but its funny that intillisence was happy with it. Now, if I did this as an instead of trigger, and set it to fire on insert, would it allow only insert transactions to commit that did not meet the criteria of my select statement?
October 20, 2010 at 2:47 pm
You could make it do some checking and decide what to do with it.
create trigger myInsertTrigger
instead of insert on MyTable
as begin
if exists(select field from MyTable) then
insert LogTable
else
insert MyTable
end
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 20, 2010 at 4:07 pm
SeanLange (10/20/2010)
You could make it do some checking and decide what to do with it.
create trigger myInsertTrigger
instead of insert on MyTable
as begin
if exists(select field from MyTable) then
insert LogTable
else
insert MyTable
end
That might work, this way if it my condition does not exist and there is no record that meets the criteria of my select, then the inserted table will commit to the weblead table just like it should. Ill put together a statement using this approach and see what you guys think.
October 21, 2010 at 8:20 am
IF Boolean_expression
{ sql_statement | statement_block }
[ ELSE
{ sql_statement | statement_block } ]
Doesnt look like your recommendation supports THEN clause
October 21, 2010 at 9:15 am
Would this even remotely be close?
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE TRIGGER [EquityLeadDeDup]
ON [dbo].[WebLead]
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @EQ_CID INT,
@EQ_MsgDate SMALLDATETIME,
@EQ_ToAddress VARCHAR(64),
@EQ_LeadType INT
IF EXISTS(
SELECT
@EQ_CID, @EQ_MsgDate, W.LeadADF, GETDATE()
FROM INSERTED AS I
INNER JOIN [dbo].[WebLead] AS W
ON I.[CID] = W.[CID]
WHERE I.[LeadType] = 3
AND W.[LeadType] = 3
AND W.[Msgdate]
BETWEEN I.[MsgDate] AND I.[MsgDate] - 30 )
BEGIN
INSERT INTO [dbo].[EQ_Audit]
([EQ_CID] , [EQ_MsgDate] , [EQ_LeadADF] ,[EQ_CreateDate])
SELECT @EQ_CID, @EQ_MsgDate, W.LeadADF, GETDATE()
FROM INSERTED AS I
INNER JOIN [dbo].[WebLead] AS W
ON I.[CID] = W.[CID]
WHERE I.[LeadType] = 3
AND W.[LeadType] = 3
AND W.[Msgdate]
BETWEEN I.[MsgDate] AND I.[MsgDate] - 30
END
ELSE
INSERT INTO [ABBCS].[dbo].[WebLead]
([OwnerID]
,[CID]
,[LeadType]
,[EntryDate]
,[EntryTime]
,[MsgDate]
,[MsgTime]
,[AssignTo]
,[AssignDate]
,[AssignTime]
,[Source]
,[SourceURL]
,[Status]
,[AutoResponse]
,[Response1Type]
,[Response1Date]
,[Response1Time]
,[Response2Type]
,[Response2Date]
,[Response2Time]
,[ToAddress]
,[DealerNumber]
,[Response1Reported]
,[Response2Reported]
,[ResponseComplete]
,[ProspectID]
,[MinutesElapsed1]
,[BusMinElapsed1]
,[MinutesElapsed2]
,[BusMinElapsed2]
,[MfrDealerID]
,[NewLeadReported]
,[BatchID]
,[GMLeadID]
,[OEMLeadIndicator]
,[StatusChangedTimeStamp]
,[Stage]
,[LeadADF]
,[RuleID]
,[LockTimeStamp]
,[UsedNew]
,[ModelYear]
,[Make]
,[Model]
,[PartyId]
,[PolkScore]
,[CentralServerLeadId]
,[SendDemoDrive]
,[DemoDriveDispoSent]
,[BadLeadDispoSent]
,[NewLeadDispoSent]
,[FirstAssignedDispoSent]
,[AutoResponseDispoSent]
,[PolkId]
,[LeadScoringInsert]
,[GMScore]
,[DupOfWebLeadId]
,[ReasonInvalid]
,[ASID]
,[InitialAssignTo])
SELECT
[OwnerID]
,[CID]
,[LeadType]
,[EntryDate]
,[EntryTime]
,[MsgDate]
,[MsgTime]
,[AssignTo]
,[AssignDate]
,[AssignTime]
,[Source]
,[SourceURL]
,[Status]
,[AutoResponse]
,[Response1Type]
,[Response1Date]
,[Response1Time]
,[Response2Type]
,[Response2Date]
,[Response2Time]
,[ToAddress]
,[DealerNumber]
,[Response1Reported]
,[Response2Reported]
,[ResponseComplete]
,[ProspectID]
,[MinutesElapsed1]
,[BusMinElapsed1]
,[MinutesElapsed2]
,[BusMinElapsed2]
,[MfrDealerID]
,[NewLeadReported]
,[BatchID]
,[GMLeadID]
,[OEMLeadIndicator]
,[StatusChangedTimeStamp]
,[Stage]
,[LeadADF]
,[RuleID]
,[LockTimeStamp]
,[UsedNew]
,[ModelYear]
,[Make]
,[Model]
,[PartyId]
,[PolkScore]
,[CentralServerLeadId]
,[SendDemoDrive]
,[DemoDriveDispoSent]
,[BadLeadDispoSent]
,[NewLeadDispoSent]
,[FirstAssignedDispoSent]
,[AutoResponseDispoSent]
,[PolkId]
,[LeadScoringInsert]
,[GMScore]
,[DupOfWebLeadId]
,[ReasonInvalid]
,[ASID]
,[InitialAssignTo]
FROM INSERTED
END
October 21, 2010 at 9:18 am
That looks like it should do the trick quite nicely. 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 25, 2010 at 7:53 am
CELKO (10/22/2010)
1) CASE is an expression and not a statement in SQL. You can use IF-THEN_ELSE constructs in T-SQL or your host language.2) Audits are done outside of the database with third party tools for legal reasons. If you just want keep track of dups for your own information use a MERGE statement. UPDATE the count of occurrences when you get a dup and INSERT the new data.
3) Rule of thumb: don't write procedural code like triggers when you can be declarative. That means no more than 5 triggers in your career.
4) Please normalize your tables. You have more NULL-able columns in one table then the entire payroll system for GM. You have repeated groups, etc.
1) Good to know
2) My requirements state the audit need to be within a certain schema
3) This is a unsupport deprecated app, I dont have access to the code base, and the people that do are unwilling to make necessary code base changes to support functionality of this nature
4) Tell me about it, Ive been fighting this war for a long time.
October 26, 2010 at 7:23 pm
zlthomps (10/21/2010)
Would this even remotely be close?
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE TRIGGER [EquityLeadDeDup]
ON [dbo].[WebLead]
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @EQ_CID INT,
@EQ_MsgDate SMALLDATETIME,
@EQ_ToAddress VARCHAR(64),
@EQ_LeadType INT
IF EXISTS(
SELECT
@EQ_CID, @EQ_MsgDate, W.LeadADF, GETDATE()
FROM INSERTED AS I
INNER JOIN [dbo].[WebLead] AS W
ON I.[CID] = W.[CID]
WHERE I.[LeadType] = 3
AND W.[LeadType] = 3
AND W.[Msgdate]
BETWEEN I.[MsgDate] AND I.[MsgDate] - 30 )
BEGIN
INSERT INTO [dbo].[EQ_Audit]
([EQ_CID] , [EQ_MsgDate] , [EQ_LeadADF] ,[EQ_CreateDate])
SELECT @EQ_CID, @EQ_MsgDate, W.LeadADF, GETDATE()
FROM INSERTED AS I
INNER JOIN [dbo].[WebLead] AS W
ON I.[CID] = W.[CID]
WHERE I.[LeadType] = 3
AND W.[LeadType] = 3
AND W.[Msgdate]
BETWEEN I.[MsgDate] AND I.[MsgDate] - 30
END
ELSE
INSERT INTO [ABBCS].[dbo].[WebLead]
([OwnerID]
,[CID]
,[LeadType]
,[EntryDate]
,[EntryTime]
,[MsgDate]
,[MsgTime]
,[AssignTo]
,[AssignDate]
,[AssignTime]
,[Source]
,[SourceURL]
,[Status]
,[AutoResponse]
,[Response1Type]
,[Response1Date]
,[Response1Time]
,[Response2Type]
,[Response2Date]
,[Response2Time]
,[ToAddress]
,[DealerNumber]
,[Response1Reported]
,[Response2Reported]
,[ResponseComplete]
,[ProspectID]
,[MinutesElapsed1]
,[BusMinElapsed1]
,[MinutesElapsed2]
,[BusMinElapsed2]
,[MfrDealerID]
,[NewLeadReported]
,[BatchID]
,[GMLeadID]
,[OEMLeadIndicator]
,[StatusChangedTimeStamp]
,[Stage]
,[LeadADF]
,[RuleID]
,[LockTimeStamp]
,[UsedNew]
,[ModelYear]
,[Make]
,[Model]
,[PartyId]
,[PolkScore]
,[CentralServerLeadId]
,[SendDemoDrive]
,[DemoDriveDispoSent]
,[BadLeadDispoSent]
,[NewLeadDispoSent]
,[FirstAssignedDispoSent]
,[AutoResponseDispoSent]
,[PolkId]
,[LeadScoringInsert]
,[GMScore]
,[DupOfWebLeadId]
,[ReasonInvalid]
,[ASID]
,[InitialAssignTo])
SELECT
[OwnerID]
,[CID]
,[LeadType]
,[EntryDate]
,[EntryTime]
,[MsgDate]
,[MsgTime]
,[AssignTo]
,[AssignDate]
,[AssignTime]
,[Source]
,[SourceURL]
,[Status]
,[AutoResponse]
,[Response1Type]
,[Response1Date]
,[Response1Time]
,[Response2Type]
,[Response2Date]
,[Response2Time]
,[ToAddress]
,[DealerNumber]
,[Response1Reported]
,[Response2Reported]
,[ResponseComplete]
,[ProspectID]
,[MinutesElapsed1]
,[BusMinElapsed1]
,[MinutesElapsed2]
,[BusMinElapsed2]
,[MfrDealerID]
,[NewLeadReported]
,[BatchID]
,[GMLeadID]
,[OEMLeadIndicator]
,[StatusChangedTimeStamp]
,[Stage]
,[LeadADF]
,[RuleID]
,[LockTimeStamp]
,[UsedNew]
,[ModelYear]
,[Make]
,[Model]
,[PartyId]
,[PolkScore]
,[CentralServerLeadId]
,[SendDemoDrive]
,[DemoDriveDispoSent]
,[BadLeadDispoSent]
,[NewLeadDispoSent]
,[FirstAssignedDispoSent]
,[AutoResponseDispoSent]
,[PolkId]
,[LeadScoringInsert]
,[GMScore]
,[DupOfWebLeadId]
,[ReasonInvalid]
,[ASID]
,[InitialAssignTo]
FROM INSERTED
END
Unless you are absolutely certain that records will never post to your table more than one record at a time, this is not going to work (and even if you are certain, it's a bad way to write triggers because chances are you're wrong -- that's just the way "never" usually works). Lowell, tried to make the point above but it looks like it didn't sink in (plus, in his example he only covered the part of the trigger which logs the duplicates).
This is more like what you need (I didn't test it but it should at least set you on the right track).
- Les
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE TRIGGER [EquityLeadDeDup]
ON [dbo].[WebLead]
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [dbo].[EQ_Audit]
([EQ_CID] , [EQ_MsgDate] , [EQ_LeadADF] ,[EQ_CreateDate])
SELECT EQ_CID =CID
, EQ_MsgDate=MsgDate
, EQ_LeadADF= W.LeadADF
, EQ_CreateDate=GETDATE()
FROM INSERTED AS I
INNER JOIN [dbo].[WebLead] AS W
ON I.[CID] = W.[CID]
WHERE I.[LeadType] = 3
AND W.[LeadType] = 3
AND W.[Msgdate] BETWEEN I.[MsgDate] AND I.[MsgDate] - 30
INSERT INTO [ABBCS].[dbo].[WebLead]
([OwnerID]
,[CID]
,[LeadType]
,[EntryDate]
,[EntryTime]
,[MsgDate]
,[MsgTime]
,[AssignTo]
,[AssignDate]
,[AssignTime]
,[Source]
,[SourceURL]
,[Status]
,[AutoResponse]
,[Response1Type]
,[Response1Date]
,[Response1Time]
,[Response2Type]
,[Response2Date]
,[Response2Time]
,[ToAddress]
,[DealerNumber]
,[Response1Reported]
,[Response2Reported]
,[ResponseComplete]
,[ProspectID]
,[MinutesElapsed1]
,[BusMinElapsed1]
,[MinutesElapsed2]
,[BusMinElapsed2]
,[MfrDealerID]
,[NewLeadReported]
,[BatchID]
,[GMLeadID]
,[OEMLeadIndicator]
,[StatusChangedTimeStamp]
,[Stage]
,[LeadADF]
,[RuleID]
,[LockTimeStamp]
,[UsedNew]
,[ModelYear]
,[Make]
,[Model]
,[PartyId]
,[PolkScore]
,[CentralServerLeadId]
,[SendDemoDrive]
,[DemoDriveDispoSent]
,[BadLeadDispoSent]
,[NewLeadDispoSent]
,[FirstAssignedDispoSent]
,[AutoResponseDispoSent]
,[PolkId]
,[LeadScoringInsert]
,[GMScore]
,[DupOfWebLeadId]
,[ReasonInvalid]
,[ASID]
,[InitialAssignTo])
SELECT
[OwnerID]
,[CID]
,[LeadType]
,[EntryDate]
,[EntryTime]
,[MsgDate]
,[MsgTime]
,[AssignTo]
,[AssignDate]
,[AssignTime]
,[Source]
,[SourceURL]
,[Status]
,[AutoResponse]
,[Response1Type]
,[Response1Date]
,[Response1Time]
,[Response2Type]
,[Response2Date]
,[Response2Time]
,[ToAddress]
,[DealerNumber]
,[Response1Reported]
,[Response2Reported]
,[ResponseComplete]
,[ProspectID]
,[MinutesElapsed1]
,[BusMinElapsed1]
,[MinutesElapsed2]
,[BusMinElapsed2]
,[MfrDealerID]
,[NewLeadReported]
,[BatchID]
,[GMLeadID]
,[OEMLeadIndicator]
,[StatusChangedTimeStamp]
,[Stage]
,[LeadADF]
,[RuleID]
,[LockTimeStamp]
,[UsedNew]
,[ModelYear]
,[Make]
,[Model]
,[PartyId]
,[PolkScore]
,[CentralServerLeadId]
,[SendDemoDrive]
,[DemoDriveDispoSent]
,[BadLeadDispoSent]
,[NewLeadDispoSent]
,[FirstAssignedDispoSent]
,[AutoResponseDispoSent]
,[PolkId]
,[LeadScoringInsert]
,[GMScore]
,[DupOfWebLeadId]
,[ReasonInvalid]
,[ASID]
,[InitialAssignTo]
FROM INSERTED
WHERE NOT EXISTS
(
SELECT 1
FROM INSERTED AS D_I
INNER JOIN [dbo].[WebLead] AS D_W
ON D_I.[CID] = D_W.[CID]
WHERE D_I.[LeadType] = 3
AND D_W.[LeadType] = 3
AND D_W.[Msgdate] BETWEEN D_I.[MsgDate] AND D_I.[MsgDate] - 30
AND D_I.[CID] = I.[CID]
AND D_I.[LeadType] = I.[LeadType]
AND D_I.[MsgDate] = I.[MsgDate]
)
END
October 27, 2010 at 11:24 am
lnoland (10/26/2010)
zlthomps (10/21/2010)
Would this even remotely be close?
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE TRIGGER [EquityLeadDeDup]
ON [dbo].[WebLead]
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @EQ_CID INT,
@EQ_MsgDate SMALLDATETIME,
@EQ_ToAddress VARCHAR(64),
@EQ_LeadType INT
IF EXISTS(
SELECT
@EQ_CID, @EQ_MsgDate, W.LeadADF, GETDATE()
FROM INSERTED AS I
INNER JOIN [dbo].[WebLead] AS W
ON I.[CID] = W.[CID]
WHERE I.[LeadType] = 3
AND W.[LeadType] = 3
AND W.[Msgdate]
BETWEEN I.[MsgDate] AND I.[MsgDate] - 30 )
BEGIN
INSERT INTO [dbo].[EQ_Audit]
([EQ_CID] , [EQ_MsgDate] , [EQ_LeadADF] ,[EQ_CreateDate])
SELECT @EQ_CID, @EQ_MsgDate, W.LeadADF, GETDATE()
FROM INSERTED AS I
INNER JOIN [dbo].[WebLead] AS W
ON I.[CID] = W.[CID]
WHERE I.[LeadType] = 3
AND W.[LeadType] = 3
AND W.[Msgdate]
BETWEEN I.[MsgDate] AND I.[MsgDate] - 30
END
ELSE
INSERT INTO [ABBCS].[dbo].[WebLead]
([OwnerID]
,[CID]
,[LeadType]
,[EntryDate]
,[EntryTime]
,[MsgDate]
,[MsgTime]
,[AssignTo]
,[AssignDate]
,[AssignTime]
,[Source]
,[SourceURL]
,[Status]
,[AutoResponse]
,[Response1Type]
,[Response1Date]
,[Response1Time]
,[Response2Type]
,[Response2Date]
,[Response2Time]
,[ToAddress]
,[DealerNumber]
,[Response1Reported]
,[Response2Reported]
,[ResponseComplete]
,[ProspectID]
,[MinutesElapsed1]
,[BusMinElapsed1]
,[MinutesElapsed2]
,[BusMinElapsed2]
,[MfrDealerID]
,[NewLeadReported]
,[BatchID]
,[GMLeadID]
,[OEMLeadIndicator]
,[StatusChangedTimeStamp]
,[Stage]
,[LeadADF]
,[RuleID]
,[LockTimeStamp]
,[UsedNew]
,[ModelYear]
,[Make]
,[Model]
,[PartyId]
,[PolkScore]
,[CentralServerLeadId]
,[SendDemoDrive]
,[DemoDriveDispoSent]
,[BadLeadDispoSent]
,[NewLeadDispoSent]
,[FirstAssignedDispoSent]
,[AutoResponseDispoSent]
,[PolkId]
,[LeadScoringInsert]
,[GMScore]
,[DupOfWebLeadId]
,[ReasonInvalid]
,[ASID]
,[InitialAssignTo])
SELECT
[OwnerID]
,[CID]
,[LeadType]
,[EntryDate]
,[EntryTime]
,[MsgDate]
,[MsgTime]
,[AssignTo]
,[AssignDate]
,[AssignTime]
,[Source]
,[SourceURL]
,[Status]
,[AutoResponse]
,[Response1Type]
,[Response1Date]
,[Response1Time]
,[Response2Type]
,[Response2Date]
,[Response2Time]
,[ToAddress]
,[DealerNumber]
,[Response1Reported]
,[Response2Reported]
,[ResponseComplete]
,[ProspectID]
,[MinutesElapsed1]
,[BusMinElapsed1]
,[MinutesElapsed2]
,[BusMinElapsed2]
,[MfrDealerID]
,[NewLeadReported]
,[BatchID]
,[GMLeadID]
,[OEMLeadIndicator]
,[StatusChangedTimeStamp]
,[Stage]
,[LeadADF]
,[RuleID]
,[LockTimeStamp]
,[UsedNew]
,[ModelYear]
,[Make]
,[Model]
,[PartyId]
,[PolkScore]
,[CentralServerLeadId]
,[SendDemoDrive]
,[DemoDriveDispoSent]
,[BadLeadDispoSent]
,[NewLeadDispoSent]
,[FirstAssignedDispoSent]
,[AutoResponseDispoSent]
,[PolkId]
,[LeadScoringInsert]
,[GMScore]
,[DupOfWebLeadId]
,[ReasonInvalid]
,[ASID]
,[InitialAssignTo]
FROM INSERTED
END
Unless you are absolutely certain that records will never post to your table more than one record at a time, this is not going to work (and even if you are certain, it's a bad way to write triggers because chances are you're wrong -- that's just the way "never" usually works). Lowell, tried to make the point above but it looks like it didn't sink in (plus, in his example he only covered the part of the trigger which logs the duplicates).
This is more like what you need (I didn't test it but it should at least set you on the right track).
- Les
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE TRIGGER [EquityLeadDeDup]
ON [dbo].[WebLead]
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [dbo].[EQ_Audit]
([EQ_CID] , [EQ_MsgDate] , [EQ_LeadADF] ,[EQ_CreateDate])
SELECT EQ_CID =CID
, EQ_MsgDate=MsgDate
, EQ_LeadADF= W.LeadADF
, EQ_CreateDate=GETDATE()
FROM INSERTED AS I
INNER JOIN [dbo].[WebLead] AS W
ON I.[CID] = W.[CID]
WHERE I.[LeadType] = 3
AND W.[LeadType] = 3
AND W.[Msgdate] BETWEEN I.[MsgDate] AND I.[MsgDate] - 30
INSERT INTO [ABBCS].[dbo].[WebLead]
([OwnerID]
,[CID]
,[LeadType]
,[EntryDate]
,[EntryTime]
,[MsgDate]
,[MsgTime]
,[AssignTo]
,[AssignDate]
,[AssignTime]
,[Source]
,[SourceURL]
,[Status]
,[AutoResponse]
,[Response1Type]
,[Response1Date]
,[Response1Time]
,[Response2Type]
,[Response2Date]
,[Response2Time]
,[ToAddress]
,[DealerNumber]
,[Response1Reported]
,[Response2Reported]
,[ResponseComplete]
,[ProspectID]
,[MinutesElapsed1]
,[BusMinElapsed1]
,[MinutesElapsed2]
,[BusMinElapsed2]
,[MfrDealerID]
,[NewLeadReported]
,[BatchID]
,[GMLeadID]
,[OEMLeadIndicator]
,[StatusChangedTimeStamp]
,[Stage]
,[LeadADF]
,[RuleID]
,[LockTimeStamp]
,[UsedNew]
,[ModelYear]
,[Make]
,[Model]
,[PartyId]
,[PolkScore]
,[CentralServerLeadId]
,[SendDemoDrive]
,[DemoDriveDispoSent]
,[BadLeadDispoSent]
,[NewLeadDispoSent]
,[FirstAssignedDispoSent]
,[AutoResponseDispoSent]
,[PolkId]
,[LeadScoringInsert]
,[GMScore]
,[DupOfWebLeadId]
,[ReasonInvalid]
,[ASID]
,[InitialAssignTo])
SELECT
[OwnerID]
,[CID]
,[LeadType]
,[EntryDate]
,[EntryTime]
,[MsgDate]
,[MsgTime]
,[AssignTo]
,[AssignDate]
,[AssignTime]
,[Source]
,[SourceURL]
,[Status]
,[AutoResponse]
,[Response1Type]
,[Response1Date]
,[Response1Time]
,[Response2Type]
,[Response2Date]
,[Response2Time]
,[ToAddress]
,[DealerNumber]
,[Response1Reported]
,[Response2Reported]
,[ResponseComplete]
,[ProspectID]
,[MinutesElapsed1]
,[BusMinElapsed1]
,[MinutesElapsed2]
,[BusMinElapsed2]
,[MfrDealerID]
,[NewLeadReported]
,[BatchID]
,[GMLeadID]
,[OEMLeadIndicator]
,[StatusChangedTimeStamp]
,[Stage]
,[LeadADF]
,[RuleID]
,[LockTimeStamp]
,[UsedNew]
,[ModelYear]
,[Make]
,[Model]
,[PartyId]
,[PolkScore]
,[CentralServerLeadId]
,[SendDemoDrive]
,[DemoDriveDispoSent]
,[BadLeadDispoSent]
,[NewLeadDispoSent]
,[FirstAssignedDispoSent]
,[AutoResponseDispoSent]
,[PolkId]
,[LeadScoringInsert]
,[GMScore]
,[DupOfWebLeadId]
,[ReasonInvalid]
,[ASID]
,[InitialAssignTo]
FROM INSERTED
WHERE NOT EXISTS
(
SELECT 1
FROM INSERTED AS D_I
INNER JOIN [dbo].[WebLead] AS D_W
ON D_I.[CID] = D_W.[CID]
WHERE D_I.[LeadType] = 3
AND D_W.[LeadType] = 3
AND D_W.[Msgdate] BETWEEN D_I.[MsgDate] AND D_I.[MsgDate] - 30
AND D_I.[CID] = I.[CID]
AND D_I.[LeadType] = I.[LeadType]
AND D_I.[MsgDate] = I.[MsgDate]
)
END
Thanks for the info again ... You are correct in that it is possible for two leads/records to post to the weblead table at the same time. I will test with this as well.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply