Oracle and PL/SQL Recipes: A Problem-Solution Approach

5-3. Responding to an Update of a Specific Table Column

Problem

You want to automatically update some particular values within a table based upon another update that has been made on a specific column of another table. For instance, assume that management has decided to change some positions around within your organization. A new manager is coming to one of the current manager positions, so several employees will receive a new manager. You need to find a way to update several employee records to change their manager from the old one to the new one.

Solution

Create an AFTER UPDATE trigger that will be executed only when the MANAGER_ID column is updated. The following trigger uses a cursor to obtain the employees that are supervised by the old ...