List:General Discussion« Previous MessageNext Message »
From:Ian Sales (DBA) Date:October 10 2005 7:51am
Subject:Re: Triggers
View as plain text  
Steffan A. Cline wrote:

>Upon insert or update I get the following error:
>
>ERROR 1442 (HY000): Can't update table 'locations' in stored
>function/trigger because it is already used by statement which invoked this
>stored function/trigger.
>
>What exactly is the meaning of this? Is there no way around this? I only
>want to update the one that was just inserted/updated.
>  
>
- you cannot use a table in a trigger which is triggered by an action on 
that self-same table, as this is recursive. You can, however, use NEW as 
a synonym for the data being changed which fires off the trigger. E.g.,

CREATE TRIGGER InsPopCoord AFTER INSERT ON locations FOR EACH ROW
UPDATE zipcodes
SET zipcodes.lat=NEW.lat, zipcodes.lon=NEW.lon
WHERE  zipcodes.zip=NEW.zip;


- ian

-- 
+-------------------------------------------------------------------+
| Ian Sales                                  Database Administrator |
|                                                                   |
|                              "All your database are belong to us" |
| ebuyer                                      http://www.ebuyer.com |
+-------------------------------------------------------------------+

Thread
TriggersSteffan A. Cline9 Oct
  • Re: TriggersRhino9 Oct
    • Re: TriggersGleb Paharenko10 Oct
  • Re: TriggersDBA)10 Oct
    • Re: TriggersSteffan A. Cline10 Oct
      • Re: TriggersSGreen10 Oct
    • Re: TriggersSteffan A. Cline10 Oct