Solution for Can’t update table ‘t1′ in stored function/trigger because it is already used by statement which invoked this store

In recently project, i needed to fix a bug about database update. The best way to solve this problem is create an update trigger, but when finished this trigger desiger, a problem show up which the error description is "Can’t update table ‘t1′ in stored function/trigger because it is already used by statement which invoked this stored function/trigger.". So after searched the answer from website, i got a solution about this issue.The following is a sample to show how to fix this problem.

This is a sample table:

CREATE TABLE `t1`

( `a` char(1) default NULL, `b` smallint(6) default NULL );

insert into t1 values ('y','1');

I have a table t1 which has column a and b, i want column a to be updated "n" when column b=0.Here is the fist version i created:

DELIMITER |

CREATE TRIGGER trigger1 AFTER UPDATE ON t1 FOR EACH ROW UPDATE t1 SET a= 'n' WHERE b=0;

| DELIMITER ;

After executed this trigger succesfully, i got an error when i start to update this table.The error statement is "Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.".

The following trigger code is a good way:

drop trigger trigger1;

DELIMITER |

CREATE TRIGGER trigger1

BEFORE UPDATE ON t1

FOR EACH ROW

BEGIN IF NEW.b=0 THEN SET NEW.a = 'n';

END IF; END

| DELIMITER ;

Now you can update the table t1. After got this solution i use it on my project.This is the update trigger i wrote:

DELIMITER $$

DROP TRIGGER ModifyLastUpdated

create trigger ModifyLastUpdated BEFORE UPDATE on EMPLOYEE_SKILL for each row

BEGIN if

old.eid=new.eid and old.skill_id=new.skill_id

then set new.last_updated=current_timestamp;

end if;

END;

$$ DELIMITER ;