Forum Home
Press F1
 
Thread ID: 102265 2009-08-13 13:01:00 mysql help WarNox (8772) Press F1
Post ID Timestamp Content User
800805 2009-08-13 13:01:00 Hey!

Basically I'm trying to create a log table, which I don't want to get too big. I've decided to limit the table to 1000 rows and to do this using a trigger.

I'm trying to create a 'before insert' trigger that updates the same table.

Something like


CREATE TRIGGER test BEFORE INSERT TO log_table
IF a > 5 THEN
UPDATE/INSERT/DELETE log_table.....
END

Now, no matter if I do a UPDATE/INSERT/DELETE, if it's done on the same table as the trigger is set to I get the following error:


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

I did some research and found that mysql does have issues with this, but I only thought it was to prevent recursive calls. How is doing a delete/update after an insert statement going to create recursion.

Is there a fix for this?

Thanks,



Gregor 		WarNox (8772)
800806 2009-08-13 14:47:00 As far as I'm aware there is no fix for this particular case - you cannot have the same table being written to by two procedures in a single operation.

The workaround is to use two queries, or to use another stored procedure to handle the whole thing (both the insert and the delete). 		Erayd (23)
800807 2009-08-13 22:09:00 How do you mean with two queries? Without a trigger?

Yeah I was thinking about a stored procedure, but wouldn't that cause the same issue?

Thanks,



Gregor 		WarNox (8772)
800808 2009-08-13 22:52:00 MySQL sadly doesn't handle this situation very well. So you want it to check if theres already 1000 entries and if so, delete the first?
I don't think MySQL has a builtin rownum or rowid value, so I'm not sure how well this will work - I've dealt mostly with MSSQL & Oracle so some of the syntax differences to MySQL may not be correct..



CREATE TRIGGER limitRows BEFORE INSERT ON log_table
BEGIN
SET @a := 0;
SELECT *, @a := @a+1 as ROWNUM
FROM log_table
IF (Select Count(*) from log_table) > 999 THEN
DELETE FROM log_table
WHERE ROWNUM = 1
ENDIF
END;


Any chance of seeing the full code for the trigger you're trying? 		inphinity (7274)
800809 2009-08-17 08:19:00 Thanks for the reply . I will have to check if mysql has the rownum function . I know mssql does as I've used it before .

This is the trigger which fails in mysql


delimiter |
CREATE TRIGGER max_five BEFORE INSERT ON Temp
FOR EACH ROW BEGIN
DECLARE tot_rows integer;
DECLARE min_value integer;
SELECT count(*) INTO tot_rows FROM Temp;
SELECT min(ID) INTO min_value FROM Temp;
IF tot_rows >= 5 THEN
DELETE FROM Temp WHERE ID=min_value;
END IF;
INSERT INTO Temp (IP_Address, Date_Time) VALUES (NEW . IP_Address, NEW . Date_Time);
END;
|

That code is valid but the problem that I described above arises . This is done on a test database so hence the table name 'temp' :)

Thanks again,



Gregor 		WarNox (8772)
800810 2009-08-17 09:52:00 How do you mean with two queries? Without a trigger?Exactly.

Yeah I was thinking about a stored procedure, but wouldn't that cause the same issue?Not if you do both actions from within the same SP - that's the entire reason I suggested it. 		Erayd (23)
1