What are some common errors or pitfalls when writing triggers in Oracle?

2 Trigger order and timing

Another common pitfall when writing triggers in Oracle is not considering the order and timing of the triggers. Oracle does not guarantee a specific order of execution for triggers that fire on the same event and table, unless you use the FOLLOWS or PRECEDES clauses. This can lead to unpredictable results if the triggers depend on each other or have side effects. Moreover, you need to be aware of the timing of the triggers, whether they fire before or after the event, and whether they fire for each row or for each statement. Depending on the timing, the trigger may have access to different values of the :new and :old pseudorecords, or may affect the number of rows affected by the event.

3 Deadlocks and concurrency

A third common error when writing triggers in Oracle is creating deadlocks or concurrency issues. A deadlock occurs when two or more sessions try to acquire locks on the same resources, but in a different order, preventing each other from completing their transactions. For example, if you have a trigger on the orders table that inserts a row into the order_details table, and another trigger on the order_details table that updates the orders table, you may create a deadlock if two sessions try to insert orders and order details at the same time. To prevent deadlocks, you should avoid triggers that access multiple tables, or use indexes, constraints, and foreign keys to enforce referential integrity. Concurrency issues occur when multiple sessions try to modify the same data at the same time, causing conflicts or inconsistencies. To avoid concurrency issues, you should use optimistic or pessimistic locking mechanisms, or use isolation levels and serializable transactions to control the visibility and consistency of the data.

5 Debugging and testing

A fifth common error when writing triggers in Oracle is not debugging and testing them properly. Triggers can be difficult to debug and test, because they are hidden from the application logic, and they may fire in different scenarios and contexts. For example, if you have a trigger that raises an exception or rolls back a transaction, you may not see the error message or the effect on the data unless you check the database logs or use a debugging tool. To debug and test triggers effectively, you should use tools such as SQL Developer, DBMS_DEBUG, or DBMS_OUTPUT, or use logging and tracing techniques to capture the trigger behavior and output. You should also use unit testing frameworks, such as utPLSQL, to verify the trigger functionality and correctness.