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

In MySQL, the UPDATE() and COLUMNS_UPDATED() functions are used to identify which columns were updated in a trigger or a stored procedure. These functions are particularly useful when you want to perform specific actions based on the columns that were modified during an update operation. Let’s explain each function and provide real-life use cases to understand their implementation better.
  1. UPDATE() Function:The UPDATE() function returns TRUE if a specified column was updated during the current update operation in a trigger or stored procedure. Otherwise, it returns FALSE. The syntax for the UPDATE() function is as follows:

 

Real-life use case

Let’s consider a scenario where you have a table called employees, and you want to maintain a log of changes whenever the salary column is updated. You can create a trigger that uses the UPDATE() function to check if the salary column was modified during an update operation. If it was updated, you can insert a record into a separate salary_log table to maintain the change history.

In this example, the trigger after_update_salary is activated after an update operation on the employees table. The UPDATE() function checks if the salary column was modified (i.e., updated), and if it returns TRUE, the trigger inserts a record into the salary_log table with the old and new salary values along with the change date.

  1. 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:

 

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:

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.

About Shiv Iyer 444 Articles
Open Source Database Systems Engineer with a deep understanding of Optimizer Internals, Performance Engineering, Scalability and Data SRE. Shiv currently is the Founder, Investor, Board Member and CEO of multiple Database Systems Infrastructure Operations companies in the Transaction Processing Computing and ColumnStores ecosystem. He is also a frequent speaker in open source software conferences globally.