create trigger it_Table1 on Table1 instead of insert as insert into Table1 select * from inserted where SNo not in (select SNo from Table1)
create table Table1(SNo int primary key, SName varchar(255)) GO create trigger tr_table1 on table1 instead of insert as begin if not exists(select * from inserted , Table1 where inserted.sno=table1.sno) begin INSERT Table1 (SNO,SName) SELECT SNO,SNAME FROM INSERTED WHERE not exists(select * from inserted , Table1 where inserted.sno=table1.sno) END END
因为楼主在创建表的时候,已经指定SNo int primary key,即SNo为主键了,所以就不会出现“在插入一条语句时如果插入的这条语句已经存在于表中”的情况。
instead of insert
as
insert into Table1
select * from inserted
where SNo not in (select SNo from Table1)
GO
create trigger tr_table1 on table1
instead of insert
as
begin
if not exists(select * from inserted , Table1 where inserted.sno=table1.sno)
begin
INSERT Table1 (SNO,SName) SELECT SNO,SNAME FROM INSERTED WHERE not exists(select * from inserted , Table1 where inserted.sno=table1.sno)
END
END