如何使用触发器检查mysql中的数据行？

发布于 2024-09-14 21:12:11 字数 618 浏览 2 评论 0原文

我想确定我的数据表中没有孩子的孩子。因此，如果我有一个父项 A 和一个子项 B (B.parent = A)，并且我尝试将子项 C 插入到项 B (C.parent = B) 中，则此触发器必须阻止它并且将 C 的parent_id 设置为A (C.parent = A)。我的表中只需要 2 个级别（父子），并且不需要爷爷。

这是我的示例，不起作用：

DELIMITER //
CREATE TRIGGER parent_control BEFORE insert ON reports
FOR EACH ROW BEGIN
IF new.parent_id is not null THEN
  set @parent_parent_id = new.parent_id;
  SELECT parent_id FROM reports INTO parent_parent_id WHERE report_id = new.parent_id;
  IF @parent_parent_id is not null THEN
    SET new.parent_id = @parent_parent_id;
  END IF;
END IF;
END;

它说：#1327 - 未声明的变量：parent_parent_id

原文

I want to be sure, that there are no children of children in my datatable.
So if I have a parent item A, and a child item B (B.parent = A), and I try to insert a child item C to the item B (C.parent = B), this trigger have to prevent it and set the parent_id of C to A (C.parent = A). I need only 2 levels in my table (parent-child), and no grandpas.

There is my sample, that doesn't work:

DELIMITER //
CREATE TRIGGER parent_control BEFORE insert ON reports
FOR EACH ROW BEGIN
IF new.parent_id is not null THEN
  set @parent_parent_id = new.parent_id;
  SELECT parent_id FROM reports INTO parent_parent_id WHERE report_id = new.parent_id;
  IF @parent_parent_id is not null THEN
    SET new.parent_id = @parent_parent_id;
  END IF;
END IF;
END;

It says: #1327 - Undeclared variable: parent_parent_id

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

醉酒的小男人 2024-09-21 21:12:11

可能想检查字段名称和数据类型等，但这样的东西可能会起作用

delimiter #

create trigger reports_before_ins_trig before insert on reports
for each row
proc_main:begin

declare pid smallint unsigned default null;

  if new.parent_id is null then
    leave proc_main;
  end if;

  select parent_id into pid from reports where report_id = new.parent_id;

  if pid is not null then 
    set new.parent_id = pid;
  end if;

end proc_main #

delimiter ;

might want to check field names and datatypes etc but something like this might work

delimiter #

create trigger reports_before_ins_trig before insert on reports
for each row
proc_main:begin

declare pid smallint unsigned default null;

  if new.parent_id is null then
    leave proc_main;
  end if;

  select parent_id into pid from reports where report_id = new.parent_id;

  if pid is not null then 
    set new.parent_id = pid;
  end if;

end proc_main #

delimiter ;

回复收藏 0 原文

~没有更多了~