Trigger to delete inserted record if it doesn't exist in another table I have a question.  I have two tables:


zip_code (zip_id, zip_code, state) - this is a static table that
stores areas I service
tech_zips (tz_id, tech_id, zip) - this is where I store what zip codes a
specific tech services


So, on my form, I have a select list that chooses a tech and a textarea to paste in zip codes.  This works fine, except it doesn't take into consideration whether or not I service a zip code.

Instead of coming up with crazy arrays and whatnot in PHP - I figured a trigger would be easier, except I know nothing about them.  :(

How would I write a trigger that does this: When a new zip code is inserted into tech_zips, the trigger sees whether or not that zip  exists in the zip_code table.  If it doesn't, then it deletes it.

Thanks for any help!  Oh, I have phpmyadmin.
Well, it's a crazy and novel idea, but you might consider that this is what foreign key constraints are actually for... you can't insert a row in table "x" if a column there has a foreign key constraint against a column in table "y" but the value you're inserting in "x" doesn't exist in "y"...

mysql&gt; create table t1 (
         id int not null auto_increment primary key
       ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.40 sec)

mysql&gt; create table t2 (
         id int not null auto_increment primary key, 
         t1_id INT NOT NULL, 
         FOREIGN KEY(t1_id) REFERENCES t1(id)
       ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.49 sec)

mysql&gt; insert into t2 (t1_id) VALUES (1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
(`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`))

mysql&gt;