-
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:
|
1 |
UPDATE(column_name) |
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.
|
1 2 3 4 5 6 7 8 |
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 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.
- 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:
|
1 |
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:
|
1 2 3 4 5 6 7 |
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.
