全部
基础类
应用实例
新技术前沿

菜鸟问题求一段触发器

LWWL 2003-05-19 09:57:44

两个表
1: jwh_name char 20,zzq_name char 20,lou_num char 2,menpai_num char 3,id int; 资料表
2: jwh_name char 20,jwh_num char 1,zzq_name char 20,zzq_num char 2; 编号参数表
我通过表2 生成的jwh_num,zzq_num 和表1的lou_num,menpai_num 得到id(长度是8位)
比如说 jwh_num=1,zzq_num=01,lou_num=04,menpai_num=304 得到id=10104304;
在表2里面写个触发器
当修改jwh_num或zzq_num时候表1的id 会自动更新
当删除的时候表1的id 前3位符合条件的都全部删除

...全文

8 点赞收藏 8

写回复

8 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

chenquan 2003-05-21

CREATE TRIGGER [insertname] ON [dbo].[Table2]
FOR update，delete
AS
declare @jwhid char,
@zzqid char
if exists(select * from inserted)
begin
declare Mycursor2 cursor for --定义游标
select jwh_id,zzq_id from inserted
open Mycursor2
fetch next from Mycursor2 into @jwhid,@zzqid
while @@fetch_status=0
begin
update t_jmjbzl set id=convert(numeric,@jwhid+@zzqid+isnull(louzhuang,'00')
+isnull(a.menpai,'000'))
where (@jwhid+@zzqid)=substring(convert(varchar,id),1,3)
fetch next from Mycursor2 into @jwhid,@zzqid
end
close Mycursor2
deallocate Mycursor2
end --表1：表2＝N:1
if exists(select * from deleted)
delete table1
From table1 a,deleted b
where a.jwh_name=b.jwh_name
and a.zzq_name=b.zzq_name
and b.jwh_num+b.zzq_num=substring(convert(varchar,a.id),1,3)

回复赞

chenquan 2003-05-19

CREATE TRIGGER [insertname] ON [dbo].[Table2]
FOR update，delete
AS
if exists(select * from inserted)
update table1 set id=convert(numeric,
isnull(b.jwh_num,'0') +isnull(b.lou_num,'00')+isnull(a.lou_num,'00')
+isnull(a.menpai_num,'000'))
From table1 a,inserted b
where a.jwh_name=b.jwh_name
and a.zzq_name=b.zzq_name
if exists(select * from deleted)
delete table1
From table1 a,deleted b
where a.jwh_name=b.jwh_name
and a.zzq_name=b.zzq_name
and b.jwh_num+b.zzq_num=substring(convert(varchar,a.id),1,3)

回复赞

chenquan 2003-05-19

呵呵，应该是我写的才对
CREATE TRIGGER [insertname] ON [dbo].[Table2]
FOR update
AS
update table1 set id=convert(numeric, isnull(b.jwh_num,'0')+isnull(b.lou_num,'00')+isnull(a.lou_num,'00')
+isnull(a.menpai_num,'000'))
From table1 a,inserted b
where a.jwh_name=b.jwh_name
and a.zzq_name=b.zzq_name
CREATE TRIGGER [insertname] ON [dbo].[Table2]
FOR delete
AS
delete table1
From table1 a,deleted b
where a.jwh_name=b.jwh_name
and a.zzq_name=b.zzq_name
and b.jwh_num+b.zzq_num=substring(convert(varchar,a.id),1,3)

回复赞

leigg 2003-05-19

CREATE TRIGGER [insertname] ON [dbo].[Table2]
FOR update
AS
update table1 set id=convert(numeric, isnull(b.jwh_num,'0')+isnull(b.lou_num,'00')+isnull(a.lou_num,'00')
+isnull(a.menpai_num,'000'))
From table1 a,inserted b
where a.jwh_name=b.jwh_name
and a.zzq_name=b.zzq_name
CREATE TRIGGER [insertname] ON [dbo].[Table2]
FOR delete
AS
delete table1
From table1 a,deleted b
where a.jwh_name=b.jwh_name
and a.zzq_name=b.zzq_name
and b.jwh_num+b.zzq_num=substring(convert(varchar,a.id),1,3)

回复赞