after 触发器对数据发生(insert,update,delete)时,才触发。 instead of 触发器是代替触发,要自己在触发器里对数据进行更新。看下面例子就知道了 create table a(id int ,a varchar(10)) create table b(id int ,b varchar(10)) create trigger test on a instead of insert as begin insert into b select * from inserted end insert into a values(1,'aaa') insert into a values(2,'aaabbb') select * from a (0 row(s) affected) select * from b id b -------------------- 1 aaa 2 aaabbb (2 row(s) affected) --drop table a,b
--instead of 触发器和 after --最好是看帮助if object_id('tb') is not null drop table tb go create table tb(id int) go create trigger tri_tb on tb instead of insert as begin --操作前执行,因为没写任何代码,所以没有插入到表tb任何记录 print '0 rows' endinsert into tb select 1 select * from tb --0行 ---------------------------------------- create trigger tri_tb2 on tb after insert as begin --操作后执行,插入动作已经完成,这里可以通过取 inserted表的记录,插入到其他表或更新其他表 print '1 rows' endinsert into tb select 1 select * from tb --1行
if object_id('tb_a') is not null drop table tb_a go create table tb_a([A_NAME] varchar(10),[A_SEX] varchar(10)) insert tb_a select '张三','男' union all select '李四','女' union all select '王五','男' go if object_id('tb_b') is not null drop table tb_b go create table tb_b([id] int,[b_count] int) insert tb_b select 1,2 union all select 2,1 go if object_id('tg') is not null drop trigger tg go create trigger tg on tb_a for insert,update,delete as update b set b.[b_count]=a.cnt from tb_b b join (select id=case [A_SEX] when '男' then 1 else 2 end,count([A_SEX]) cnt from tb_a group by [A_SEX]) a on b.id=a.id join (select id=case [A_SEX] when '男' then 1 else 2 end from inserted) c on a.id=c.id update b set b.[b_count]=a.cnt from tb_b b join (select id=case [A_SEX] when '男' then 1 else 2 end,count([A_SEX]) cnt from tb_a group by [A_SEX]) a on b.id=a.id join (select id=case [A_SEX] when '男' then 1 else 2 end from deleted) c on a.id=c.id go -->开始测试并查询 insert tb_a select '赵六','男' select * from tb_b /* id b_count ----------- ----------- 1 3 2 1(2 行受影响) */ update tb_a set [A_SEX]='女' where [A_NAME]='赵六' select * from tb_b /* id b_count ----------- ----------- 1 2 2 2(2 行受影响) */ delete tb_a where [A_NAME]='赵六' select * from tb_b /* id b_count ----------- ----------- 1 2 2 1(2 行受影响) */
insert into C values(11,'AAA') insert into C values(22,'BBB') insert into C values(33,'CCC') --------------------------------- insert into a values(100,'xxx') insert into a values(200,'yyy') insert into a values(300,'zzz') (其初表a,表b为空) 结果如下: select * from a --------结果为空 select * from b --------结果如下(为什么会这样????) 11 AAA 22 BBB 33 CCC 11 AAA 22 BBB 33 CCC 11 AAA 22 BBB 33 CCC select * from c ------结果如下(最初值,即表c一开始的值) 11 AAA 22 BBB 33 CCC
instead of --> 取而代之 after -->亡羊補牢
insert into a values(100,'xxx') insert into a values(200,'yyy') insert into a values(300,'zzz') 因为触发器是INSTEAD OF,所以将不会对A执行 INSERT操作, 而只执行触发器里面的SQL语句。 向B中插入C中的所有数据
after 是先对表做INSERT 或UPDATE或DELETE语句 之后才执行 触发器里的操作而instead of 是只执行触发器里的操作, 对表做INSERT 或UPDATE或DELETE语句 不执行! SQL语句+触发器执行步骤 AFTER SQL语句 ->触发器 instead of 触发器
insert into C values(11,'AAA') insert into C values(22,'BBB') insert into C values(33,'CCC') --------------------------------- insert into a values(100,'xxx') insert into a values(200,'yyy') insert into a values(300,'zzz') 你先向C表插入了三条数据,然后向a表插入数据,此时每向a 插入一条触发一次,b表就是9条记录了。
楼上的搞错了。 首先A表,B表都为空。 C表的值为: 11 AAA 22 BBB 33 CCC 下面这个触发器是将表C的值插入表B。 create trigger test on a instead of insert as begin insert into b select * from c end那插入后表B的值为什么会变下面这样。搞不明白。请教大家。 select * from b --------结果如下(为什么会这样????) 11 AAA 22 BBB 33 CCC 11 AAA 22 BBB 33 CCC 11 AAA 22 BBB 33 CCC 表B为什么有这么多行。怎么不是前面三行????? 我只是将表C的值插入表B。 表C只有三行啊。请指教!!!
因为你的触发器是这样: create trigger test on a instead of insert as begin insert into b select * from c end 改成这样试试: create trigger test on a instead of insert as begin insert into b select * from inserted end
楼上的很不错啊 我借鉴他们的实践了一下哦!不错啊!都对。。、 create table a(id int ,a varchar(10)) create table b(id int ,b varchar(10)) create table c(id int ,b varchar(10)) insert into a values(1,'aaa') insert into a values(2,'aaabbb') insert into C values(11,'AAA') insert into C values(22,'BBB') insert into C values(33,'CCC') insert into a values(100,'xxx') insert into a values(200,'yyy') insert into a values(300,'zzz')
select * from a select * from b select * from cdelete from b where b='Test' delete from c delete from a --测试instead of触发器是否是只执行触发器里面的SQL语句,不会对A执行 INSERT操作 create trigger test on a instead of insert as begin insert into b select * from inserted end --测试after触发器是否是执行向表C追加完记录后,再更新表B中ID为3的记录 alter trigger TestCafter on c after insert as begin update b set b='Test' from b where id='33' end --测试after触发器是否是执行向表C追加完记录后,再向表a追加当前向表C追加的记录 create trigger cInsert on c after insert as begin insert into a select * from inserted end --测试inserted是否是指当前追加的记录 create trigger testC_tbInserted on c instead of insert as[color=#339966] begin insert into c select * from inserted insert into a select * from inserted end --删除触发器 drop trigger cInsert drop trigger test drop trigger testC_tbInserted[/color]
instead of 触发器是代替触发,要自己在触发器里对数据进行更新。看下面例子就知道了
create table a(id int ,a varchar(10))
create table b(id int ,b varchar(10)) create trigger test
on a
instead of insert
as
begin
insert into b
select * from inserted
end insert into a values(1,'aaa')
insert into a values(2,'aaabbb') select * from a
(0 row(s) affected) select * from b
id b
--------------------
1 aaa
2 aaabbb
(2 row(s) affected) --drop table a,b
--instead of 触发器和 after
--最好是看帮助if object_id('tb') is not null
drop table tb
go
create table tb(id int)
go
create trigger tri_tb
on tb
instead of insert
as
begin
--操作前执行,因为没写任何代码,所以没有插入到表tb任何记录
print '0 rows'
endinsert into tb select 1
select * from tb
--0行
----------------------------------------
create trigger tri_tb2
on tb
after insert
as
begin
--操作后执行,插入动作已经完成,这里可以通过取 inserted表的记录,插入到其他表或更新其他表
print '1 rows'
endinsert into tb select 1
select * from tb
--1行
go
create table tb_a([A_NAME] varchar(10),[A_SEX] varchar(10))
insert tb_a select '张三','男'
union all select '李四','女'
union all select '王五','男'
go
if object_id('tb_b') is not null drop table tb_b
go
create table tb_b([id] int,[b_count] int)
insert tb_b select 1,2
union all select 2,1
go
if object_id('tg') is not null drop trigger tg
go
create trigger tg on tb_a
for insert,update,delete
as
update b
set b.[b_count]=a.cnt
from tb_b b
join (select id=case [A_SEX] when '男' then 1 else 2 end,count([A_SEX]) cnt from tb_a group by [A_SEX]) a
on b.id=a.id
join (select id=case [A_SEX] when '男' then 1 else 2 end from inserted) c
on a.id=c.id
update b
set b.[b_count]=a.cnt
from tb_b b
join (select id=case [A_SEX] when '男' then 1 else 2 end,count([A_SEX]) cnt from tb_a group by [A_SEX]) a
on b.id=a.id
join (select id=case [A_SEX] when '男' then 1 else 2 end from deleted) c
on a.id=c.id
go
-->开始测试并查询
insert tb_a select '赵六','男'
select * from tb_b
/*
id b_count
----------- -----------
1 3
2 1(2 行受影响)
*/
update tb_a set [A_SEX]='女' where [A_NAME]='赵六'
select * from tb_b
/*
id b_count
----------- -----------
1 2
2 2(2 行受影响)
*/
delete tb_a where [A_NAME]='赵六'
select * from tb_b
/*
id b_count
----------- -----------
1 2
2 1(2 行受影响)
*/
insert into C values(22,'BBB')
insert into C values(33,'CCC')
---------------------------------
insert into a values(100,'xxx')
insert into a values(200,'yyy')
insert into a values(300,'zzz') (其初表a,表b为空)
结果如下:
select * from a --------结果为空
select * from b --------结果如下(为什么会这样????)
11 AAA
22 BBB
33 CCC
11 AAA
22 BBB
33 CCC
11 AAA
22 BBB
33 CCC
select * from c ------结果如下(最初值,即表c一开始的值)
11 AAA
22 BBB
33 CCC
after -->亡羊補牢
insert into a values(100,'xxx')
insert into a values(200,'yyy')
insert into a values(300,'zzz')
因为触发器是INSTEAD OF,所以将不会对A执行 INSERT操作,
而只执行触发器里面的SQL语句。
向B中插入C中的所有数据
对表做INSERT 或UPDATE或DELETE语句 不执行! SQL语句+触发器执行步骤
AFTER SQL语句 ->触发器
instead of 触发器
11 AAA
22 BBB
33 CCC
11 AAA
22 BBB
33 CCC
11 AAA
22 BBB
33 CCC 表B为什么有这么多行。怎么不是前面三行?????
我只是将表C的值插入表B。
表C只有三行啊。请指教!!!
insert into C values(22,'BBB')
insert into C values(33,'CCC')
---------------------------------
insert into a values(100,'xxx')
insert into a values(200,'yyy')
insert into a values(300,'zzz') 你先向C表插入了三条数据,然后向a表插入数据,此时每向a 插入一条触发一次,b表就是9条记录了。
首先A表,B表都为空。
C表的值为:
11 AAA
22 BBB
33 CCC
下面这个触发器是将表C的值插入表B。
create trigger test on a
instead of insert
as
begin
insert into b
select * from c
end那插入后表B的值为什么会变下面这样。搞不明白。请教大家。
select * from b --------结果如下(为什么会这样????)
11 AAA
22 BBB
33 CCC
11 AAA
22 BBB
33 CCC
11 AAA
22 BBB
33 CCC
表B为什么有这么多行。怎么不是前面三行?????
我只是将表C的值插入表B。
表C只有三行啊。请指教!!!
触发器就执行一次
表B 就多3行你一共向表A插入了三次数据
表B就多了9行
create trigger test on a
instead of insert
as
begin
insert into b
select * from c
end 改成这样试试:
create trigger test on a
instead of insert
as
begin
insert into b
select * from inserted
end
我借鉴他们的实践了一下哦!不错啊!都对。。、
create table a(id int ,a varchar(10))
create table b(id int ,b varchar(10))
create table c(id int ,b varchar(10)) insert into a values(1,'aaa') insert into a values(2,'aaabbb') insert into C values(11,'AAA')
insert into C values(22,'BBB')
insert into C values(33,'CCC') insert into a values(100,'xxx')
insert into a values(200,'yyy')
insert into a values(300,'zzz')
select * from a
select * from b
select * from cdelete from b where b='Test'
delete from c
delete from a
--测试instead of触发器是否是只执行触发器里面的SQL语句,不会对A执行 INSERT操作
create trigger test
on a
instead of insert
as
begin
insert into b select * from inserted
end
--测试after触发器是否是执行向表C追加完记录后,再更新表B中ID为3的记录
alter trigger TestCafter
on c
after insert
as
begin
update b set b='Test' from b where id='33'
end
--测试after触发器是否是执行向表C追加完记录后,再向表a追加当前向表C追加的记录
create trigger cInsert
on c
after insert
as
begin
insert into a select * from inserted
end
--测试inserted是否是指当前追加的记录
create trigger testC_tbInserted
on c
instead of insert
as[color=#339966]
begin
insert into c select * from inserted
insert into a select * from inserted
end
--删除触发器
drop trigger cInsert
drop trigger test
drop trigger testC_tbInserted[/color]
instead of 却能建立在视图上