MySQL Bugs: #14887: Allow Insert Trigger to call simple UPDATE on same table trigger is based

Bug #14887	Allow Insert Trigger to call simple UPDATE on same table trigger is based
Submitted:	11 Nov 2005 21:33	Modified:	15 Dec 2010 10:33
Reporter:	Kirk Smith	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	5.0.15-standard-log	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
Currently I can't have an insert trigger (before or after) that updates a column of data in the same table.  I am able to do this with an UPDATE trigger, but not on an insert.

The goal of the trigger is to keep a "flag" updated in the table based on data that is added or changed.  When the trigger runs, it would need to remove the flag from a different row(s), before adding it to the currently inserted row.

The update trigger I have below works as expected, but the insert one does not (#1442 error).

I know the current limitation is to not allow insert triggers to alter the current table, but in this instance there should be a safe way to do it.  Especially since the update trigger works (as long as you purposely avoid altering the row that the trigger is operating on).

Delimiter is //

The Insert Trigger I would like to work would be:
CREATE TRIGGER Regression_Data_bi BEFORE INSERT on Regression_Data 
  FOR EACH ROW 
  BEGIN 
  DECLARE TS TIMESTAMP;
    IF is_Latest_Result(NEW.Regression_Run,NEW.TestArea,NEW.TestCase,NEW.Result_TS) THEN
       SET NEW.Latest=1;
    ELSE
       SET NEW.Latest=0;
    END IF;
  END//

My Update Trigger is:

CREATE TRIGGER Regression_Data_bu BEFORE UPDATE on Regression_Data 
  FOR EACH ROW 
  BEGIN 
    DECLARE TS TIMESTAMP;
      IF is_Latest_Result (OLD.Regression_Run, OLD.TestArea, OLD.TestCase,
NEW.Result_TS) THEN
         SELECT Result_TS into TS from Regression_Data 
             where Regression_Run=OLD.Regression_Run 
             AND TestArea=OLD.TestArea 
             AND TestCase=OLD.TestCase 
             AND Latest=1;
         IF TS <> NEW.Result_TS THEN
            call clear_Latest_Result_Flag (OLD.Regression_Run, OLD.TestArea, OLD.TestCase);
         END IF;
         SET NEW.Latest=1;
      END IF;
  END//

Associated stored procedures are:

Create PROCEDURE clear_Latest_Result_Flag 
   (Run Integer, tArea VARCHAR(255), tCase VARCHAR(255)) deterministic 
   BEGIN 
       UPDATE Regression_Data SET Latest='0' 
           WHERE Regression_Run=Run 
           AND TestArea=tArea 
           AND TestCase=tCase 
           AND Latest='1'; 
   END //

Create function is_Latest_Result 
   (Run Integer, tArea VARCHAR(255), tCase VARCHAR(255), newTS TIMESTAMP ) returns BOOLEAN deterministic 
   BEGIN 
      DECLARE RES BOOLEAN DEFAULT FALSE; 
      DECLARE TS TIMESTAMP; 
      SET TS = Latest_TS(Run,tArea,tCase); 
      IF (newTS >= TS) THEN 
          SET RES = TRUE;  
      END IF; return RES; 
   END //

My table look like:

CREATE TABLE `Regression_Data` (
  `TestArea` varchar(245) NOT NULL default '',
  `TestCase` varchar(245) NOT NULL default '',
  `Result` enum('UNKNOWN','PASS','FAIL','UNEXPECTED_PASS','EXPECTED_FAIL','CORE_DUMP','TIMED_OUT','UNTESTED','UNKNOWN_FAIL','RUNNING') NOT NULL default 'UNKNOWN',
  `Result_TS` datetime NOT NULL default '0000-00-00 00:00:00',
  `Latest` int(1) NOT NULL default '0',
  PRIMARY KEY  (`Regression_Run`,`Result_TS`,`TestArea`,`TestCase`),
  KEY `Latest` (`Regression_Run`,`Latest`,`Result_TS`,`TestArea`,`TestCase`,`Result`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

How to repeat:
Create the trigger listed above.
insert into the table a couple of times, where the Result_TS is now() (to force the Latest flag to be updated.

Suggested fix:
Allow simple  UPDATE on the same table when the WHERE clause will avoid altering the current INSERT (or just completed INSERT if its an AFTER INSERT trigger).

Possible work around is to have AFTER INSERT trigger already released the table lock so that an UPDATE can be done to the same table.

This is still a problem in 5.0.62.

http://dev.mysql.com/doc/refman/5.0/en/stored-program-restrictions.html says:

- Within a stored function or trigger, it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger. 

I assume this is caused by the table lock and a naivety about who is asking to perform the modification while the table is locked.  Couldn't this be resolved by making the currently locked table (the source of the trigger) available with a special table name (eg. self, target, source, this_trigger_table)?  But if that could be done I would imagine it wouldn't be much more complicated to allow the trigger's table name to be used.

My illustration of the problem is a bit simpler:

CREATE TABLE `user_affiliate` (
  `user_id` int(10) unsigned NOT NULL,
  `affiliate_id` int(10) unsigned NOT NULL,
  `created` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `expired` timestamp NULL default NULL,
  PRIMARY KEY  (`id`),
) ENGINE=InnoDB COMMENT='store single affiliate for user during time range'

create trigger user_affiliate_expire 
  before insert on user_affiliate for each row 
  update user_affiliate ua
    set ua.expired=DATE_SUB(NEW.created, INTERVAL 1 SECOND) 
    where ua.expired is null and ua.user_id=NEW.user_id;

insert into user_affiliate (user_id,avc_user_id,created)
values (1,2000,now());

ERROR 1442 (HY000): Can't update table 'user_affiliate' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

I can understand blocking an INSERT trigger from calling an UPDATE that has a trigger that executes an INSERT, but because the trigger and modification operations differ here, I expect this to work.

Let's call this a duplicate (special case) of Bug #29542.