What makes a Trigger unique?

In Oracle, triggers and procedures are both PL/SQL objects that execute a sequence of actions, but they serve distinct purposes and have key differences in their characteristics. Here’s a breakdown of the distinguishing features:

1. Invocation
   • Trigger:
     • Automatically invoked by Oracle in response to specific events such as INSERT, UPDATE, or DELETE operations on a table or a view, or database-level events (e.g., logon or system errors).
     • Cannot be explicitly called by a user or another PL/SQL program.
   • Procedure:
     • Explicitly called by the user, an application, or another PL/SQL block using the EXEC statement or within a PL/SQL block using its name.
2. Purpose
   • Trigger:
     • Used to enforce complex business rules, maintain audit trails, enforce security policies, or synchronize data automatically when certain events occur.
     • Often acts as a reactive mechanism to changes in the database.
   • Procedure:
     • Used to encapsulate and execute reusable business logic or operations, such as performing calculations, validating data, or executing multiple SQL statements in a controlled manner.
     • Acts as an explicitly called, reusable unit of logic.
3. Execution Context
   • Trigger:
     • Executes implicitly within the transaction or operation that caused it to fire.
     • Cannot accept parameters or return values.
     • Cannot commit, rollback, or change the transaction context because it runs within the triggering transaction.
   • Procedure:
     • Executes independently and can accept IN, OUT, or IN OUT parameters to interact with the calling environment.
     • Can commit or rollback transactions explicitly.
4. Event Scope
   • Trigger:
     • Bound to a specific table, view, or database event.
     • Can execute before, after, or instead of the triggering event.
     • Limited to the scope of the event and its related data.
   • Procedure:
     • Not tied to any specific event or object.
     • Can operate on any data within the database, based on the parameters passed to it.
5. Lifecycle and Reusability
   • Trigger:
     • Automatically executed and cannot be reused directly in different contexts.
     • Created with a predefined purpose for specific events on specific objects.
   • Procedure:
     • Can be reused and called multiple times in different contexts and applications.
     • Modular, allowing for flexibility in usage.
6. Performance Impact
   • Trigger:
     • May introduce performance overhead because it executes automatically for every relevant operation (e.g., every row of an INSERT statement).
     • Can potentially lead to cascading effects if poorly designed.
   • Procedure:
     • Executes only when explicitly called, giving developers control over when and how it impacts performance.
7. Use in SQL Statements
   • Trigger:
     • Cannot be called within SQL statements directly.
     • Only executes in response to database events.
   • Procedure:
     • Cannot be used in SQL queries but can be invoked explicitly from a PL/SQL block, another procedure, or a trigger.

Summary Table

Characteristic	Trigger	Procedure
Invocation	Automatically triggered by events	Explicitly called by users or programs
Purpose	Enforce rules, audit trails, react to events	Encapsulate reusable logic
Execution Context	Bound to event, no parameters or returns	Independent, can take/return parameters
Event Scope	Specific to a table/view or database event	Independent of specific events
Lifecycle and Reusability	Not reusable, tied to specific events	Reusable in multiple contexts
Performance Impact	Implicit and may cause performance overhead	Controlled and explicit execution
Use in SQL Statements	Cannot be called in SQL	Cannot be called in SQL, only invoked directly

By understanding these distinctions, developers can determine when to use triggers or procedures based on the requirements of their database logic.

A trigger 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:

1. 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.
2. 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.
3. 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.
4. 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 following series of images below illustrates the possible actions that can cause a table level trigger to fire.

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 series of images 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.