List:General Discussion« Previous MessageNext Message »
From:Quentin Bennett Date:May 16 2006 2:53am
Subject:RE: Need help with triggers
View as plain text  
I've never written a trigger, but the error implies that you can't update the stores table
when in a trigger ON the stores table.

This makes sense, especially if you consider an 'UPDATE' trigger - the trigger would
become recursive.

So, rather than deciding which combination are recursive and which aren't, maybe there's a
general rule that you can't change the table on which the trigger is placed?

Hopefully someone will let you (and me) know if I don't know what I'm talking about!

Quentin

-----Original Message-----
From: Daevid Vincent [mailto:daevid@stripped]
Sent: Tuesday, 16 May 2006 2:41 p.m.
To: 'Martijn Tonies'; mysql@stripped
Subject: RE: Need help with triggers


> > 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?


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
The information contained in this email is privileged and confidential and
intended for the addressee only. If you are not the intended recipient, you
are asked to respect that confidentiality and not disclose, copy or make use
of its contents. If received in error you are asked to destroy this email
and contact the sender immediately. Your assistance is appreciated.
Thread
Need help with triggersDaevid Vincent15 May
  • Re: Need help with triggersMartijn Tonies15 May
    • RE: Need help with triggersDaevid Vincent16 May
RE: Need help with triggersQuentin Bennett16 May
Re: Need help with triggersPeter Brawley16 May