Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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. –  Max Vernon Oct 30 '13 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. –  SherwoodPro Oct 30 '13 at 12:22
    
@SherwoodPro trust me avoid triggers in MySQL and save yourself the time learning them.. dba.stackexchange.com/questions/48797/… –  Raymond Nijland Oct 30 '13 at 22:21
add comment

2 Answers

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>
share|improve this answer
    
@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. –  Michael - sqlbot Oct 31 '13 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.. –  Raymond Nijland Oct 31 '13 at 7:19
add comment

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 !!!

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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