写一个触发器---在线等待………………………………

jealousme 2003-08-21 08:30:58

有2各表table1 table2当向table2中插入一个记录时：
1》查找table1中的某个字段（table1.zd1）值,
2》查找table2中的该记录的某个字段（table2.zd2）值,
3》比较2个值的大小
如果table1.zd1(dec)>=table2.zd2(dec)
那么提取table2中的该条记录的字段table2.zd3(varchar)和table2.4(int),

二者字符串相加变成成一个新值存入另外一个字段table2.zd5(varchar);
同时table2.zd4+1(数值)
否则将字段table2.zd6进行和 table2.zd5同样的变化，只是table2.zd4不变.

结束！---好难。。。。。。。。

...全文

72 10 打赏收藏转发到动态举报

写回复

用AI写文章

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

happy_0325 2003-08-21

打赏
举报

用case when

pengdali 2003-08-21

打赏
举报

CREATE TRIGGER 名 on table2
INSTEAD OF INSERT
AS
insert table2 (zd0,zd2,zd5,zd6,zd4,zd3) select a.zd0,a.zd2,
case when b.zd1>=a.zd2 then a.zd3+cast(a.zd4 as varchar(10)) end,
case when b.zd1<a.zd2 then a.zd3+cast(a.zd4 as varchar(10)) end,
case when b.zd1>=a.zd2 then a.zd4+1 end,zd3
from inserted a join table1 b on a.zd0=b.zd0

lxf31 2003-08-21

打赏
举报

create trigger T_name on table2
for insert
as
select table1.zd1 from table1 where table1.z0= table2.z0
go
select table2.zd2 from table2 where table2.z0=table1.z0
go

if (select table1.zd1 from table1 where table1.z0=table2.z0)>(select table2.zd2 from table2 where table2.z0=table1.z0)
begin
update table2 set table2.zd5=(select table2.zd3+table2.zd4 from table1,table2 where table2.z0=table1.z0)
where table1.zd1=table2.zd2
update table2 set table2.zd4=table2.zd4+1
else
update table2 set table2.zd6=(select table2.zd3+table2.zd4 from table1,table2 where table2.z0=table1.z0)
where table1.zd1=table2.zd2
end
go

靠！写的好费劲，还不知道对不对。
兄弟就这点思路了

jealousme 2003-08-21

打赏
举报

哈哈，i have a try and latter see you .

3x

这个东西完整吗？？

cuiyonggang007 2003-08-21

打赏
举报

在插入触发器中定义游标

lifeforu 2003-08-21

打赏
举报

create trigger on table2 for insert
as
if (select zd1 from table1 where zd0=(select zd0 from inserted))>=(select zd2 from inserted)
update table2 set zd5=zd3+cast(zd4 as varchar),zd4=zd4+1
where zd0 = (select zd0 from inserted)
else
update table2 set zd6=zd3+cast(zd4 as varchar)
where zd0 = (select zd0 from inserted)

jealousme 2003-08-21