xeqtr1982(ShaKa) ( ) 信誉：100 2006-04-05 14:39:00
create table a(a1 int,a2 int,a3 int)
create table b(b1 int,b2 int,b3 int)
gocreate trigger cfq on a
for insert,update
asif @@rowcount=0 return
declare @count_ins int
declare @count_del int
select @count_ins=count(*) from inserted
select @count_del=count(*) from deletedif(@count_ins>0 and @count_del>0)
begin
if update(a1)
update b set b1=a.a1 from inserted a,deleted c where b.b1=c.a1
if update(a2)
update b set b2=a.a2 from inserted a,deleted c where b.b2=c.a2
if update(a3)
update b set b3=a.a3 from inserted a,deleted c where b.b3=c.a3
endif(@count_ins>0 and @count_del=0)
begin
insert into b select * from inserted
end
goinsert into a select 1,2,3
insert into a select 3,4,5
insert into a select 6,7,8
go
select * from a
select * from bupdate a set a1=100 where a1=1
update a set a2=500 where a2=4
go
select * from a
select * from bdrop trigger cfq
drop table a
drop table b
问题if @@rowcount=0 return
declare @count_ins int
declare @count_del int
select @count_ins=count(*) from inserted
select @count_del=count(*) from deleted@是什么意思？declare是什么命令? 嗨！这断不懂哦！解释 一下下？
create table a(a1 int,a2 int,a3 int)
create table b(b1 int,b2 int,b3 int)
gocreate trigger cfq on a
for insert,update
asif @@rowcount=0 return
declare @count_ins int
declare @count_del int
select @count_ins=count(*) from inserted
select @count_del=count(*) from deletedif(@count_ins>0 and @count_del>0)
begin
if update(a1)
update b set b1=a.a1 from inserted a,deleted c where b.b1=c.a1
if update(a2)
update b set b2=a.a2 from inserted a,deleted c where b.b2=c.a2
if update(a3)
update b set b3=a.a3 from inserted a,deleted c where b.b3=c.a3
endif(@count_ins>0 and @count_del=0)
begin
insert into b select * from inserted
end
goinsert into a select 1,2,3
insert into a select 3,4,5
insert into a select 6,7,8
go
select * from a
select * from bupdate a set a1=100 where a1=1
update a set a2=500 where a2=4
go
select * from a
select * from bdrop trigger cfq
drop table a
drop table b
问题if @@rowcount=0 return
declare @count_ins int
declare @count_del int
select @count_ins=count(*) from inserted
select @count_del=count(*) from deleted@是什么意思？declare是什么命令? 嗨！这断不懂哦！解释 一下下？
@指的是变量，@@为全局变量
在批处理或过程的正文中用 DECLARE 语句声明变量，并用 SET 或 SELECT 语句给其指派值。
----------------------
是的
为！记录行数吧？理解！对比新的记录和老的记录？
那@count_ins>0 and @count_del>0 是不是就是证明新添加的记录里，在老记录里有重复？》
----------------------
行数@@rowcount是全局变量！
是由系统来维护的，每次批处理查询，更新，插入等后，都重置@@rowcount 的值
if(@count_ins>0 and @count_del>0)
-------
inserted 表和 deleted 表里都有数据 ---因此认为是update操作if(@count_ins>0 and @count_del=0)
-------
inserted 表有数据 deleted表没数据 ---因此认为是insert操作@@ROWCOUNT,sqlserver的系统函数
返回受上一语句影响的行数。
begin
if update(a1)
update b set b1=a.a1 from inserted a,deleted c where b.b1=c.a1
if update(a2)
update b set b2=a.a2 from inserted a,deleted c where b.b2=c.a2
if update(a3)
update b set b3=a.a3 from inserted a,deleted c where b.b3=c.a3
end表C 是那里来的？
c 是 deleted 表的别名(deleted c)-------------最后一次回答..
----@@rowcount 全局变量 返回上一次操作的数量declare @count_ins int
declare @count_del int
----用户定义变量select @count_ins=count(*) from inserted
----inserted 插入操作数据集select @count_del=count(*) from deleted
----deleted 被删除的数据集----inserted、deleted 往往用在触发器中参考联机帮助