Can't Update table in stored trigger, cause it's already used by statement which invoked this stored trigger

Question

I've got a problem with trigger in mysql:

CREATE TRIGGER trigger1 BEFORE INSERT ON test1

FOR EACH ROW 

BEGIN 

    **update test1 set  p=new.p where idtest=new.idtest;**

UPDATE kompo set kompo.s=kompo.s -1 where idk = new.idk;

IF (SELECT s - mini FROM kompo WHERE idk = NEW.idk) < 0
THEN 
UPDATE test2 SET kk=kk+1 WHERE idk=NEW.idk;    

END IF;
END$$

the bolded command causes the error : Can't Update table in stored trigger, cause it's already used by statement which invoked this stored trigger.

Have you got any idead how to update inserted value?

Thank you in advance.

Answer 1

Perhaps this does what you really want:

BEGIN 

    set new.p = new.p * 0.9;

    update kompo
        set kompo.s = kompo.s - 1
        where kompo.idk = new.idk;

    IF (SELECT s - mini FROM kompo WHERE kompo.idk = NEW.idk) < 0 THEN 
        UPDATE test2
            SET kk = kk+1
            WHERE test2.idk = NEW.idk;    

    END IF;
END;

If you intend for the first update to affect more than one row in the table, then you have a problem with your data model.