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

[Georg]

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