Skip navigation links

MySQL Forums :: Triggers :: Can't update table 'tbl' in stored function/trigger because it is already used by statement which invoked this stored function/trigger


Advanced Search

Can't update table 'tbl' in stored function/trigger because it is already used by statement which invoked this stored function/trigger
Posted by: Georg ()
Date: October 19, 2006 02:47PM

Hi there

I'm not new to MySQL, but definately to Triggers, Stored Procedures, Views, the 5.x stuff ...

Can someone explain for me the (deeper) reason for this error message please?

No matter what I try, I always end up with it :(

My intentions are a bit complex, but this here for a short overview:
I have a table (imported from MsAccess) with more than 800k adresses. Each row has a DATETIME field (lets call it print_date) when it was last printed, if ever, otherwise it contains NULL. Since the records should remain as unique as possible and by any chance stay as updated as possible, I'ld like to create a trigger, that checks for one or more existing rows (checking for tbl.lastname, tbl.postal_code, tbl.birthday and (tbl.tel OR tbl.tel2)). If this is the case, it should take the MAX(print_date) - if not older than 1 year - from the (yet) existing rows, delete them then and let the insert happen with this MAX(print_date) ... (to keep the table 'clean')

So this is what I tried:

CREATE TRIGGER testtrigger BEFORE INSERT ON tbl
FOR EACH ROW
BEGIN
DECLARE printval DATETIME;
SELECT MAX(print_date) INTO printval FROM tbl WHERE postal_code = NEW.postal_code AND lastname = NEW.lastname AND birthday = NEW.birthday AND ((tel = NEW.tel OR tel2 = NEW.tel) OR (tel = NEW.tel2 OR tel2 = NEW.tel2)) GROUP BY CONCAT(lastname,birthday,etc..);
SET NEW.print_date = IF(DATE_ADD(printval, INTERVAL 1 YEAR) < NOW(),NULL,printval);
DELETE FROM tbl WHERE NEW.postal_code AND lastname = NEW.lastname AND birthday = NEW.birthday AND ((tel = NEW.tel OR tel2 = NEW.tel) OR (tel = NEW.tel2 OR tel2 = NEW.tel2));
END;

I also tried to put the SELECT and the DELETE into a stored procedure and CALL it from the trigger but with the same result:

Can't update table 'tbl' in stored function/trigger because it is already used by statement which invoked this stored function/trigger <-- this happens with ALL INSERT statements

So it looks like that either I didnt understand the trigger thing right, or I'm trying to do something that MySQL doesnt want me to this way.

In any case, your thoughts and hints are more than just welcome ...
... thanks
Georg

Options: ReplyQuote


Subject Views Written By Posted
Can't update table 'tbl' in stored function/trigger because it is already used by statement which invoked this stored function/trigger 55546 Georg 10/19/2006 02:47PM
Re: Can't update table 'tbl' in stored function/trigger because it is already used by statement which invoked this stored function/trigger 28464 Christian Trummer 10/20/2006 11:03AM
Re: Can't update table 'tbl' in stored function/trigger because it is already used by statement which invoked this stored function/trigger 21308 Georg Klaissner 10/20/2006 03:06PM
Re: Can't update table 'tbl' in stored function/trigger because it is already used by statement which invoked this stored function/trigger 20115 mikeryksen 12/07/2006 01:38PM
Re: Can't update table 'tbl' in stored function/trigger because it is already used by statement which invoked this stored function/trigger 21717 Jody Robbins 03/18/2008 10:13AM
Re: Can't update table 'tbl' in stored function/trigger because it is already used by statement which invoked this stored function/trigger 15951 Peter Betzler 11/21/2006 10:43PM
Re: Can't update table 'tbl' in stored function/trigger because it is already used by statement which invoked this stored function/trigger 16754 b G 12/24/2006 01:06PM
Re: Can't update table 'tbl' in stored function/trigger because it is already used by statement which invoked this stored function/trigger 14860 Maarten Abbink 08/01/2007 02:02PM
Re: Can't update table 'tbl' in stored function/trigger because it is already used by statement which invoked this stored function/trigger 11256 Roberto Fonseca 04/23/2008 08:46PM
Re: Can't update table 'tbl' in stored function/trigger because it is already used by statement which invoked this stored function/trigger 11622 Matt Price-Hutchinson 05/15/2008 04:50AM
Re: Can't update table 'tbl' in stored function/trigger because it is already used by statement which invoked this stored function/trigger 9780 Matt Price-Hutchinson 05/15/2008 04:51AM
Re: Can't update table 'tbl' in stored function/trigger because it is already used by statement which invoked this stored function/trigger 11000 Saul Azcano 06/26/2008 09:32AM
Re: Can't update table 'tbl' in stored function/trigger because it is already used by statement which invoked this stored function/trigger 10114 Enrique Delgado 08/06/2008 04:27PM
Re: Can't update table 'tbl' in stored function/trigger because it is already used by statement which invoked this stored function/trigger 16277 Andrej Nagy 01/05/2009 02:55PM
Re: Can't update table 'tbl' in stored function/trigger because it is already used by statement which invoked this stored function/trigger 9847 Brian Dieckman 02/25/2009 10:58AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.