A trigger is a named PL/SQL unit that is stored in the database and can be invoked repeatedly when some events occur. A trigger is a named PL/SQL unit that is stored in the database and can be invoked repeatedly when some events occur.
You can enable and disable a trigger, but you cannot explicitly invoke it. While a trigger is enabled, the database automatically invokes it that is, the trigger fires whenever its triggering event occurs. While a trigger is disabled, it does not fire.
You can write triggers that fire whenever one of the following operations occurs.
- DML statements (INSERT, UPDATE, DELETE)
- DDL statements (CREATE, ALTER or DROP)
- Database events, such as login/logoff, errors, or startup/shutdown
Use of Triggers:
- Automatically generate derived column values
- Provide auditing
- Prevent invalid transactions
- Gather statistics on table access
- Modify table data when DML statements are issued against views
- Enforce complex security authorizations
- Enforce referential integrity
- Enforce complex business rules
- Provide transparent event logging
- Publish information about database events, user events, and SQL statements to subscribing applications
Types of Triggers
- Row Triggers and Statement Triggers
- BEFORE and AFTER Triggers
- INSTEAD OF Triggers
- Triggers on System Events and User Events
Row Triggers:
A row trigger is fired each time the table is affected by the triggering statement.
For example,
If an UPDATE statement updates multiple rows of a table, then row trigger is fired for each row affected by the UPDATE statement. If a triggering statement affects zero rows, then trigger won’t run.
Statement Triggers:
A statement trigger is fired only once when the table is affected by the triggering statement, regardless of the number of rows affected the triggering statement. Even if zero rows are affected, the trigger will run.
BEFORE Triggers:
BEFORE triggers run the trigger action before the triggering statement is run.
AFTER Triggers:
AFTER triggers run the trigger action after the triggering statement is run.
INSTEAD OF Triggers:
INSTEAD OF triggers provide a transparent way of modifying views that cannot be modified directly through DML statements.
These triggers are called INSTEAD OF triggers because, unlike other types of triggers, Oracle fires the trigger instead of executing the triggering statement.
You can write normal INSERT, UPDATE, and DELETE statements against the view and the INSTEAD OF trigger is fired to update the underlying tables appropriately. INSTEAD OF triggers are activated for each row of the view that gets modified.
Triggers on System Events and User Events
You can use triggers to publish information about database events to subscribers. Applications can subscribe to database events just as they subscribe to messages from other applications. These database events can include:
System events
- Database startup and shutdown
- Data Guard role transitions
- Server error message events
- User events
- User login and logoff
Triggers on system events can be defined at the database level or schema level. The DBMS_AQ package is one example of using database triggers to perform certain actions. For example, a database shutdown trigger is defined at the database level:
CREATE TRIGGER register_shutdown
ON DATABASE SHUTDOWN
BEGIN
...
DBMS_AQ.ENQUEUE(...);
...
END;
The syntax of Triggers Syntax for Creating a Trigger
CREATE [OR REPLACE ]
TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name] ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
BEGIN
--- sql statements
END;
Example 1 :
BEFORE UPDATE FOR EACH ROW
1) Create the ‘product’ and ‘product_price_history’ table
2) Create the price_history_trigger and execute it.
3) Let’s update the price of a product.
Once the above update query is executed, the trigger fires and updates the ‘product_price_history’ table.
4)If you ROLLBACK the transaction before committing to the database, the data inserted to the table is also rolled back.
Example 2:
AFTER UPDATE FOR EACH ROW
1. Create Emp_log table.
2. Create the log_salary_increase trigger and execute it.
3. Update salary of an employee
4. Check Emg_log data.
Example 3:
Conditional Trigger Prints Salary Change Information
Example 4:
BEFORE UPDATE, Statement-Level:
This trigger will insert a record into the table ‘product_check’ before an SQL update statement is executed, at the statement level.
Example 5:
BEFORE UPDATE, Row Level:
This trigger will insert a record into the table ‘product_check’ before each row is updated.
Example 6:
AFTER UPDATE, Statement-Level:
This trigger will insert a record into the table ‘product_check’ after a SQL update statement is executed, at the statement level.
Example 7:
AFTER UPDATE, Row Level:
This trigger will insert a record into the table ‘product_check’ after each row is updated.
Now let’s execute an update statement on table product.
Let’s check the data in ‘product_check’ table to see the order in which the trigger is fired.
Example 7:
Trigger with REFERENCING Clause
creates a table with the same name as a correlation name, new, and then creates a trigger on that table. To avoid conflict between the table name and the correlation name, the trigger references the correlation name as Newest.
How To know Information about Triggers.
We can use the data dictionary view ‘USER_TRIGGERS’ to obtain information about any trigger.
This view stores information about the header and body of the trigger.
The above SQL query provides the header and body of the trigger ‘Before_Update_Stat_product’.
You can drop a trigger using the following command.