For Example: The price of a product changes constantly.
It is important to maintain the history of the prices of the products.
It works, but it won't tell you if the value changed, only that the column is being updated (even if the update doesn't change the value).
Many applications update all of the columns in a table -- setting them to their old value, rather then trying to figure out exactly which columns have been updated by the client. create or replace trigger test_trigger 2 after update of x,y on test 3 for each row 4 begin 5 if updating( 'X' ) then 6 dbms_output.put_line( 'X is being updated' ); 7 end if; 8 9 if updating( 'Y' ) then 10 dbms_output.put_line( 'Y is being updated' ); 11 end if; 12 end; 13 / Trigger created.
You might omit this clause if you just want to record the fact that the operation occurred, but not examine the data for each row.
We can create a trigger to update the 'product_price_history' table when the price of the product is updated in the 'product' table.
1) Create the 'product' table and 'product_price_history' table Once the above update query is executed, the trigger fires and updates the 'product_price_history' table.
1) Row level trigger - An event is triggered for each row upated, inserted or deleted.
2) Statement level trigger - An event is triggered for each sql statement executed.
SQL Notice in the last update it indicated that x and y both were being updated (which they were), but the update didn't change the value (both x and y were 1 prior to the update which set them to 1). You can use updating (column) but it will only tell you that the column itself is being updated and not if the value has changed.