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

菜鸟问题 求一段触发器

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)
回复
a1n1 2003-05-19
我的写错了,学习tj_dns(愉快的登山者)
回复
a1n1 2003-05-19
错了错了
回复
修改jwh_num或zzq_num后,将表一和表二的对应关系断了;
无法再进行表一的修改了。

若想达到目的:
1。建立修改对照表;
或:
2。建立外键关联,设置集联修改和删除;
3。建议ID为CHAR(8)型。
回复
a1n1 2003-05-19
create trigger tr1
on 资料表
for update
as
if (update 编号参数表.jwh_num ...)
begin
update 资料表 set id =....
end
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2003-05-19 09:57
社区公告
暂无公告