create trigger tr1 on bb for insert as insert into bb select * from inserted if exists(select bb.id from bb join inserted on bb.id=inserted.id group by bb.id having count(bb.id)>1) begin rollback tran print'cannot' end
求触发器,CREATE TRIGGER DeleteReplicate ON dbo.table FOR INSERT AS if exists(select column1 from table where column1=(select column1 from inserted)) rollback我写的不对呀.什么都插不进去了.
--建立测试环境:create table bb (id int,name varchar(8))---添加记录 insert bb select 1,'a' union all select 2,'b' union all select 3,'c' union all select 4,'d'--创建触发器create trigger tr1 on bb for insert as insert into bb select * from inserted if exists(select bb.id from bb join inserted on bb.id=inserted.id ) begin rollback tran print'不能输入重复数据,操作被取消' end--测试insert into bb values (1,'e')
to xiaonvjing(飞扬) 你的一个都插不进呀.
--建立测试环境: create table bb (id int,name varchar(8))---添加记录 insert bb select 1,'a' union all select 2,'b' union all select 3,'c' union all select 4,'d'--创建触发器create trigger tr1 on bb instead of insert as if exists(select * from bb where id in (select id from inserted) and name in (select name from inserted)) begin rollback tran print'不能输入重复数据,操作被取消' end else begin insert into bb select * from inserted end
--测试 insert into bb values (1,'a')/* 不能输入重复数据,操作被取消 */insert into bb values (5,'e')/* (所影响的行数为 1 行) (所影响的行数为 1 行) */select * from bb/* id name -- ----- 1 a 2 b 3 c 4 d 5 e */
不好意思,改正一下,现在没问题啦,你试一下吧!!! --建立测试环境: drop table bb create table bb (id int,name varchar(8))---添加记录 insert bb select 1,'a' union all select 2,'b' union all select 3,'c' union all select 4,'d'--创建触发器 create trigger tr1 on bb for insert as begin tran if exists(select id from bb group by id having count(id)=2 ) begin rollback tran end else begin commit tran end--测试 insert into bb values (5,'d')
--建立测试环境: create table bb (id int,name varchar(8))---添加记录 insert bb select 1,'a' union all select 2,'b' union all select 3,'c' union all select 4,'d'select * from bb --创建触发器create trigger tr1 on bb instead of insert as declare @id int declare @name varchar(800)declare tt cursor for select id,name from inserted open tt fetch next from tt into @id,@name while (@@fetch_status =0) begin if exists(select * from bb where id =@id and name=@name) begin rollback tran print'不能输入重复数据,操作被取消' end else begin insert into bb values(@id,@name) end fetch next from tt into @id,@name end close tt deallocate tt --测试 insert bb select 1,'a' union all select 5,'d'
----结果 id name 1 a 2 b 3 c 4 d 5 d
to 回复人: wmlin(紫风) 我觉得麻烦 我也傻傻问一句,你们的格式怎么都一样? --建立测试环境: ---添加记录 --创建触发器 --测试这是用摸板做的吗?给我讲讲这些常识吧.
好方法,我试试.
能不能帮写一个?
on bb
for insert
as
insert into bb select * from inserted
if exists(select bb.id from bb join inserted on
bb.id=inserted.id group by bb.id having count(bb.id)>1)
begin
rollback tran
print'cannot'
end
FOR INSERT
AS
if exists(select column1 from table
where column1=(select column1 from inserted))
rollback我写的不对呀.什么都插不进去了.
insert bb select 1,'a'
union all select 2,'b'
union all select 3,'c'
union all select 4,'d'--创建触发器create trigger tr1
on bb
for insert
as
insert into bb select * from inserted
if exists(select bb.id from bb join inserted on
bb.id=inserted.id )
begin
rollback tran
print'不能输入重复数据,操作被取消'
end--测试insert into bb values (1,'e')
你的一个都插不进呀.
create table bb (id int,name varchar(8))---添加记录
insert bb select 1,'a'
union all select 2,'b'
union all select 3,'c'
union all select 4,'d'--创建触发器create trigger tr1
on bb
instead of insert
as
if exists(select * from bb where id in (select id from inserted) and name in (select name from inserted))
begin
rollback tran
print'不能输入重复数据,操作被取消'
end
else begin
insert into bb select * from inserted
end
--测试
insert into bb values (1,'a')/*
不能输入重复数据,操作被取消
*/insert into bb values (5,'e')/*
(所影响的行数为 1 行)
(所影响的行数为 1 行)
*/select * from bb/*
id name
-- -----
1 a
2 b
3 c
4 d
5 e
*/
--建立测试环境:
drop table bb
create table bb (id int,name varchar(8))---添加记录
insert bb select 1,'a'
union all select 2,'b'
union all select 3,'c'
union all select 4,'d'--创建触发器
create trigger tr1
on bb
for insert
as
begin tran
if exists(select id from bb group by id having count(id)=2 )
begin
rollback tran
end
else
begin
commit tran
end--测试 insert into bb values (5,'d')
create table bb (id int,name varchar(8))---添加记录
insert bb select 1,'a'
union all select 2,'b'
union all select 3,'c'
union all select 4,'d'select * from bb
--创建触发器create trigger tr1
on bb
instead of insert
as declare @id int
declare @name varchar(800)declare tt cursor for select id,name from inserted
open tt
fetch next from tt into @id,@name
while (@@fetch_status =0)
begin
if exists(select * from bb where id =@id and name=@name)
begin
rollback tran
print'不能输入重复数据,操作被取消'
end
else
begin
insert into bb values(@id,@name)
end
fetch next from tt into @id,@name
end
close tt
deallocate tt
--测试
insert bb select 1,'a'
union all select 5,'d'
id name
1 a
2 b
3 c
4 d
5 d
我觉得麻烦 我也傻傻问一句,你们的格式怎么都一样?
--建立测试环境:
---添加记录
--创建触发器
--测试这是用摸板做的吗?给我讲讲这些常识吧.