Conditional insert in trigger

Question

I am having some problem in implementing trigger with conditional insert statements. The piece of code I had tried which works without conditional statement is

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;

However when I try the similar looking trigger with conditional statement, I am getting errors. The code I tried looks like this

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;

Please suggest me what should I ammend in the later part of code to make it work. Thanks.

"I am getting errors". You should include the errors in your question. — Patrick Q, Jun 23 '14 at 19:24
Did you put a `DELIMITER` statement before this? If not, the first `;` will end the entire `CREATE TRIGGER` statement, in the middle of the `BEGIN` and `IF` blocks. — Barmar, Jun 23 '14 at 19:28
Thanks a lot @Barmar for giving the tip on delimiter. I tried with defining delimiter and now my trigger is working great. Thanks once again. :) — sam, Jun 24 '14 at 16:48

score 0 · Answer 1 · answered Jun 24 '14 at 16:52

When you write a trigger or procedure that contains multiple statements, you need to change the query delimiter first:

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 ;

Conditional insert in trigger

1 Answers1