From: Daevid Vincent
Date: May 16 2006 2:40am
Subject: RE: Need help with triggers
List-Archive: http://lists.mysql.com/mysql/198004
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

> > This is my first trigger I'm trying to write.
> >
> > I have two tables. 'stores' and 'zipcodes'.
> >
> > I want to automatically set the latitude and longitude of 
> the store using
> > it's zipcode lookup in the zipcode table.
> >
> > DELIMITER $$;
> >
> > DROP TRIGGER `store_coord`$$
> >
> > create trigger `store_coord` BEFORE INSERT on `stores`
> > for each row BEGIN
> >     UPDATE stores AS s, zipcodes AS z
> > SET s.latitude = z.latitude,
> >     s.longitude = z.longitude
> > WHERE s.zip = z.zip AND s.id = NEW.id;
> >   END;
> > $$
> 
> First things first ...
> 
> Updating a row that doesn't exist yet (BEFORE INSERT trigger)
> doesn't make sense :-)
> 
> Why not simply adjust it like this:
> new.latitude := <your looked up latitude>
> new.longitude := <your looked up longitude>
> 
> Given that it's a "before insert", modifying the NEW.<column> values,
> these will be stored in the table.

> > So when I do this:
> >
> > insert into `stores` (id, name, zip) values (10,'test 
> company',  14526);
> >
> > I get this error
> >
> > Error Code : 1442
> > Can't update table 'stores' in stored function/trigger because it is
> > already used by statement which invoked this stored function/trigger.

I must not be doing something right, I've tried all these combinations and
ALL give that same error message:

DELIMITER $$;
DROP TRIGGER `store_coord`$$
create trigger `store_coord` BEFORE INSERT on `stores`
	for each row BEGIN
	    UPDATE stores AS s, zipcodes AS z
	SET NEW.latitude = z.latitude,
	    NEW.longitude = z.longitude
	WHERE s.zip = z.zip AND s.id = NEW.id;
  END;
$$

DELIMITER $$;
DROP TRIGGER `store_coord`$$
create trigger `store_coord` BEFORE INSERT on `stores`
	for each row BEGIN
	    UPDATE stores AS s, zipcodes AS z
	SET NEW.latitude := z.latitude,
	    NEW.longitude := z.longitude
	WHERE s.zip = z.zip AND s.id = NEW.id;
  END;
$$

DELIMITER $$;
DROP TRIGGER `store_coord`$$
create trigger `store_coord` AFTER INSERT on `stores`
	for each row BEGIN
	    UPDATE stores AS s, zipcodes AS z
	SET s.latitude = z.latitude,
	    s.longitude = z.longitude
	WHERE s.zip = z.zip AND s.id = NEW.id;
  END;
$$

What am I doing wrong?