| Lesson 3 | What is unique about a trigger? |
| Objective | Distinguish between a trigger and a procedure. |
What makes a Trigger unique?
A trigger is a stored sub-program that is similar to a procedure. It is automatically executed by the database whenever a specified
action occurs. The table below shows the similarities and differences between triggers and procedures.
| Description | Trigger | Procedure |
| Stored in the database | Yes | Yes |
| Can be executed manually | No | Yes |
| Can be executed automatically | Yes | No |
| Can call other procedures and functions | Yes | Yes |
| Can modify data (DML commands) | Yes, with some limitations | Yes |
| Can execute COMMIT command | No | Yes |
As the name implies, a trigger is fired when some database action occurs. You can define triggers to fire before or after the action.
Actions include inserting, updating, and deleting rows in a table. A trigger is always assigned to one of these levels:
Table level triggers are the only triggers covered in this module. The other levels are seldom used and are similar enough to table triggers that you can easily handle them if needed.
The SlideShow below illustrates the possible actions that can cause a table level trigger to fire.
- Table: You can create a trigger that fires before or after updating a row in a table. One typical function of this level of trigger is to keep data integrity synchronized. This is by far the most common level of trigger that is used.
- View: You can create a trigger that fires when you update a view and that actually replaces the update command with some other more complex commands. This is useful for Oracle Object views.
- Schema: You can create a trigger that fires whenever someone attempts to alter an object in the schema as a way to prevent invalid actions.
- Database: You can create triggers that fire whenever someone logs into the database. The trigger records activity in a log table.
Table level triggers are the only triggers covered in this module. The other levels are seldom used and are similar enough to table triggers that you can easily handle them if needed.
The SlideShow below illustrates the possible actions that can cause a table level trigger to fire.
Trigger Firing Sequence
A trigger that updates, inserts, or deletes rows can cause a cascading effect that fires other triggers in a looping effect. This is why you should avoid redundant or circular trigger firings.
The Slide Show showed you an extreme example, just to give you an idea of how the timing of triggers works. In reality, you seldom find a reason to create that many triggers for one action (inserting) on one table. It is more typical for you to create a trigger for each action (insert, update, and delete) on one table. Another typical scenario might involve creating several very similar triggers for related tables. For example, you might have five tables that all require a
The next lesson discusses the special variables that you can use when coding a trigger.
The Slide Show showed you an extreme example, just to give you an idea of how the timing of triggers works. In reality, you seldom find a reason to create that many triggers for one action (inserting) on one table. It is more typical for you to create a trigger for each action (insert, update, and delete) on one table. Another typical scenario might involve creating several very similar triggers for related tables. For example, you might have five tables that all require a
CREATE_DATE column filled in by using a trigger. In this case, you create five triggers that perform essentially the same task, which is one trigger for each table.The next lesson discusses the special variables that you can use when coding a trigger.