用instead of触发器,下例实现了将test1表数据插入test2表,如果ID为4的倍数则跳过,不执行插入操作: --------------------------------------------------------------------------------------------------------------------------------- create table test1(id int,name varchar(10)) create table test2(id int,name varchar(10)) insert into test1 select 1,'AAAA' insert into test1 select 2,'BBBB' insert into test1 select 3,'CCCC' insert into test1 select 4,'DDDD' insert into test1 select 5,'EEEE' insert into test1 select 6,'FFFF' insert into test1 select 7,'GGGG' insert into test1 select 8,'HHHH' gocreate trigger trg_test2 on test2 instead of insert as begin declare @id int,@name varchar(10) declare t_cursor cursor for select * from inserted open t_cursor fetch next from t_cursor into @id,@name
while @@fetch_status=0 begin if @id%4!=0 insert into test2 values(@id,@name) fetch next from t_cursor into @id,@name end close t_cursor deallocate t_cursor end goinsert into test2 select * from test1 goselect * from test2/* ID Name ---- -------- 1 AAAA 2 BBBB 3 CCCC 5 EEEE 6 FFFF 7 GGGG */drop trigger trg_test2 drop table test1,test2 go
同意楼上意见!大数据量插入时性能不会很好 还不如直接在inserted表上做筛选 insert into tb select * from inserted where condition1 and condition2SET XACT_ABORT ON全部执行或全部回滚 楼主的意思是插入部分数据 设为OFF(默认)还差不多
create table test1(id int,name varchar(10)) create table test2(id int,name varchar(10)) insert into test1 select 1,'AAAA' insert into test1 select 2,'BBBB' insert into test1 select 3,'CCCC' insert into test1 select 4,'DDDD' insert into test1 select 5,'EEEE' insert into test1 select 6,'FFFF' insert into test1 select 7,'GGGG' insert into test1 select 8,'HHHH' gocreate trigger trg_test2 on test2 instead of insert as begin insert into test2 select * from inserted where id%4<>0 end goinsert into test2 select * from test1 goselect * from test2drop trigger trg_test2 drop table test1,test2 go
你可以在触发器里边对用inserted和原表进行连接删除,删除那些不满足条件的记录.create trigger dbo.tri_Test on Test for insert as if @@rowcount=0 return Delete a from 表 a join inserted b on a.[id]=b.[id] where 条件................... Go
--创建测试表---------------------------------- create table Test([id] int,[name] varchar(10))insert Test select 1,'aa' insert Test select 2,'bb' insert Test select 3,'cc'--创建触发器-------------------------------------删除名称长度不等于两个字符的记录-------------create trigger dbo.tri_Test on Test for insert as if @@rowcount=0 return delete a from Test a join inserted b on a.[id]=b.[id] where len(b.[name])<>2 Go --测试----------------------------------------- insert Test select * from Test_1
---------------------------------------------------------------------------------------------------------------------------------
create table test1(id int,name varchar(10))
create table test2(id int,name varchar(10))
insert into test1 select 1,'AAAA'
insert into test1 select 2,'BBBB'
insert into test1 select 3,'CCCC'
insert into test1 select 4,'DDDD'
insert into test1 select 5,'EEEE'
insert into test1 select 6,'FFFF'
insert into test1 select 7,'GGGG'
insert into test1 select 8,'HHHH'
gocreate trigger trg_test2
on test2
instead of insert
as
begin
declare @id int,@name varchar(10)
declare t_cursor cursor for select * from inserted
open t_cursor
fetch next from t_cursor into @id,@name
while @@fetch_status=0
begin
if @id%4!=0
insert into test2 values(@id,@name)
fetch next from t_cursor into @id,@name
end
close t_cursor
deallocate t_cursor
end
goinsert into test2 select * from test1
goselect * from test2/*
ID Name
---- --------
1 AAAA
2 BBBB
3 CCCC
5 EEEE
6 FFFF
7 GGGG
*/drop trigger trg_test2
drop table test1,test2
go
谢谢!看不懂,怎么去做啊to: libin_ftsafe(子陌红尘)
能否不用游标啊!
谢谢!
select * from inserted where condition1 and condition2SET XACT_ABORT ON全部执行或全部回滚 楼主的意思是插入部分数据 设为OFF(默认)还差不多
create table test1(id int,name varchar(10))
create table test2(id int,name varchar(10))
insert into test1 select 1,'AAAA'
insert into test1 select 2,'BBBB'
insert into test1 select 3,'CCCC'
insert into test1 select 4,'DDDD'
insert into test1 select 5,'EEEE'
insert into test1 select 6,'FFFF'
insert into test1 select 7,'GGGG'
insert into test1 select 8,'HHHH'
gocreate trigger trg_test2
on test2
instead of insert
as
begin
insert into test2 select * from inserted where id%4<>0
end
goinsert into test2 select * from test1
goselect * from test2drop trigger trg_test2
drop table test1,test2
go
on Test for insert
as
if @@rowcount=0
return
Delete a
from 表 a join inserted b
on a.[id]=b.[id]
where 条件...................
Go
create table Test([id] int,[name] varchar(10))insert Test select 1,'aa'
insert Test select 2,'bb'
insert Test select 3,'cc'--创建触发器-------------------------------------删除名称长度不等于两个字符的记录-------------create trigger dbo.tri_Test
on Test for insert
as
if @@rowcount=0
return
delete a
from Test a join inserted b
on a.[id]=b.[id]
where len(b.[name])<>2
Go
--测试-----------------------------------------
insert Test
select * from Test_1