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 55944 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 28672 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 21428 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 20215 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 21841 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 16028 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 16825 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 14926 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 11311 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 11685 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 9826 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 11041 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 10165 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 16541 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 9931 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.