PDA

View Full Version : mysql help



WarNox
14-08-2009, 01:01 AM
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

Erayd
14-08-2009, 02:47 AM
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).

WarNox
14-08-2009, 10:09 AM
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

inphinity
14-08-2009, 10:52 AM
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?

WarNox
17-08-2009, 08:19 PM
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

Erayd
17-08-2009, 09:52 PM
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.