MySQL Update Trigger that updates multiple columns from values in another table

MySQLtrigger

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?

Best Answer

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.

Related Solutions

Mysql – Create a trigger to update table data on another Server’s database

To accomplish what you are wanting to do, it is possible to use the FEDERATED storage engine on both servers, in conjunction with triggers, to allow each server to update the other server's database.

This is not exactly a simple out-of-the-box solution, because it requires additional precautions and requires you to decide whether consistency or isolation tolerance is more important and allow the queries to fail when the other server isn't available (more consistency) or use a CONTINUE HANDLER to suppress errors (isolation tolerance).

But here is an extremely simplified example.

Each server would have the identical configuration.

The local user table:

CREATE TABLE user (
  username varchar(64) NOT NULL,
  password varbinary(48) NOT NULL, /* encrypted of course */
  PRIMARY KEY(username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

A local table that is federated to the user table on the other server.

CREATE TABLE remote_user (
  username varchar(64) NOT NULL,
  password varbinary(48) NOT NULL, /* encrypted of course */
  PRIMARY KEY(username)
) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://username:pass@the_other_host:port/schema/user';

Selecting from remote_user on one server will retrieve the records from the other server, and insert/update/delete on that table will change data on the other server.

So, we create triggers do accomplish the purpose of updating the distance server. They are written as BEFORE triggers, with the idea being that we don't want to do something to ourselves that we can't do to the other server -- for example, if a username already exists on the other server, but not here, we want the insert on the other server to throw an error that prevents us from creating the user here... as opposed to creating a user here with what would be a conflicting username. This is, of course, one of the tradeoff decisions you'll need to make.

DELIMITER $$

CREATE TRIGGER user_bi BEFORE INSERT ON user FOR EACH ROW
BEGIN
  INSERT INTO remote_user (username,password) VALUES (NEW.username,NEW.password);
END $$

CREATE TRIGGER user_bu BEFORE UPDATE ON user FOR EACH ROW
BEGIN
  UPDATE remote_user 
     SET username = NEW.username,
         password = NEW.password
   WHERE username = OLD.username;
END $$

CREATE TRIGGER user_bd BEFORE DELETE ON user FOR EACH ROW
BEGIN
  DELETE FROM remote_user
   WHERE username = OLD.username;
END $$

DELIMITER ;

This is not a perfect solution and is not a high-availability solution, because it relies on solid connectivity between the two systems and even if you are using InnoDB and transactions, the actions you take against the target table are not part of your local transaction and cannot be rolled back.

I use the FEDERATED engine quite a bit; it comes in handy for a number of creative purposes in my environment, including one situation where I used a federated query launched by a trigger to impose foreign key constraints against a foreign data source; however, I restrict its use to back-end processes where unexpected issues such as timeouts, coding errors, or server-to-server network/outage/isolation events cannot result in the end user on one of our web sites experiencing any kind of problem. Your ability to tolerate such a situation would be a major determining factor into whether this is an appropriate solution.

An alternative would be to configure your two servers in master/master replication. For this, you would need to use different database names on each server, so that for most events that replicate, the two servers could not possibly conflict with each other. In the worst-case scenario, if you lose connectivity or encounter a replication error, the two sites would still be running independently and you could resynchronize and recover. Configuration would look something like this:

database_a database for site A
database_b database for site B
database_c database for only the shared table(s)

Then, in database_a and database_b:

CREATE ALGORITHM=MERGE SQL SECURITY INVOKER VIEW user AS SELECT * FROM c.user;

MySQL will treat database_a.user and database_b.user as aliases for the "real" user table, database_c.user, so you would not have to change your application other than to use its designated database (i.e, you wouldn't have to configure it to understand that the user table was actually in a different schema, because the view will function pretty much transparently with this configuration). If the schemas have foreign keys against the user table, you would declare those against the true base table database_c.user.

Configure the two servers to replicate everything, but set auto_increment_increment and auto_increment_offset appropriately so you do not have conflicting auto-increment values on the shared table(s), if your tables use auto-increment. (Note, the documentation says that these variables are for NDB tables only, but that's not accurate).

An extra advantage of this setup is that your two servers would then have a complete duplicate of the other site's data that you could potentially use to your advantage for recovery from hardware failure in one of the servers.

Mysql – Unable to update table after creating an After update trigger

Thats correct you can not update the same table where the trigger is getting executed, however if you use before update you can set the st to 0 by checking the conditions something as

DELIMITER $$

USE `smsdev`$$

DROP TRIGGER /*!50032 IF EXISTS */ `pageprivilege_update`$$

CREATE
    /*!50017 DEFINER = 'smsdev'@'%' */
    TRIGGER `pageprivilege_update` BEFORE UPDATE ON `pageprivilege` 
    FOR EACH ROW 
    BEGIN
     if (new.ad = 0 AND new.ed =0 AND new.dl =0 AND new.rd = 0) then
       set new.st=0 ;
     end if;
END;
$$

DELIMITER ;

Best Answer

Related Solutions

Mysql – Create a trigger to update table data on another Server’s database

Mysql – Unable to update table after creating an After update trigger

Related Question