Instead of Trigger to handle dup keys
-
I am trying to come up with a way to handle duplicate keys on a batch insert without failing. I'd like to handle this within the DBMS. One way we do this now is a bulk insert into an unrelated working table and then insert into the production table the difference between the working table and the production table. I was wondering if triggers could be used to do a similiar operation. I created the example below which only seems to do one or the other i.e. log the dups or insert the unique rows, but it won't do both why?
if exists (select * from sysobjects where id = object_id(N'[dbo].[bkiTriggerTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[bkiTriggerTest]
GO
if exists (select * from sysobjects where id = object_id(N'[dbo].[bkiTriggerTestDups]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[bkiTriggerTestDups]
GO
--
Create table dbo.bkiTriggerTest
(
pkid int identity(1,1) not null,
dval1 numeric(22,0) default(0),
dval2 nvarchar(4000) default(N'Variable length text field'),
Constraint bkiTriggerTest_PK Primary Key Clustered (pkid),
) ON [Primary]
go
Create table dbo.bkiTriggerTestDups
(
pkid int not null,
dval1 numeric(22,0) default(0),
dval2 nvarchar(4000) default(N'Variable length text field'),
) ON [Primary]
go
if exists (select * from sysobjects where id = object_id(N'[dbo].[bkiTriggerTest_Instead]') and OBJECTPROPERTY(id, N'ExecIsInsteadOfTrigger') = 1)
drop trigger [dbo].[bkiTriggerTest_Instead]
GO
Create trigger dbo.bkiTriggerTest_Instead
on dbo.bkiTriggerTest
INSTEAD OF INSERT
AS
set nocount on
insert into dbo.bkiTriggerTestDups (i2.pkid, i2.dval1, i2.dval2)
select i2.pkid, i2.dval1, i2.dval2
from Inserted i2 inner join dbo.bkiTriggerTest base
on i2.pkid = base.pkid
insert into dbo.bkiTriggerTest (pkid, dval1, dval2)
select pkid, dval1, dval2
from Inserted i
where not exists
(select i2.pkid from Inserted i2 inner join dbo.bkiTriggerTest base
on i2.pkid = base.pkid
)
go
-------------------------------
set identity_insert bkiTriggerTest ON
insert into dbo.bkiTriggerTest (pkid, dval1, dval2)
values( 1, 1, N'Test insert 1')
go
-- contents of bki.dat
-- 4, 4, N'Record 4'
-- 2, 2, N'Record 2'
-- 3, 3, N'Record 3'
--
bulk insert dbo.bkiTriggerTest from 'h:\temp\bki.dat'
with (DATAFILETYPE='char',
KEEPIDENTITY,
FIELDTERMINATOR = ',',
ROWTERMINATOR ='\n',
FIRE_TRIGGERS)
go
select * from dbo.bkiTriggerTest
select * from dbo.bkiTriggerTestDups
go
-- contents of bki2.dat
-- 4, 4, N'Record 4'
-- 2, 2, N'Record 2'
-- 6, 6, N'Record 6'
--
bulk insert dbo.bkiTriggerTest from 'h:\temp\bki2.dat'
with (DATAFILETYPE='char',
KEEPIDENTITY,
FIELDTERMINATOR = ',',
ROWTERMINATOR ='\n',
FIRE_TRIGGERS)
go
select * from dbo.bkiTriggerTest
select * from dbo.bkiTriggerTestDups
go
-
David,
Just use if @@rowcount = 0 condition in your instead of insert trigger just before the actual table insert statement. This will avoid failing statements as well as log the desired duplicates as you coded.
Gopal
-
Little error in your trigger. Please try this :
Create trigger dbo.bkiTriggerTest_Instead
on dbo.bkiTriggerTest
INSTEAD OF INSERT
AS
set nocount on
insert into dbo.bkiTriggerTestDups (i2.pkid, i2.dval1, i2.dval2)
select i2.pkid, i2.dval1, i2.dval2
from Inserted i2 inner join dbo.bkiTriggerTest base
on i2.pkid = base.pkid
insert into dbo.bkiTriggerTest (pkid, dval1, dval2)
select pkid, dval1, dval2
from Inserted i
where i.pkid not in
(select i2.pkid from Inserted i2 inner join dbo.bkiTriggerTestDups base
on i2.pkid = base.pkid
)
-
Gopal (ext5qxt)
If I test for @@ROWCOUNT equal to zero, then I won't execute the second insert when there are dups. But I want to execute both statements because there may be some dups and some not.
Thanks, Dave
-
Grasshopper,
Ahh! I couldn't see it for anything. What I meant was not what I typed. I wanted the difference like what follows:
Create trigger dbo.bkiTriggerTest_Instead
on dbo.bkiTriggerTest
INSTEAD OF INSERT
AS
set nocount on
insert into dbo.bkiTriggerTestDups (i2.pkid, i2.dval1, i2.dval2)
select i2.pkid, i2.dval1, i2.dval2
from Inserted i2 inner join dbo.bkiTriggerTest base
on i2.pkid = base.pkid
insert into dbo.bkiTriggerTest (pkid, dval1, dval2)
select pkid, dval1, dval2
from Inserted i
where not exists
-- where i.pkid not in
(select i2.pkid from Inserted i2 inner join dbo.bkiTriggerTest base
on i.pkid = base.pkid
)
(Hmmm. It looks strange to me with the 92 join syntax, but I guess logically it should be the same.)
Thanks, Dave
-
CHECK IT OUT
Create trigger dbo.bkiTriggerTest_Instead
on dbo.bkiTriggerTest
INSTEAD OF INSERT
AS
-- TRIGGER GETS CALLED FOR EVERY INSERT STATEMENT. ONE ROW AT A TIME
set nocount on
insert into dbo.bkiTriggerTestDups (i2.pkid, i2.dval1, i2.dval2)
select i2.pkid, i2.dval1, i2.dval2
from Inserted i2 inner join dbo.bkiTriggerTest base
on i2.pkid = base.pkid
-- INSERT ONLY IF THERE IN NO DUPLICATE
IF @@ROWCOUNT = 0
insert into dbo.bkiTriggerTest (pkid, dval1, dval2)
select pkid, dval1, dval2
from Inserted i
/* YOU DO NOT NEED THIS CONDITION. AS IT IS INSTEAD OF TRIGGER.
where not exists
-- where i.pkid not in
(select i2.pkid from Inserted i2 inner join dbo.bkiTriggerTest base
on i.pkid = base.pkid
)
*/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply