mysql - 触发器中的条件插入

Question

这个问题已经有答案了:

Delimiters in MySQL (4 个回答)

已关闭 9 年前。

我在使用条件插入语句实现触发器时遇到一些问题。我尝试过的无需条件语句即可工作的代码是

CREATE TRIGGER TR_BI_USERS after insert on USERS for each row 
  insert into PERMISSION(per_id,role_id,page_id,allow_y_n,created_by) 
  select d.user_id,d.role_id,c.page_id,'Y',d.created_by 
  from USERS as d cross join resource c 
  where d.user_id=new.user_id and d.role_id=1;

但是，当我尝试带有条件语句的类似触发器时，我收到错误。我尝试的代码如下所示

CREATE TRIGGER TR_BI_USERS after insert on tourism_web.USERS for each row 
  begin 
    if new.role_id=1 then 
      insert into PERMISSION(per_id,role_id,page_id,allow_y_n,created_by) 
      select d.user_id,d.role_id,c.page_id,'Y',d.created_by 
      from USERS as d cross join resource c 
      where d.user_id=new.user_id; 
    else 
      insert into PERMISSION(per_id,role_id,page_id,allow_y_n,created_by) 
      select d.user_id,d.role_id,c.page_id,'N',d.created_by 
      from USERS as d cross join resource c 
      where d.user_id=new.user_id;
    end if;
  end;

请建议我应该在代码的后面部分修改哪些内容才能使其正常工作。谢谢。

Answer 1

当您编写包含多个语句的触发器或过程时，需要首先更改查询分隔符:

DELIMITER //
CREATE TRIGGER TR_BI_USERS after insert on tourism_web.USERS for each row 
  begin 
    if new.role_id=1 then 
      insert into PERMISSION(per_id,role_id,page_id,allow_y_n,created_by) 
      select d.user_id,d.role_id,c.page_id,'Y',d.created_by 
      from USERS as d cross join resource c 
      where d.user_id=new.user_id; 
    else 
      insert into PERMISSION(per_id,role_id,page_id,allow_y_n,created_by) 
      select d.user_id,d.role_id,c.page_id,'N',d.created_by 
      from USERS as d cross join resource c 
      where d.user_id=new.user_id;
    end if;
  end;
DELIMITER ;