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 table PRODUCTS with columns id, name, sku, packaging_type, width, height, weight, case_count.

I want a trigger to fire when packaging_type is updated. packaging_type will be an integer value.

There is a second table PACK_TYPES with columns id, width, height, weight, case_count. This is where I need to get the values from.

So PRODUCTS.packaging_type gets updated, how do I update PRODUCTS.width, PRODUCTS.height, PRODUCTS.weight, and PRODUCTS.case_count with the corresponding values where PRODUCTS.packaging_type = PACK_TYPES.id?

share|improve this question
add comment

2 Answers 2

up vote 1 down vote accepted

If you want to get right down to it, that's not the best approach from a theoretical perspective, because you're copying data around in your database that you really should be deriving.

CREATE ALGORITHM=MERGE VIEW products_with_packaging_info AS
SELECT p.*, 
       pt.width as packaging_width, 
       pt.height as packaging_height,
       pt.weight as packaging_weight,
       pt.case_count AS packaging_case_count
  FROM PRODUCTS p
  JOIN PACK_TYPES pt ON pt.id = p.packaging_type;

Done. SELECT queries against this view work exactly the same as queries against either table individually, as long as every product has a pack type. Queries against this view can still take advantage of the indexes on the base tables, and there's no overhead involved with copying the attributes from one table to another, which always has the potential for update anomalies.

You might even be surprised to find that the columns in the view can actually be updated as if it were a table, with updates propagating down into the base tables.

I offer this suggestion because a well-designed database should be such that it is impossible to get two different answers to the same question. For example, if a PACK_TYPES row is changed because an error is found, how do its new values propagate backwards into products?

But if you really want to take the trigger approach, that looks something like this:

DELIMITER $$

DROP TRIGGER IF EXISTS PRODUCTS_bu $$
CREATE TRIGGER PRODUCTS_bu BEFORE UPDATE ON PRODUCTS FOR EACH ROW
BEGIN

  IF NOT (NEW.packaging_type <=> OLD.packaging_type) THEN
    BEGIN
      DECLARE my_width INT DEFAULT NULL;      -- using
      DECLARE my_height INT DEFAULT NULL;     -- the
      DECLARE my_weight INT DEFAULT NULL;     -- appropriate
      DECLARE my_case_count INT DEFAULT NULL; -- data types here
      SELECT width, height, weight, case_count
        FROM PACK_TYPES
       WHERE id = NEW.packaging_type
        INTO my_width, my_height, my_weight, my_case_count;
      SET NEW.width = my_width, NEW.height = my_height, NEW.weight = my_weight, NEW.case_count = my_case_count;
    END;
  END IF;

END $$
DELIMITER ;

The <=> "spaceship" is the "null-safe equality operator" which constrains "NOT [possibly null] = [possibly null]" to always be either TRUE or FALSE; this is needed because [possibly null] != [possibly null] will never be true if either expression is NULL. This is the case because, logically, "NOT (FALSE)" is "TRUE" while "NOT (NULL)" is "NULL."

I could have declared the variables at the beginning and avoided the inner BEGIN/END but it seems optimal to avoid that work until we know we actually need to execute the inner logic in the first place, which is avoided whenever 'packaging_type' hasn't actually changed on a row for a given update query. Within a block, declarations have to precede other statements, so delaying the declarations requires the addition of the inner BEGIN/END.

You would also want a similar trigger for BEFORE INSERT which would be identical except you'd remove the 4 lines starting with IF ... BEGIN ... END ... END IF from the body of the procedure, use a new trigger name, and change BEFORE UPDATE to BEFORE INSERT.

It's BEFORE -- not AFTER -- in both cases, because the trigger fires BEFORE the newly-inserted or newly-updated row is written to the database.

share|improve this answer
add comment

Something similar to this may work:

DROP TRIGGER IF EXISTS BI_PRODUCTS;
DELIMITER //
CREATE TRIGGER BI_PRODUCTS BEFORE INSERT ON PRODUCTS FOR EACH ROW 
BEGIN 
  SET NEW.width := (SELECT width FROM PACK_TYPES WHERE id = NEW.packaging_type);
END //

DELIMITER ;

However, why storing the records in a duplicate way? That is not very normalised. Unless you have a really good reason, a better way to do this would be to convert packaging_type into a FOREIGN KEY of PACK_TYPES and perform a JOIN whenever the columns from both tables are required.

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.