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?
|
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
