MySQL Trigger Functions for Efficient Update Operations: Utilizing UPDATE() and COLUMNS_UPDATED() for Real-Life Scenarios

UPDATE(column_name)

Real-life use case

Imagine a table called employees. You want to maintain a log whenever the salary column is updated. You can create a trigger that uses the UPDATE() function to check if the salary column was modified. If updated, the trigger inserts a record into a separate salary_log table to maintain the change history.

CREATE TRIGGER after_update_salary
AFTER UPDATE ON employees FOR EACH ROW
BEGIN
    IF UPDATE(salary) THEN
        INSERT INTO salary_log (employee_id, old_salary, new_salary, change_date)
        VALUES (NEW.id, OLD.salary, NEW.salary, NOW());
    END IF;
END;

In this example, the after_update_salary trigger activates after an update operation on the employees table. The UPDATE() function checks whether the salary column was modified. If it returns TRUE, the trigger inserts a record into the salary_log table. This record contains the employee ID, old salary, new salary, and change date.

2. COLUMNS_UPDATED() Function: The COLUMNS_UPDATED() function is used to return a bitmask that represents the columns updated during an update operation in a trigger or stored procedure. Each bit in the bitmask corresponds to a column in the table, and if the corresponding bit is set to 1, it indicates that the column was updated. The syntax for the COLUMNS_UPDATED() function is as follows:

COLUMNS_UPDATED()

Real-life use case

Let’s consider a scenario where you have a table called 

inventory that contains various columns, including quantity, price, and last_updated. You want to update the last_updated column to the current timestamp whenever either the quantity or price columns are modified. You can use the COLUMNS_UPDATED() function along with a bitwise operation to achieve this:

CREATE TRIGGER after_update_inventory
AFTER UPDATE ON inventory FOR EACH ROW
BEGIN
    IF (COLUMNS_UPDATED() & B'0011') != 0 THEN
        SET NEW.last_updated = NOW();
    END IF;
END;

In this example, the trigger after_update_inventory is activated after an update operation on the inventory table. The COLUMNS_UPDATED() function returns a bitmask representing the updated columns, and we use a bitwise AND operation (&) with the bitmask B’0011′ to check if either the quantity or price columns were updated (since 0011 in binary represents columns 2 and 3). If the result is not zero (i.e., at least one of the specified columns was updated), the trigger sets the last_updated column to the current timestamp using the NOW() function.

These are just a couple of examples to illustrate the implementation of the UPDATE() and COLUMNS_UPDATED() functions in MySQL triggers. Depending on your specific use case, you can leverage these functions to perform various actions based on the updated columns in your database.