22,210
社区成员
发帖
与我相关
我的任务
分享
我看是你写的有问题,
我执行一点问题都没有, 完全可以做到功能
-- =============================================
-- Author: T.O.P
-- Create date: 2009/11/28
-- Version: SQL SERVER 2005
-- =============================================
if object_id('[tB]') is not null drop table [tB]
go
create table [tB]([L01] varchar(1),[L02] numeric(2,1),[L03] numeric(2,1))
insert [tB]
select 'A',1.3,1.5 union all
select 'B',2,6 union all
select 'C',4.2,5.3
go
create trigger tri_tab on [tB]
instead of insert
as
insert into [tB] select * from inserted a where not exists(select 1 from [tB] where L01=a.L01)
go
insert [tB]
select 'A',1.3,1.5
insert [tB]
select 'D',1.3,1.5
select * from [tB]
drop trigger tri_tab
drop table tb
--测试结果:
/*
L01 L02 L03
---- --------------------------------------- ---------------------------------------
A 1.3 1.5
B 2.0 6.0
C 4.2 5.3
D 1.3 1.5
(4 row(s) affected)
*/
create trigger tri_tab on table
after of insert
as
delete t
from inserted a inner join [table] t on a.L01 = t.L01
where exists(select 1 from [table] where L01=a.L01)
这个不用写出发器吧, 加个unique key不就可以了~
create trigger tri_tab on table instead of insert
as
insert into [table] select * from inserted a where not exists(select 1 from [table] where L01=a.L01)