From: Peter Brawley Date: May 16 2006 4:00am Subject: Re: Need help with triggers List-Archive: http://lists.mysql.com/mysql/198006 Message-Id: <44694E55.1050806@earthlink.net> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=======AVGMAIL-44694E564A57=======" --=======AVGMAIL-44694E564A57======= Content-Type: multipart/alternative; boundary=------------000503000906000608050305 --------------000503000906000608050305 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Daevid, >> > > 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; >> > > $$ Lose the UPDATE clause. All you need (and all that's permitted) is SET NEW ... And lose the WHERE clause. It's redundant (also not permitted). If you need a value from another table, DECLARE a var and write a SELECT INTO for it. (BTW, if your database looks up latitude & longitude from zipcodes, why bother copying them into store_coord?) PB >>> 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 := >> new.longitude := >> >> Given that it's a "before insert", modifying the NEW. 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? > > > --------------000503000906000608050305 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Daevid,

>> > > 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;
>> > > $$

Lose the UPDATE clause. All you need (and all that's permitted) is SET NEW ...
And lose the WHERE clause. It's redundant (also not permitted).
If you need a value from another table, DECLARE a var and write a SELECT INTO for it.
(BTW, if your database looks up latitude & longitude from zipcodes, why bother copying them into store_coord?)

PB

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?

--------------000503000906000608050305-- --=======AVGMAIL-44694E564A57======= Content-Type: text/plain; x-avg=cert; charset=us-ascii Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Content-Description: "AVG certification" No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.6/339 - Release Date: 5/14/2006 --=======AVGMAIL-44694E564A57=======--