Menu
In MySQL, I want to fire a trigger after the update of specific columns.
I know how to do it in Oracle and DB2:
CREATE TRIGGER myTrigger AFTER UPDATE of myColumn1,myColumn2 ... ON myTable FOR EACH ROW BEGIN .... END How to do that with MySQL?
529
In SQL Server, you can create DML triggers that execute code only when a specific column is updated. The trigger still fires, but you can test whether or not a specific column was updated, and then run code only if that column was updated. You can do this by using the UPDATE() function inside your trigger.
By default, triggers will be created in the schema you are currently working in. trigger_name: This is the name of the trigger which you want to create. table_name: This is the name of the table to which the trigger will be applied. SQL statements: SQL statements form the body of the trigger.
Using a SQL Server trigger to check if a column is updated, there are two ways this can be done; one is to use the function update(<col name>) and the other is to use columns_updated().
Restrictions. We can access the OLD rows but cannot update them. We can access the NEW rows but cannot update them. We cannot create an AFTER UPDATE trigger on a VIEW.
You can't trigger on a particular column update in SQL. It is applied on a row.
You can put your condition for columm in your trigger with an IF statement, as below:
DELIMITER // CREATE TRIGGER myTrigger AFTER UPDATE ON myTable FOR EACH ROW BEGIN IF !(NEW.column1 <=> OLD.column1) THEN --type your statements here END IF; END;// DELIMITER ; You can't specify only to fire on specific column changes. But for a record change on a table you can do
delimiter | CREATE TRIGGER myTrigger AFTER UPDATE ON myTable FOR EACH ROW BEGIN ... END | delimiter ; In your trigger you can refer to old and new content of a column like this
if NEW.column1 <> OLD.column1 ... If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
