菜鸟问题 求一段触发器

[LWWL]

两个表
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位符合条件的都全部删除

[a1n1]

create trigger tr1
on 资料表
for update
as
if (update 编号参数表.jwh_num ...)
begin
update 资料表 set id =....
end

[愉快的登山者]

修改jwh_num或zzq_num后，将表一和表二的对应关系断了；
无法再进行表一的修改了。

若想达到目的：
1。建立修改对照表；
或：
2。建立外键关联，设置集联修改和删除；
3。建议ID为CHAR（8）型。

[a1n1]

错了错了

[a1n1]

我的写错了，学习tj_dns(愉快的登山者)

[leigg]

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)

[chenquan]

呵呵，应该是我写的才对
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)

[chenquan]

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]

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)