查看: 28422|回复: 22

[精华] 从实际出发--一个触发器问题的解决

[复制链接]
论坛徽章:
1
优秀写手
日期:2013-12-18 09:29:09
跳转到指定楼层
1#
发表于 2013-9-24 05:32 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式
本帖最后由 lyzhanhun 于 2013-9-29 12:44 编辑

实际应用碰到的问题,觉得有点意思发来看看

抽象后
表定义
create table aa (id int, ID1 VARCHAR);

数据
insert into aa values(1, 'aa');
insert into aa values(2, 'aab');
insert into aa values(3, 'aabcc');
insert into aa values(4, 'aabdd');
insert into aa values(5, 'aabddcc');

删除语句
固定格式为 delete from aa where id = ?

触发目的
删除某一行时, 级联删除id1 以当前删除行id1开头的行, 如 删除 第一行时,后面数据都被删除 ,因为它们都以'aa'开头

原触发器
CREATE OR REPLACE  TRIGGER TGAA
AFTER DELETE ON  AA
FOR EACH ROW
begin
delete from aa where id1 like :old.id1 + '%';
end;

问题, Dm中触发器触发的语句暂时会触发触发器, 而打上删除标记的条目再被删除时会报错, 如删除 1时, 数据2 3 4 5会被触发删除, 而数据2 删除时 会触发 3 4 5 删除, 3 4 5 会被打上删除标记, 完成后 回到删除1 触发的删除, 然后删除数据 3时, 会报错,这里是Bug 或者策略,暂不关注
现需要替换该触发器 完成原触发器本来的意图 写了个, 想知道有没有更简洁的办法

CREATE OR REPLACE  TRIGGER TGAA
AFTER DELETE ON  AA
FOR EACH ROW
declare
c  cursor is select id1 from aa where id != :old.id and id1 like :old.id1 + '%' order by id1 asc for update;
type tmp_id1 is table of varchar index by int;
v  tmp_id1;
k  varchar;
i  int;
n  int;
del_flag bool;
BEGIN
   i = 0;
   del_flag = TRUE;
   open c;
   loop
   fetch c into k;
   if c%notfound then
      exit;
   end if;
   
   if i = 0 or k != v(i) THEN
       v(i + 1) = k;
       i = i + 1;
   end if;
   
   for n in 1 .. i - 1 loop
       if INSTR(k, v(n)) = 1 THEN
           del_flag = FALSE;
           exit;
       end if;
   end loop;
   
   if del_flag then
    delete from aa where current of c;
   end if;
   end loop;
   close c;
END;


认证徽章
论坛徽章:
43
现任管理团队成员
日期:2011-05-07 01:45:08ITPUB元老
日期:2012-09-12 14:50:28版主5段
日期:2014-06-11 02:21:31阿斯顿马丁
日期:2013-11-19 10:38:16祖母绿
日期:2012-11-06 12:43:12路虎
日期:2013-11-20 11:37:53雪佛兰
日期:2013-09-05 13:28:25ITPUB9周年纪念徽章
日期:2010-10-08 09:28:512015中国数据库技术大会纪念徽章
日期:2015-04-24 16:04:24秀才
日期:2015-05-26 13:32:07
2#
发表于 2013-9-24 11:48 | 只看该作者
本帖最后由 myth8860 于 2013-9-24 17:01 编辑

元组级触发器体中的 SQL 语句修改触发语句的变异表。所谓变异表,就是当前被 DML语句修改的表。
对触发器来说,变异表就是创建该触发器的表。
--这个是dm6手册上说的,dm7我还没有找到变异表的说明。

你这个触发器写的很牛!

使用道具 举报

回复
论坛徽章:
0
3#
发表于 2013-9-24 12:00
UP

认证徽章
论坛徽章:
43
现任管理团队成员
日期:2011-05-07 01:45:08ITPUB元老
日期:2012-09-12 14:50:28版主5段
日期:2014-06-11 02:21:31阿斯顿马丁
日期:2013-11-19 10:38:16祖母绿
日期:2012-11-06 12:43:12路虎
日期:2013-11-20 11:37:53雪佛兰
日期:2013-09-05 13:28:25ITPUB9周年纪念徽章
日期:2010-10-08 09:28:512015中国数据库技术大会纪念徽章
日期:2015-04-24 16:04:24秀才
日期:2015-05-26 13:32:07
4#
发表于 2013-9-24 16:58 | 只看该作者
delete from aa where current of c;

这个也触发了这个表啊,估计是系统判定不会引起循环触发。

使用道具 举报

回复
论坛徽章:
6
2012新春纪念徽章
日期:2012-01-04 11:53:292013年新春福章
日期:2013-02-25 14:51:24林肯
日期:2013-09-12 15:57:33优秀写手
日期:2013-12-18 09:29:09慢羊羊
日期:2015-03-04 14:51:352015年新春福章
日期:2015-03-06 11:57:31
5#
发表于 2013-9-24 20:37 | 只看该作者
这个触发器确实挺有意思.

换个思路, 可以把这个语句改写为: delete from aa where id like ? || '%'

使用道具 举报

回复
认证徽章
论坛徽章:
43
现任管理团队成员
日期:2011-05-07 01:45:08ITPUB元老
日期:2012-09-12 14:50:28版主5段
日期:2014-06-11 02:21:31阿斯顿马丁
日期:2013-11-19 10:38:16祖母绿
日期:2012-11-06 12:43:12路虎
日期:2013-11-20 11:37:53雪佛兰
日期:2013-09-05 13:28:25ITPUB9周年纪念徽章
日期:2010-10-08 09:28:512015中国数据库技术大会纪念徽章
日期:2015-04-24 16:04:24秀才
日期:2015-05-26 13:32:07
6#
发表于 2013-9-25 10:08 | 只看该作者
建议楼主改个标题,我们推荐到首页上去。

使用道具 举报

回复
论坛徽章:
11
SQL极客
日期:2013-12-09 14:13:35SQL数据库编程大师
日期:2013-12-06 13:59:43SQL大赛参与纪念
日期:2013-12-06 14:03:45红孩儿
日期:2012-12-19 11:08:17优秀写手
日期:2013-12-18 09:29:09暖羊羊
日期:2015-04-22 14:41:41
7#
发表于 2013-9-26 15:49 | 只看该作者
很有意思啊,巧妙的利用 where current of 游标,来构造条件避免触发器的递归触发。

使用道具 举报

回复
论坛徽章:
4
2011新春纪念徽章
日期:2011-02-18 11:42:48奥迪
日期:2013-12-07 17:06:42优秀写手
日期:2013-12-18 09:29:09日产
日期:2014-01-15 14:18:32
8#
发表于 2013-9-27 15:02 | 只看该作者
这个触发器写的有点意思

使用道具 举报

回复
论坛徽章:
0
9#
发表于 2013-9-29 11:46 | 只看该作者

使用道具 举报

回复
认证徽章
论坛徽章:
169
SQL数据库编程大师
日期:2016-01-13 10:30:43SQL极客
日期:2013-12-09 14:13:35SQL大赛参与纪念
日期:2013-12-06 14:03:45最佳人气徽章
日期:2015-03-19 09:44:03现任管理团队成员
日期:2015-08-26 02:10:00秀才
日期:2015-07-28 09:12:12举人
日期:2015-07-13 15:30:15进士
日期:2015-07-28 09:12:58探花
日期:2015-07-28 09:12:58榜眼
日期:2015-08-18 09:48:03
10#
发表于 2013-9-29 12:16 | 只看该作者
!好文!

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表