Mysql – How to disable triggers from nesting/recursing when accessing the same table

MySQLtrigger

So I have this data

+----+----------+-------+---------------+----------------+------------+--------+
| id | date     | accid | accid_currbal | Action         | Desc       | Amount |
| 1  | 05/06/17 | 1     | 7777          | Create Account | some desc… | 7777   |
| 2  | 05/06/17 | 1     | 32091         | Add Funds      | some desc… | 24324  |
| 3  | 05/06/17 | 1     | 32100         | Add Funds      | some desc… | 9      |
| 4  | 05/06/17 | 1     | 32600         | Add Funds      | some desc… | 500    |
+----+----------+-------+---------------+----------------+------------+--------+

And an BEFORE_UPDATE trigger that accesses this table when it fires

CREATE ..... TRIGGER 
`db_moneytracker`.`transactions_BEFORE_UPDATE` BEFORE UPDATE ON 
`transactions` FOR EACH ROW
BEGIN
DECLARE adj INT;

if (old.amount <> new.amount && old.accid = new.accid) then
set adj = new.amount - old.amount;

update transactions set accid_curr_accbalance = accid_curr_accbalance + adj 
where accid = new.accid and `date` >= new.date;

elseif (old.accid <> new.accid) then
SIGNAL SQLSTATE '45000'
                SET MESSAGE_TEXT = 'IDs unchangeable';
end if;
END

But produces an error when updated

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

I think I understood what it meant so… I want the trigger not to fire the UPDATE is fired by THIS trigger.

Best Answer

You don't need an update statement:

In a "before update" trigger, you can directly change any of the values in the new row.

if ( old.amount <> new.amount && old.accid = new.accid ) then
   set adj = new.amount - old.amount ; 
   set new.accid_curr_accbalance = new.accid_curr_accbalance + adj ; 
else
   . . .

Related Solutions

Accessing ORA_ROWSCN in the triggers body

It doesn't seem to be possible (even if Oracle itself populates the value before launching BEFORE UPDATE trigger) - referring to :NEW.ora_rowscn in the trigger body generates error, select ora_rowscn from table1 in the trigger causes mutating trigger error.

Mysql – How to insert into junction table using triggers

You have to think in terms of the design.

After triggers on the Song table cannot help populate the SongArtist table since Song and Artist have no immediate relationship.

Although you should not need this, you could have before triggers on SongArtist to check for validity of SongID and ArtistID.

DELIMITER $$
CREATE TRIGGER before_songartist_insert
BEFORE INSERT ON SongArtist
FOR EACH ROW 
BEGIN
    DECLARE song_ok,artist_ok,sum_ok INT;
    SELECT COUNT(1) song_ok   FROM Song WHERE songID = NEW.songID;
    SELECT COUNT(1) artist_ok FROM Artist WHERE artistID = NEW.artistID;
    SET sum = song_ok + artist_ok;
    IF sum_ok < 2 THEN
        SELECT COUNT(1) INTO sum_ok FROM information_schema.dummy;
    END IF;
END; $$
CREATE TRIGGER before_songartist_insert
BEFORE UPDATE ON SongArtist
FOR EACH ROW 
BEGIN
    DECLARE song_ok,artist_ok,sum_ok INT;
    SELECT COUNT(1) song_ok   FROM Song WHERE songID = NEW.songID;
    SELECT COUNT(1) artist_ok FROM Artist WHERE artistID = NEW.artistID;
    SET sum = song_ok + artist_ok;
    IF sum_ok < 2 THEN
        SELECT COUNT(1) INTO sum_ok FROM information_schema.dummy;
    END IF;
END; $$
DELIMITER ;

In reality, the ON DELETE CASCADE and ON UPDATE CASCADE clauses should make using triggers unnecessary. You should insert rows into the Song and Artist as a single transaction. Then, using songIDs and artistIDs from the completed transaction, you can insert them into SongArtist and let any defined CONSTRAINT check the validity.

Best Answer

Related Solutions

Accessing ORA_ROWSCN in the triggers body

Mysql – How to insert into junction table using triggers

Related Question