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.