5

I have a query like following:

delimiter $$
DROP TRIGGER IF EXISTS TR_SCIN_BANK_UPD$$
CREATE TRIGGER TR_SCIN_BANK_UPD
AFTER UPDATE ON SCIN_BANK
FOR EACH ROW
BEGIN
  IF OLD.BANK_NAME != NEW.BANK_NAME THEN
    INSERT into SCIN_BANK_LOG SET BANK_ID=OLD.BANK_ID, BANK_NAME=OLD.BANK_NAME, LAST_UPD_USER_ID=OLD.LAST_UPD_USER_ID, LAST_UPD_TS=now();
  END IF;
  IF OLD.BANK_DESC != NEW.BANK_DESC THEN
    INSERT into SCIN_BANK_LOG SET BANK_ID=OLD.BANK_ID, BANK_DESC=OLD.BANK_DESC, LAST_UPD_USER_ID=OLD.LAST_UPD_USER_ID, LAST_UPD_TS=now();
  END IF;
END$$

when executing I get

This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'

this error can any one provide solution for this?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502

3 Answers3

8

I guess there already is an AFTER UPDATE trigger on that table, but it is not named TR_SCIN_BANK_UPD, meaning that your DROP TRIGGER IF EXISTS line does nothing.

Tomalak
  • 332,285
  • 67
  • 532
  • 628
1

Upgrading Mysql to version 5.6.27 solved it for me:

Server version: 5.6.27-0ubuntu0.14.04.1 (Ubuntu)

shacharsol
  • 2,326
  • 20
  • 14
0

yes mysql dont support this if you use same event with different trigger name and at same time.

better, for this you should use if condition here : like below :

delimiter $$
create trigger trigger_name
AFTER insert
on t1
for each row
begin
  if new.col1 like '%Sabcd%'
  then
    update t2 inner join t3 on t2.col1=t1.col1
    set t2.col2=0 ;
  end if;
  if new.col1 like '%dcba%'
  then
    update t2 inner join t3 on t2.col1=t1.col1
    set t2.col2=02;
  end if;
end;
$$
simplifiedDB
  • 164
  • 9