Mysql – Unable to update table after creating an After update trigger

MySQLstored-procedurestrigger

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.

Best Answer

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 ;

Related Solutions

Mysql – Performance of a Trigger vs Stored Procedure in MySQL

For the sake of simplicity, triggers are the way to go for implementing any kind of tracking of database changes. However, you need to be aware of what happens under the hood when you use triggers.

According to MySQL Stored Procedure Programming, page 256 under the head "Trigger Overhead" says the following:

It is important to remember that, by necessity, triggers add overhead to the DML statement to which they apply. the actual amount of overhead will depend upon the nature of the trigger, but --- as all MySQL triggers execute FOR EACH ROW --- the overhead can rapidly accumulate for statements that process large numbers of rows. You should therefore avoid placing any expensive SQL statements or procedural code in triggers.

An expanded explanation of trigger overhead is given on pages 529-531. The conclulding point from that section states the following:

The lesson here is this: since the trigger code will execute once for every row affected by a DML statement, the trigger can easily become the most significant factor in DML performance. Code inside the trigger body needs to be as lightweight as possible and -- in particular -- any SQL statements in the trigger should be supported by indexes whenever possible.

Not mentioned in the book is another factor when using triggers: When it comes to audit logging, please be aware of what you log data into. I say this because should you choose to log to a MyISAM table, each INSERT into a MyISAM table produces a full table lock during the INSERT. This can become a serious bottleneck in a high-traffic, high-transaction environment. Additionally, if the trigger is against an InnoDB table and you log changes in MyISAM from within the trigger, this will secretly disabled ACID compliance (i.e., reduce block transactions to autocommit behavior), which is cannot be rolled back.

When using triggers on InnoDB tables and logging changes

The table you log to is also InnoDB
You have autocommit turned off
You setup START TRANSACTION...COMMIT/ROLLBACK blocks thoroughly

In this way, audit logs can benefit from COMMIT/ROLLBACK as would main tables.

Concerning using stored procedures, you would have to painstakingly call the stored procedure at every point of DML against the table being tracked. One could easily miss logging changes in the face of tens of thousands of lines of application code. Placing such code in a trigger eliminates finding all those DML statements.

CAVEAT

Depending on how complex the trigger is, it can still be a bottleneck. If you want to reduce bottlenecks in audit logging, there is something you can do. However, it will require a little infrastructure change.

Using commodity hardware, create two more DB Servers

Distribution master (DM) to relay audit info only
MySQL Server for Auditing (AU)

This will server to reduce write I/O on the main database (MD) due to audit logging. Here is how you can accomplish it:

Step 01) Turn on binary logging in the main database.

Step 02) Using an inexpensive server, setup MySQL (same version as MD) with binary logging enabled. This will be DM. Setup replication from MD to DM.

Step 03) Using a second inexpensive server, setup MySQL (same version as MD) with binary logging disabled. Setup each audit table to use --replicate-do-table. This will be AU. Setup replication from DM to AU.

Step 04) mysqldump the table structures from MD and load it into DM and AU.

Step 05) Convert all audit tables in MD to use the BLACKHOLE storage engine

Step 06) Convert all tables in DM and AU to use the BLACKHOLE storage engine

Step 07) Convert all audit tables in AU to use the MyISAM storage engine

When done

DM will replicate from MD and record stuff in its binary log only
With --replicate-do-table filter on all audit tables, AU will replicate from DM

What this does is store audit info on a separate DB server and also reduce any write I/O degradation that MD would normally have.

Mysql – find and insert row to another table using thesql trigger

This should do the trick for you:

DELIMITER $$
DROP TRIGGER IF EXISTS `employee_INSERT` $$
CREATE TRIGGER `employee_INSERT` 
AFTER INSERT ON `employee`
FOR EACH ROW
BEGIN
    INSERT INTO employee_tools (Id, Tool)
    SELECT new.Id, tools.Tool_Name 
            FROM tools
            WHERE tools.Division = new.Division;
END $$
DELIMITER ;

Best Answer

Related Solutions

Mysql – Performance of a Trigger vs Stored Procedure in MySQL

Mysql – find and insert row to another table using thesql trigger

Related Question