I'm trying to write an Oracle update trigger that will NOT fire when a certain column is updated. Let's say I have four columns, a, b, c and d. I only want the trigger to work when a, b and d are updated. It would be best if I could say something like "update on table when column not like c." The c column is consistent throughout all of my 196 tables; the columns a,b and d are not. I'm using Oracle 8.1.7.
In Oracle, a trigger can be fired on UPDATE of specific columns. The following syntax can be used to define a trigger that will fire on updates of only specific columns of a table:
CREATE OR REPLACE TRIGGER my_tab_upd_trigger BEFORE UPDATE OF column_a,column_b,column_d ON my_tab FOR EACH ROW BEGIN .... END; /Hopefully, you can see how I only have three of the four columns defined in the trigger definition. If column_c is updated, the trigger will not fire.
For More Information
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.