MySQL Trigger Error

[Garethp]

Okay, so I have a table test that is

 

----------------------------------

| ID        | Begin  | End    |

----------------------------------

| int(10) | int(10) | int(10) |

----------------------------------

 

With ID being an auto-increment. What I want to do is create a trigger to automatically set Begin to the current timestamp when inserted, and to set End to the timestamp at which the newest row is entered (so that only the most current row will have an End of 0, the others will have the timestamp that they were no longer the current row). Triggers at teh bottom. My problem being that I'm getting the error

 

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

 

Presumably because it doesn't want me to update test when the trigger is called from test. The problem is that it's very important to the final production of this to make sure that the integrity of the data is 100%, hence my putting them in triggers instead of doing it via PHP. Does anyone have any suggestions on how I can do this?

 

Before Insert

CREATE TRIGGER set_begin
BEFORE INSERT
ON test
FOR EACH ROW
BEGIN
SET NEW.Begin = NOW();
SET NEW.End = 0;
END;
|

 

After Insert

CREATE TRIGGER update_end 
AFTER INSERT
ON test
FOR EACH ROW
BEGIN
UPDATE test SET End = NOW() WHERE End = 0;
END;
|

[DavidAM]

mySql's implementation of triggers does NOT allow the trigger to touch the table that fired the trigger. Since triggers were invented to aid with data integrity, this is (in my opinion) a serious limitation.

 

The best solution, (again, my opinion) is to create a stored procedure or function that will update the "current" row and insert the new one.

 

Having said that, let me say that your triggers are messed up. Your AFTER insert is updating all rows where End = 0, this would update the row you just inserted as well. You would need to do that update BEFORE the insert. Also, there is the possibility that the NOW() value in BEFORE would be different than the NOW() value in AFTER. Without knowing the application, I don't know if this is significant or not. Actually, I'm not sure if NOW() changes during trigger/procedure execution, so that last statement might not be correct.

 

Also, watch out, END is (I believe) a reserved word. I would choose something different to prevent problems there (perhaps StartTIme and EndTIme).

 

Having said all that, I question whether that database design is optimal. Is it necessary to have an EndTime in a record? You could always get the current record by selecting for the MAX StartTime. You could determine the previous record by looking at the previous ID (not the most accepted way to do it) or the MAX StartTime that is LESS THAN the "current" record's StartTime.

[Garethp]

Thanks, I'll look at using a stored procedure. The reason why I'm using a start time and an end time is that it will eventually be used for a table that will hold a varying percentage for values on another table (so this will have a column percentage, which will be calculated against the column on the other table), and it needs to be applied during a certain time period (so past transactions will only use the percentage that was set at the time of it's transaction), and this is the easiest way for me to do it. I ruled out putting the percentage on the transaction row because I don't want to disturb that program at all (it was made by my predecessor, so it's a bit fragile). Thanks for your reply though!