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.

  • You would think a trigger might be a great way of doing this. Except the front-end will have no idea that an entered tech_zips row has been magically deleted in the back-end. And this is saying nothing about the scalability of triggers. Build a function in php (gah!) that confirms the validity of the zips entered.
    – Hannah Vernon
    Oct 30, 2013 at 6:23
  • Wouldn't that be just as taxing? The only thing I can think of doing is creating a 33,000+ record array to check against. Is it better for PHP to handle that type of comparison than mysql? As far as the frontend not knowing - it isn't important as this info is only going to be used by another system on another sub-domain that allows someone to search and see if we service an area. Oct 30, 2013 at 12:22
  • @SherwoodPro trust me avoid triggers in MySQL and save yourself the time learning them.. dba.stackexchange.com/questions/48797/… Oct 30, 2013 at 22:21

2 Answers 2


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
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)
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`))

  • @RaymondNijland thanks for the +1 but I do wish you were not so "down" on triggers in MySQL. Your objections seem largely theoretical and your observations in the post you reference do not seem right based on my review of the code. Triggers, proc, and functions all consume resources -- but that's why we have resources -- so we can use them to accomplish what needs to be done. In this case, foreign keys simply seem more appropriate. Triggers are a reality, they are very useful and practical, and far superior to trusting the application logic to do things the database needs to do for itself. Oct 31, 2013 at 3:05
  • your are right indeed based on thoery.. i may need to analyse this better to make an statement on how this impacts the performance only an small part off the trigger code is analysed.. Oct 31, 2013 at 7:19

You cannot make an INSERT trigger DELETE the same row in just put it.

For the record, it is possible to make the trigger with some special code to interrupt the trigger's progress in the event the zipcode was missing. But is this plausible? It would slow down the INSERT row by row. It would also disturb bulk inserts should anyone of the zipcodes be missing.

Try constructing an INSERT that receives results from a SELECT that gives you all-or-nothing:

INSERT INTO tech_zips (tech_id, zip)
SELECT A.NewTechID,B.zip_id FROM
(SELECT 12345 NewTechID,'10013' zip_code) A
INNER JOIN zip_code B ON A.zip_code=B.zip_code;

If you have a batch of zipcodes, do this:

INSERT INTO tech_zips (tech_id, zip)
SELECT A.NewTechID,C.zip_id FROM
(SELECT 12345 NewTechID) A,
    SELECT '10013' zip_code UNION
    SELECT '10014'          UNION
    SELECT '10015'          UNION
    SELECT '10016'
) B
INNER JOIN zip_code C ON B.zip_code=C.zip_code;

In the first case, if zip_code '10013' did not exist in the zip_code table, nothing is inserted.

In the second case, if zip_codes '10014' and '10015' do not exist in the zip_code table, you should expect tech_id 12345 to have 2 rows inserted with the zip_ids for zip_codes '10013' and '10016'. Therefore, there is no messy DELETE/UNDELETE to code in a trigger.

Give it a Try !!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.