Triggers in PL/SQL – Types, Uses, & Examples

In PL/SQL, triggers serve as the database’s unnoticed guardians, silently watching every data movement and acting when certain events take place.

Triggers are essential tools for both database administrators and developers, as they ensure data integrity, automate processes, and apply complicated business logic. In this blog, we will explore the interesting world of triggers in PL/SQL, including its many types, applications, and restrictions.

What Are Triggers in PL/SQL?

Triggers are procedural code blocks in PL/SQL that occur automatically in response to particular events happening in an Oracle database. They occur with the execution of certain actions or operations, such as INSERT, UPDATE, or DELETE. 

In the database, triggers in PL/SQL automate some activities, enforce business rules, and preserve data integrity. They can also be programmed to run either before the triggering event (pre-trigger) or behind it (post-trigger).

Enroll in internshala’s data analyst course with placement and get a job guarantee.

Creating Triggers in PL SQL

You can create a trigger in PL SQL with the following syntax:

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)    
DECLARE
Declaration-statements  
BEGIN   
Executable-statements  
EXCEPTION  
Exception-handling-statements  
END;  

Here is what each statement means: 

• CREATE [OR REPLACE ] TRIGGER trigger_name: It creates or replaces an existing trigger with ‘trigger_name’.
• {BEFORE | AFTER | INSTEAD OF}: This statement specifies when the trigger would be executed. 
• {INSERT [OR] | UPDATE [OR] | DELETE}: This statement specifies the data manipulation language (DML) operation. 
• [OF col_name]: This highlights the column name that would be updated. 
• ON table_name: This statement specifies the table associated with the trigger. 
• [REFERENCING OLD AS o NEW AS n]: This allows the user to refer to new and old values for various DML statements, like INSERT, UPDATE, and DELETE.   
• [FOR EACH ROW]: This specifies the row-level trigger that would be executed for each row being affected. 
• WHEN (condition): This statement provides a condition applicable to the rows for which the trigger would fire.     
• DECLARE: This marks the beginning of the declaration section. Here, cursors, variables, and other items are declared.
• Declaration-statements: These are statements used to declare local variables, cursors, and other items that will be used within the trigger.  
• BEGIN: This marks the beginning of the executable section, where the main logic of the trigger is defined.
• Executable-statements: These statements define the actions to be performed when a trigger is fired. 
• EXCEPTION: This marks the beginning of the exception-handling section, where error handling logic is defined. 
• Exception-handling-statements: These are statements used to handle exceptions that may occur during the execution of the trigger. 
• END:  This marks the end of trigger definition.

Worried about a job guarantee? Enroll in the best data analytics course with placement and

PL SQL Trigger Example

Let us take an example of a table with employee records. We will create a trigger that updates an employee’s salary before and after promotion. 

Step 1: Create the Table and Insert Records

CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    employee_name VARCHAR2(100),
    salary NUMBER(10, 2),
    promotion_date DATE
);

INSERT INTO employees (employee_id, employee_name, salary, promotion_date)
VALUES (1, 'John Doe', 50000, NULL);

INSERT INTO employees (employee_id, employee_name, salary, promotion_date)
VALUES (2, 'Jane Smith', 60000, NULL);

The above-mentioned code will create the following table: 

employee_id	employee_name	salary	promotion_date
1	John Doe	50000	[original date and time]
2	Jane Smith	60000	[original date and time]

Step 2: Create the Trigger

The following trigger will automatically update an employee’s salary before and after promotion, assuming a 10% salary increase after promotion. 

CREATE OR REPLACE TRIGGER update_salary_on_promotion
BEFORE UPDATE OF promotion_date ON employees
FOR EACH ROW
BEGIN
    IF :NEW.promotion_date IS NOT NULL THEN
        -- Update the salary before promotion
        :OLD.salary := :OLD.salary;
        -- Update the salary after promotion
        :NEW.salary := :OLD.salary * 1.1; -- 10% increase after promotion
    END IF;
END;
/

Step 3: Execute a Query to Update the Promotion Date

UPDATE employees
SET promotion_date = SYSDATE
WHERE employee_id = 1;

Step 4: Display the Updated Record

Finally, let’s query the employees table to see the updated records:

SELECT * FROM employees;

Output:

You will get the following table as the output:

employee_id	employee_name	salary	promotion_date
1	John Doe	55000	[current date and time]
2	Jane Smith	60000	[original date and time]

In this output, the promotion date for the employee with employee_id=1 is updated to the current date and time, indicating promotion. The salary is updated to 55000 from 50000 after promotion, reflecting 10% increase as defined in the trigger. There is no change in the records of other employees. 

Types of Triggers in PL/SQL with Examples

Based on a variety of factors, triggers in PL/SQL may be divided into distinct categories. To help you understand, let’s go through each type of trigger in PL SQL with examples.

1. Row-Level Triggers

A row-level trigger occurs once for each row that a triggering event affects.

A. Before Row Triggers

This trigger occurs before the insertion, update, or deletion of a row. It may be used to change the values of the currently processed row. 

Example:

CREATE OR REPLACE TRIGGER before_insert_trigger
BEFORE INSERT ON student
FOR EACH ROW
BEGIN
  : NEW.created_date := SYSDATE; -- Set the created_date column to the current date and time
END;

Now, add the following row in the table ‘student’. 

INSERT INTO student (student_id, student_name) VALUES (1, ‘John Doe’);

The trigger will be applied and the output will be as follows: 

STUDENT_ID	STUDENT_NAME	CREATED_DATE
1	John Doe	2024-02-20 08:30:45 (or the current date and time)

B. After-Row Triggers

Following an INSERT, UPDATE, or DELETE operation on a row, this type of trigger occurs. It may be used to conduct actions based on the row’s modifications. 

Example:

CREATE OR REPLACE TRIGGER after_update_trigger
AFTER UPDATE ON student
FOR EACH ROW
BEGIN
  -- Log the update in the audit table
  INSERT INTO audit_table (student_id, action, action_date)
  VALUES (: OLD.student_id, 'UPDATE', SYSDATE);
END;

For this trigger to be applicable, update a row in the table ‘student’ using the following code: 

UPDATE student SET student_name = ‘Jane Doe’ WHERE student_id = 1;

Let’s assume that the ‘audit_table’ is empty. After the above-mentioned update operation, the trigger will fire, and a new row will be inserted in the empty table. 

Output: 

STUDENT_ID	ACTION	ACTION_DATE
1	UPDATE	2024-02-20 09:45:12 (or the current date and time)

C. Instead of Row Triggers

This trigger is used with views and fires instead of the view’s default DML actions. It enables you to create custom actions for DML operations. 

Example:

CREATE OR REPLACE TRIGGER instead_of_insert_trigger
INSTEAD OF INSERT ON view_name
FOR EACH ROW
BEGIN
  -- Perform custom actions instead of the default insert operation
  INSERT INTO actual_table (column1, column2)
  VALUES (:NEW.column1, :NEW.column2);
END;

Now, insert a row in the ‘view_name’ using the following code: 

INSERT INTO view_name (column1, column2) VALUES (‘John’, ‘Doe’);

Output: 

column1	column2
John	Doe

The trigger will intercept the operation and insert the values in the ‘actual_table’.

2. Statement-Level Triggers

No matter how many rows are impacted, a trigger event on a table always fires a statement-level trigger.

A. Before Statement Triggers

This trigger occurs before the execution of a SQL query. It can be used to take actions or validations before processing the statement. 

Example:

CREATE OR REPLACE TRIGGER before_statement_trigger
BEFORE INSERT ON student
BEGIN
  -- Perform some validation or action before the insert statement is executed
  IF : NEW.fees < 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Fees cannot be negative.');
  END IF;
END;

Output: 

If your insert value will have a negative value, the trigger will show the following result: 

Error: ORA-20001: Fees cannot be negative.

B. After Statement Triggers

Upon execution of a SQL statement, this trigger occurs. It can be used to conduct actions based on the statement’s overall outcome. 

Example:

CREATE OR REPLACE TRIGGER after_statement_trigger
AFTER INSERT OR DELETE ON student
BEGIN
  -- Update the student count in a separate table
  UPDATE student_count_table
  SET count = (SELECT COUNT(*) FROM student);
END;

Initially, let’s say the ‘student’ table has 10 rows. The ‘student_count_table’ will look like this: 

COUNT

10

Now, let’s insert a new row in the ‘student’ table with the following code: 

INSERT INTO student (student_id, student_name) VALUES (11, ‘Jane Smith’);

After insertion of the above row, the ‘after_statement_trigger’ is fired and it updates the ‘student_count_table’. Here’s the output: 

COUNT

11

3. Database-Level Triggers

No matter which user or application provides the statement, database triggers in PL SQL are specified on a table, saved in the corresponding database, and performed as a result of an INSERT, UPDATE, or DELETE statement being made against a table.

A. Startup Triggers

This trigger activates after the initialization of the database. It can be used to undertake setup activities or to carry out particular operations during startup. 

Example:

CREATE OR REPLACE TRIGGER startup_trigger
AFTER STARTUP ON DATABASE
BEGIN
  -- Perform some initialization tasks after the database startup
  DBMS_OUTPUT.PUT_LINE('Database has started.');
END;

B. Shutdown Triggers

This trigger starts when the database is shutting down. It can be used to undertake cleaning tasks or to carry out particular operations upon shutdown. 

Example:

CREATE OR REPLACE TRIGGER shutdown_trigger
BEFORE SHUTDOWN ON DATABASE
BEGIN
  -- Perform some cleanup tasks before the database shutdown
  DBMS_OUTPUT.PUT_LINE('Database is shutting down.');
END;

4. DDL Triggers

DDL (Data Definition Language) triggers are actions that occur in reaction to DDL statements, such as CREATE, ALTER, or DROP. It enables you to capture and control DDL activities in the database. 

Example:

CREATE OR REPLACE TRIGGER ddl_trigger
AFTER CREATE OR ALTER OR DROP ON DATABASE
BEGIN
  -- Log the DDL operation in the audit table
  INSERT INTO ddl_audit_table (ddl_operation, ddl_object, ddl_date)
VALUES (ORA_DICT_OBJ_TYPE, ORA_DICT_OBJ_NAME, SYSDATE);
END;

Now, let’s say you execute a DDL statement as the following: 

CREATE TABLE example_table (

    id NUMBER,

    name VARCHAR2(100)

);

This CREATE TABLE statement will trigger the ‘ddl_trigger’, which will insert a record into the ‘ddl_audit_table’ to log this DDL operation. 

Next, you have to query the ‘ddl_audit_table’:

SELECT * FROM ddl_audit_table;

The output will be as follows: 

DDL_OPERATION	DDL_OBJECT	DDL_DATE
TABLE	EXAMPLE_TABLE	2024-02-20 12:00:00

5. Instead of Triggers

This trigger is used with views and fires instead of the view’s usual DML actions. It enables you to create custom actions for DML activities. 

Example:

CREATE OR REPLACE TRIGGER instead_of_update_trigger
INSTEAD OF UPDATE ON view_name
BEGIN
  -- Perform custom actions instead of the default update operation
  UPDATE actual_table
  SET column1 = :NEW.column1,
      column2 = :NEW.column2
  WHERE id = :OLD.id;
END;

Let’s update a row in the ‘view_name’: 

UPDATE view_name

SET column1 = ‘NewValue1’, column2 = ‘NewValue2’

WHERE id = 1;

This update operation will be intercepted by the trigger ‘instead_of_update_trigger’, which will update the corresponding row in the actual_table.

To see the changes reflected in the ‘actual_table’, you can query it:

SELECT * FROM actual_table;

The output will show the updated values: 

ID	COLUMN 1	COLUMN2
1	NewValue1	NewValue2

6. Compound Triggers

To increase flexibility and effectiveness, this trigger combines row-level and statement-level triggers. It enables you to specify actions at various levels and phases of the triggering event. 

Example:

CREATE OR REPLACE TRIGGER compound_trigger
FOR INSERT ON student
COMPOUND TRIGGER

  BEFORE EACH ROW IS
  BEGIN
    -- Perform some action before each row is inserted
    DBMS_OUTPUT.PUT_LINE('Before row insert.');
  END BEFORE EACH ROW;

  AFTER STATEMENT IS
  BEGIN
    -- Perform some action after the insert statement is executed
    DBMS_OUTPUT.PUT_LINE('After statement.');
  END AFTER STATEMENT;

END compound_trigger;

Insert multiple row into the ‘student’ table using the following code: 

INSERT INTO student (student_id, student_name) VALUES (1, ‘John Doe’);

INSERT INTO student (student_id, student_name) VALUES (2, ‘Jane Smith’);

When you insert these rows, the compound trigger mentioned above will give the following output: 

Before row insert.Before row insert.After statement.

7. System Triggers

The Oracle database defines and invokes these triggers in response to specified system events.Server problems, log-in or log-off events, and particular user activities are examples of these events. The behavior and examples of system triggers are dependent on the precise event to which they are related.

For example,

CREATE OR REPLACE TRIGGER after_startup

AFTER STARTUP ON DATABASE

BEGIN

    DBMS_OUTPUT.PUT_LINE(‘Database startup event occurred’);

END;

/

Wherever the database is started, it will fire the above-mentioned trigger to give the following output. 

Database startup event occurred

8. Database Event Triggers

Events such as server faults, log-in or logoff events, or specific user activities cause these triggers to fire. It is possible to define custom actions to execute when these events occur. The behavior and examples of database event triggers vary depending on the event to which they are related.

For example,

CREATE OR REPLACE TRIGGER logon_audit_trigger

AFTER LOGON ON DATABASE

BEGIN

    INSERT INTO logon_audit (user_id, logon_time, user_ip)

    VALUES (USER, SYSDATE, SYS_CONTEXT(‘USERENV’, ‘IP_ADDRESS’));

END;

/

This database event trigger will fire after any user logs on the database. It will insert a record in the ‘logon_audit’ table with user ID, logon time, and user’s IP address. 

Why Use PLSQL Triggers?

Triggers in PL/SQL are used for a variety of applications. They are commonly employed for the following reasons:

• Triggers can be used to impose sophisticated integrity requirements or business rules that are incomprehensible using normal SQL constraints. They preserve data integrity by ensuring that data entered or edited in the database fits specified requirements.
• Triggers automate database processes by performing specified actions in response to predetermined triggers. They reduce the need for manual intervention while ensuring data uniformity and accuracy. A trigger, for example, can update associated data, produce audit logs, or compute derived values.
• Triggers can be used to track and log changes to the database’s data. They offer an audit trail by documenting who made the modification, what was modified, and when it was updated. This is useful for regulatory compliance, troubleshooting, and historical analysis.
• Database triggers enable the implementation of extensive business logic. They can contain complex computations, validations, or workflow operations that must be implemented consistently for data changes. It is easier to manage and ensure consistent execution of logic by centralizing it in triggers.
• When specific events occur, triggers enable cascading actions. It is possible to use triggers to automatically delete related entries from other tables upon the destruction of a record, ensuring data consistency and referential integrity.
• Triggers can transform data during insertion, updation, or deletion. You can use triggers to check and alter data values before saving them in the database.
• By managing data access and permissions, triggers can enforce security controls. They have the ability to prohibit or alter data modifications depending on certain circumstances, ensuring that only approved changes are permitted.
• Triggers enable database modification and extension beyond what is given by normal SQL procedures. They allow you to adapt the database’s behavior to unique requirements or business demands.

If you wish to learn more about triggers, you can consider taking an online SQL course.

Limitations of Triggers in PL/SQL

While PL/SQL triggers provide strong capabilities, there are several restrictions and factors to be aware of. These include the following.

• Triggers that are poorly designed or inefficient might have a detrimental influence on database performance. They run as part of the transaction and can incur extra costs, particularly if they require complicated logic or influence a large number of entries.
• A table can include several triggers, which, if not carefully managed, might result in cascading triggers. When one trigger affects data, another trigger fires, leading to an unexpected chain of trigger invocations.
• Triggers cannot call or initiate other triggers directly. Trigger layering, in which one trigger activates another, is not permitted. This constraint eliminates potentially endless loops or unintentional recursion in trigger execution.
• You cannot directly edit the data that caused the trigger to fire within a trigger. This restriction protects data integrity by preventing trigger-induced data inconsistency. If you need to make changes to the data, you may need to utilize alternative means, such as stored procedures.
• Triggers can add to the complexity of the database schema and logic. Their behavior may not be obvious, particularly in big and complicated datasets. To ensure adequate functioning and maintainability, careful design, documentation, and testing are essential.
• Triggers, which do not need direct user engagement, are often conducted automatically in response to predetermined events or procedures. If you need to ask the user for input or show messages while the trigger is running, you should think about other options.
• Certain DDL commands, like as TRUNCATE TABLE and ALTER TABLE, do not support triggers. This limits the ability of triggers to govern or track changes in the database structure.
• Triggers run as part of the transaction that sets them off. If a trigger fails or meets an issue, the entire transaction may be impacted, resulting in rollbacks or incorrect data states.

Conclusion

Triggers are more than simple lines of code in PL/SQL; they are the invisible forces that assure data integrity, automate operations, and enforce business rules within the database. Database triggers in PL/SQL play an important role in assuring the seamless operation and stability of your database system, from verifying data to preserving audit trails. Understanding the various sorts of triggers, leveraging their strengths, and taking into account their restrictions allows you to use their power to construct powerful and efficient database solutions.

Did you find this blog helpful in understanding triggers? Let us know in the comments below. Preparing for a job interview? Check out this list of top PL SQL interview questions to excel at your interview.

Preparing for a job interview? Check out this list of top PL SQL interview questions to excel at your interview.

FAQs