Create trigger tr_T2 on T2 for update as update t1 set State=1 from deleted d inner join t1 on d.Code=t1.Code
create trigger my_trig on t1 for delete as begin update t1 set state = ... from t1 , deleted d where t1.code = d.code end create trigger my_trig on t1 for delete as begin update t1 set state = ... from t1 , deleted d where t1.code = d.code and d.code = ... end
create trigger my_trig on t1 for delete as begin update t1 set state = ... from t1 , deleted d where t1.code = d.code end
再请问一下 T yid current state Max count hist nhist 2012001 8 0 2 3 15 -1添加的数据insert T (yid,state,hist) values(2012002 ,0,18)数据应为如下 yid current state Max count hist nhist 2012001 8 0 2 3 15 18 2012002 9 0 2 3 18 -1insert T (yid,state,hist) values(2012003 ,1,20) 数据应为如下 yid current state Max count hist nhist 2012001 8 0 2 3 15 18 2012002 9 0 2 3 18 20 2012003 0 1 9 4 20 -1 谢谢
create table T(yid varchar(10), [current] int, state int,[Max] int, [count] int, hist int,nhist int) insert into t values('2012001', 8, 0, 2, 3, 15, -1) go create trigger my_trig on t for insert as begin declare @yid as varchar(10) set @yid = (select yid from inserted) update t set nhist = (select top 1 hist from t where yid > m.yid order by yid) from t m where m.nhist = -1 update t set nhist = -1 where yid = @yid end goinsert into t(yid,[current],state,[Max], [count],hist) values('2012002', 9 ,0 ,2 ,3 ,18 ) select * From t /* yid current state Max count hist nhist ---------- ----------- ----------- ----------- ----------- ----------- ----------- 2012001 8 0 2 3 15 18 2012002 9 0 2 3 18 -1(所影响的行数为 2 行) */insert into t(yid,[current],state,[Max], [count],hist) values('2012003', 0, 1, 9, 4, 20 ) select * From t /* yid current state Max count hist nhist ---------- ----------- ----------- ----------- ----------- ----------- ----------- 2012001 8 0 2 3 15 18 2012002 9 0 2 3 18 20 2012003 0 1 9 4 20 -1(所影响的行数为 3 行) */drop table t
insert T (yid,state,hist) values(2012003 ,1,20) 只能确定三个值啊
如果state 为1 则 current 清空为0 否则 current 自增 max值是 当current重置为0时 则判断 current > max 为真则 max = current count 则表示统计的是state = 1次数
create trigger tri_T2_deleted for delete as begin update T set state=需要更新的值 from T1 t,Deleted d where t.code=d.code end
帮我看这个语句declare @count int,@c1 intselect @count = sum(case when iKey between 1000 and 1015 then 1 else 0 end),@c1 = sum(case when iKey between 2000 and 2015 then 1 else 0 end) from tb insert tb values(@count,@c1) 如果表格中没有这么多记录 @count 和 @c1为空 添加时出错 怎么判断是其值是空 为空是符值为0
for update
as
update t1
set State=1
from deleted d
inner join t1 on d.Code=t1.Code
as
begin
update t1 set state = ... from t1 , deleted d where t1.code = d.code
end
create trigger my_trig on t1 for delete
as
begin
update t1 set state = ... from t1 , deleted d where t1.code = d.code and d.code = ...
end
as
begin
update t1 set state = ... from t1 , deleted d where t1.code = d.code
end
yid current state Max count hist nhist
2012001 8 0 2 3 15 -1添加的数据insert T (yid,state,hist) values(2012002 ,0,18)数据应为如下
yid current state Max count hist nhist
2012001 8 0 2 3 15 18
2012002 9 0 2 3 18 -1insert T (yid,state,hist) values(2012003 ,1,20)
数据应为如下
yid current state Max count hist nhist
2012001 8 0 2 3 15 18
2012002 9 0 2 3 18 20
2012003 0 1 9 4 20 -1
谢谢
insert into t values('2012001', 8, 0, 2, 3, 15, -1)
go
create trigger my_trig on t for insert
as
begin
declare @yid as varchar(10)
set @yid = (select yid from inserted)
update t set nhist = (select top 1 hist from t where yid > m.yid order by yid) from t m where m.nhist = -1
update t set nhist = -1 where yid = @yid
end
goinsert into t(yid,[current],state,[Max], [count],hist) values('2012002', 9 ,0 ,2 ,3 ,18 )
select * From t
/*
yid current state Max count hist nhist
---------- ----------- ----------- ----------- ----------- ----------- -----------
2012001 8 0 2 3 15 18
2012002 9 0 2 3 18 -1(所影响的行数为 2 行)
*/insert into t(yid,[current],state,[Max], [count],hist) values('2012003', 0, 1, 9, 4, 20 )
select * From t
/*
yid current state Max count hist nhist
---------- ----------- ----------- ----------- ----------- ----------- -----------
2012001 8 0 2 3 15 18
2012002 9 0 2 3 18 20
2012003 0 1 9 4 20 -1(所影响的行数为 3 行)
*/drop table t
insert T (yid,state,hist) values(2012003 ,1,20)
只能确定三个值啊
2012001 8 0 2 3 15 18
2012002 9 0 2 3 18 20
2012003 0 1 9 4 20 -1那你中间那些值怎么来的?
8 0
9 0
0 1
create trigger tri_T2_deleted
for delete
as
begin
update T set state=需要更新的值
from T1 t,Deleted d where t.code=d.code
end
insert tb values(@count,@c1)
如果表格中没有这么多记录 @count 和 @c1为空 添加时出错 怎么判断是其值是空 为空是符值为0