MySQL trigger: On UPDATE, DELETE old row if exists and cancel update -

i have table in want perform update. problem update changes primary keys , may produce error because of collision, eg: want update keys go 1-15 1-16, , 1-16 exists, error arises

i know how via php, need update if there no collision, , delete old row , cancel update if collision may occur. decided use trigger because see clean, eg: if want update 1-15 1-16, , 1-16 exists, delete 1-15 , abort update. trigger i'm trying create:

create trigger table_trigger before update on table each row begin     if ( select count(1) table item_id = new.item_id , related_id = new.related_id )          delete table item_id = old.item_id , related_id = old.related_id;         rollback transaction;     end if; end;// 

but giving mysql error:

sql error (1064): have error in sql syntax; check manual corresponds mysql server version right syntax use near '' @ line 5

which delete line. have not knowledge triggers, how can achieve i'm trying? on right way?

op's comment:

i use rollback transaction try abort update

as per documentation on triggers (even functions) can't use explicit commit/rollback. not permitted.

the trigger cannot use statements explicitly or implicitly begin or end transaction, such start transaction, commit, or rollback.

if don't want update not executed on condition matching, better throw error. won't let update happen.

example:

delimiter //  drop trigger if exists table_trigger //  create trigger table_trigger before update on table each row begin   declare rowcount int default 0;   declare error_message varchar(1024) default '';    select count(1) rowcount table    item_id = new.item_id      , related_id = new.related_id ;    if ( rowcount > 0 ) -- if( rowcount ) -- works     -- delete table      --   item_id = old.item_id      --     , related_id = old.related_id;     -- rollback transaction; -- not allowed      set error_message =          concat( error_message, 'update not allowed ' );     set error_message =          concat( error_message, 'combination \'item_id=' );     set error_message =          concat( error_message, new.item_id, '\' , ');     set error_message =          concat( error_message, '\'related_id=', new.related_id, '\'' );      -- throw error     -- update not allowed combination      -- 'itemid=1' , 'related_id=5'  ( example )     signal sqlstate 23000 set message_text = error_message;   end if; end;//  delimiter ;