"Topic-PL/SQL Triggers"

PL/SQL triggers are procedural code blocks or stored programs that are automatically executed or fired when some specific database events occur, such as insertions, updates, or deletions on a table. Triggers can be defined to run before or after the event occurs, allowing us to enforce business rules, maintain data integrity, or perform additional actions based on the event.

Types of TRIGGERS:

Triggers can be classified based on the following parameters:

Classification based on the timing of their execution in relation to the triggering event:

1. #Before_Triggers: These triggers are executed before the triggering event occurs. They are commonly used for data validation or modification before the actual operation takes place. Before triggers can be used with INSERT, UPDATE, and DELETE statements.
2. #After_Triggers: After triggers are executed after the triggering event completes. They are often used for auditing, logging, or performing additional actions based on the outcome of the triggering event. After triggers can be used with INSERT, UPDATE, DELETE, and SELECT statements.
3. #Instead_of_Triggers: Instead of triggers are primarily used with views. These triggers are the special type of trigger. It is used only in DML triggers. It is used when any DML event is going to occur on the complex view.

Classification based on the Level:

1. #Row_level_Triggers: Row-level triggers are executed once for each row affected by the triggering event. They can access and modify column values using the :NEW and :OLD qualifiers. Row-level triggers are specified using the FOR EACH ROW clause. (only for DML)
2. #Statement_level_Triggers: Statement-level triggers are executed once for each triggering event, regardless of the number of rows affected. They cannot access or modify individual column values. Statement-level triggers do not include the FOR EACH ROW clause.

Classification based on the Event:

1. #DML_Triggers: These triggers are fired in response to Data Manipulation Language (DML) statements such as INSERT, UPDATE, and DELETE. They allow us to perform actions before or after the data modification occurs.
2. #DDL_Triggers: DDL triggers are triggered by Data Definition Language (DDL) statements, which are used to CREATE, ALTER, or DROP database objects such as tables, views, or procedures. DDL triggers enable us to enforce data integrity rules or perform administrative tasks.
3. #Database_Triggers: Database triggers are associated with specific database-wide events, such as STARTUP or SHUTDOWN of the database, LOGON or LOGOFF of a user, or errors occurring in the database. They provide the ability to respond to these events and take appropriate actions.
4. #System_Triggers: System triggers are executed when certain system-level events occur, such as a server error, a change in database configuration, or resource-related events. They allow us to handle system-specific events and implement custom logic.

Benefits of TRIGGERS:

• Enforce data integrity and maintain consistency: Triggers enable us to enforce data integrity by automatically performing validation checks or implementing business rules before or after data modifications. This helps maintain the consistency and accuracy of the data.
• Automate actions and tasks based on events: Triggers automate actions and tasks based on specific events or data modifications. They can automatically perform additional operations such as updating related tables, generating audit logs, or sending notifications. This reduces manual effort and ensures consistent actions.
• Enhance security with access controls and restrictions: Triggers can be used to enhance security by implementing access controls or restrictions. They allow us to perform authentication checks, enforce row-level security, or block unauthorized operations, improving data protection.
• Promote code reusability for modular development: Triggers can be reusable components that can be applied to multiple tables or events. This promotes code reusability, reduces code duplication, and simplifies maintenance efforts.
• Maintaining consistency across related tables: Triggers help maintain consistency across related tables by automatically updating or synchronizing data in response to modifications in one table. This ensures data integrity and eliminates the risk of inconsistencies.

SYNTAX for creating a trigger:

CREATE [OR REPLACE] TRIGGER trigger_name

{BEFORE | AFTER | INSTEAD OF} -- Trigger Timing

{INSERT | UPDATE | DELETE} -- Event

ON table_name

[FOR EACH ROW] -- Row Level

[ WHEN condition] -- conditional clause for trigger to get execute

DECLARE

  -- Declaration section for variables, constants, etc.

BEGIN

  -- Trigger logic and executable statements

EXCEPTION

  -- Exception handling statements

END;

/

The WHEN condition is an optional clause that allows us to specify a condition that must evaluate to true for the trigger to be executed. This condition can include comparisons, logical operators, and other expressions.

With this syntax, we can create a trigger that is triggered before or after an insert, update, or delete operation on a specific table. The trigger can be defined to execute for each affected row using the FOR EACH ROW clause. We can also include a WHEN condition to further filter when the trigger should be executed.

Inside the trigger, we can declare variables, constants, and other declarations in the declaration section. The trigger logic and executable statements are placed between the BEGIN and END; block. Exception handling statements can be included to handle any exceptions that may occur during the trigger execution.

Finally, the / delimiter is used to mark the end of the trigger definition.

Here's an example of a trigger that calculates and updates the total salary of an employee whenever a new salary record is inserted into the "employees" table:

CREATE OR REPLACE TRIGGER calculate_total_salary

AFTER INSERT ON employees

FOR EACH ROW

DECLARE

total_salary NUMBER;

BEGIN

-- Retrieve the current total salary of the employee

SELECT SUM(salary) INTO total_salary

FROM employees

WHERE employee_id = :new.employee_id;

-- Update the total_salary column for the employee

UPDATE employees

SET total_salary = total_salary

WHERE employee_id = :new.employee_id;

-- Commit the transaction

COMMIT;

EXCEPTION

-- Handle exceptions, if any

WHEN OTHERS THEN

-- Log the error or perform error handling

DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);

END;

/

In this example:

• The trigger is named "calculate_total_salary".
• It is an "AFTER INSERT" trigger, meaning it will execute after an insert operation on the "employees" table.
• The trigger is defined to execute for each row using the "FOR EACH ROW" clause.
• The trigger logic calculates the total salary of the employee by summing up all salary records for that employee.
• The total_salary is then updated in the "employees" table for the corresponding employee.
• Exception handling is included using the "WHEN OTHERS" clause, which catches any exceptions that may occur during the trigger execution and outputs the error message using DBMS_OUTPUT.PUT_LINE.
• Finally, the trigger definition is terminated with the "/" delimiter.

:old and :new are pseudorecords referred to access row level data when using row level trigger:

:old - refers to OLD VALUE

:new - refers to NEW VALUE

For Below operation, respective old and new values:

1. INSERT- :old.value= NULL, :new value= post insert value
2. DELETE- :old.value= Pre Delete value, :new value= null
3. UPDATE- :old.value= Pre update value, :new value= Post Update value

DDL triggers:

Using the Data Definition Language (DDL) triggers, the DBA can automatically track all changes to the database, including changes to tables, indexes and constraints. The data from this trigger is especially useful for change control for the DBA.

Syntax:

CREATE [OR REPLACE] TRIGGER trigger_name

{BEFORE | AFTER} DDL_event

ON {DATABASE | SCHEMA}

[WHEN condition]

DECLARE

-- Declaration section for variables, constants, etc.

BEGIN

-- Trigger logic and executable statements

EXCEPTION

-- Exception handling statements

END;

/

• CREATE [OR REPLACE] TRIGGER: This statement is used to create a new DDL trigger or replace an existing DDL trigger.
• trigger_name: Specifies the name of the trigger.
• {BEFORE | AFTER}: Indicates whether the trigger fires before or after the DDL event.
• DDL_event: Specifies the DDL event that triggers the execution of the trigger. DDL events include CREATE, ALTER, and DROP statements for various database objects.
• ON SCHEMA: Specifies that the trigger is associated with the DATABASE or schema-level DDL events.
• [WHEN condition]: Optional clause that allows us to specify a condition that must evaluate to true for the trigger to be executed.
• DECLARE: The start of the declaration section where variables, constants, and other declarations are defined.
• BEGIN: The start of the trigger logic and executable statements.
• EXCEPTION: The start of the exception handling section where exception handling statements are defined.
• END;: Marks the end of the trigger logic and exception handling.
• /: A delimiter that indicates the end of the trigger definition.

In Summary,

PL/SQL triggers are an essential feature of Oracle databases that enable the automation and customization of actions in response to specific events or conditions. They offer a range of benefits, including enforcing data integrity, implementing business rules, maintaining audit trails, and extending the functionality of the database. Triggers can be classified based on the event they are associated with, such as DML or DDL triggers, and can be defined to execute before or after the event. They provide a flexible and powerful tool for managing and controlling database operations, enhancing the overall functionality and reliability of the database system.