Mysql – Trigger to delete inserted record if it doesn’t exist in another table

deleteMySQLtrigger

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.

Best Answer

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> 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> 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> 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>
Related Question