Unable to update table after creating an After update trigger

Question

I have a table like this

/*Table: pageprivilege*/
------------------------

/*Column Information*/
----------------------

FIELD   TYPE              COLLATION          NULL    KEY     DEFAULT  Extra           PRIVILEGES                       COMMENT
------  ----------------  -----------------  ------  ------  -------  --------------  -------------------------------  -------
id      INT(10) UNSIGNED  (NULL)             NO      PRI     (NULL)   AUTO_INCREMENT  SELECT,INSERT,UPDATE,REFERENCES         
pageid  VARCHAR(20)       latin1_swedish_ci  YES             (NULL)                   SELECT,INSERT,UPDATE,REFERENCES         
roleid  TINYINT(4)        (NULL)             YES             (NULL)                   SELECT,INSERT,UPDATE,REFERENCES         
ad      TINYINT(1)        (NULL)             YES             0                        SELECT,INSERT,UPDATE,REFERENCES         
ed      TINYINT(1)        (NULL)             YES             0                        SELECT,INSERT,UPDATE,REFERENCES         
dl      TINYINT(1)        (NULL)             YES             0                        SELECT,INSERT,UPDATE,REFERENCES         
rd      TINYINT(1)        (NULL)             YES             0                        SELECT,INSERT,UPDATE,REFERENCES         
st      TINYINT(1)        (NULL)             YES             0                        SELECT,INSERT,UPDATE,REFERENCES   

I have an condition that, if the rows are updated such that

ad, ed, dl, rd are all set as 0 then the value of st automatically gets changed to 0 too

For that I wrote a procedure

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE `smsdev`.`update_priv`()
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
    BEGIN

     UPDATE pageprivilege SET st=0 WHERE ad = 0 AND ed =0 AND dl =0 AND rd = 0;

    END$$

DELIMITER ;  

And added a trigger which gets called after the table has been updated and calls the procedure like this

DELIMITER $$

USE `smsdev`$$

DROP TRIGGER /*!50032 IF EXISTS */ `pageprivilege_update`$$

CREATE
    /*!50017 DEFINER = 'smsdev'@'%' */
    TRIGGER `pageprivilege_update` AFTER UPDATE ON `pageprivilege` 
    FOR EACH ROW BEGIN
     CALL update_priv();
END;
$$

DELIMITER ;

The issue is whenever I make any update to the any of the 4 field, ad, ed, dl, rd I get this error

Can't update table 'pageprivilege' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

I am new to using trigger and stored procedure, I did read that is a bad idea to fire procedure from inside trigger, but this is just like a test of idea for me so if you could just bear with me and help me with this hack-ish code it would be gr8.

Answer 1

Thats correct you can not update the same table where the trigger is getting executed, however if you use before update you can set the st to 0 by checking the conditions something as

DELIMITER $$

USE `smsdev`$$

DROP TRIGGER /*!50032 IF EXISTS */ `pageprivilege_update`$$

CREATE
    /*!50017 DEFINER = 'smsdev'@'%' */
    TRIGGER `pageprivilege_update` BEFORE UPDATE ON `pageprivilege` 
    FOR EACH ROW 
    BEGIN
     if (new.ad = 0 AND new.ed =0 AND new.dl =0 AND new.rd = 0) then
       set new.st=0 ;
     end if;
END;
$$

DELIMITER ;