Database development with PL/SQL INSY 8311

Instructor:
▪ Eric Maniraguha | eric.maniraguha@auca.ac.rw | LinkedIn Profile

6h00 pm – 9h50 pm
▪ Monday A -G207
▪ Tuesday B-G204
▪ Wednesday E-106 April 2025
▪ Thursday F-G307 1
 Database development with PL/SQL

Reference reading
▪ Difference between Cursor and Trigger in DBMS
▪ Difference between Cursor and Trigger in PLSQL
▪ 9 Using Triggers
▪ CREATE TRIGGER
▪ Oracle PL/SQL Trigger: A Comprehensive Guide
▪ Auditing employee table changes with PL/SQL Trigger
▪ Oracle PL/SQL

Lecture 08 – PLSQL Triggers and Audit Mechanisms 2

 The objectives of triggers in PL/SQL

What is a Trigger in PL/SQL?

A trigger in PL/SQL is a predefined program or block of code that is automatically executed (or "fired") when a specific event occurs in the database. It is stored in the database and
is triggered by actions such as INSERT, UPDATE, DELETE, or DDL operations on a table, view, schema, or database.

Key Features of Triggers:

▪Automatic Execution: Triggers execute automatically when the specified event occurs.
▪Data Integrity: They help enforce business rules and maintain consistency in the database.
▪Error Handling: Triggers can validate data before changes are made.
▪Event-driven: They respond to specific actions like INSERT, UPDATE, DELETE, or system events like LOGON, SHUTDOWN.

Types of Triggers in PL/SQL:

▪DML Triggers – Fired on INSERT, UPDATE, DELETE operations.

▪DDL Triggers – Fired on CREATE, ALTER, DROP statements.
▪Database Triggers – Fired on system events like LOGON, LOGOFF, STARTUP, SHUTDOWN.
▪INSTEAD OF Triggers – Used for views that do not allow direct modifications.

When to Use Triggers?

•Auditing changes (e.g., tracking salary modifications, order status updates).

•Enforcing business rules (e.g., preventing unauthorized deletions).
•Automatically updating related tables (e.g., updating stock levels when an order is placed).

3
 Oracle – PLSQL Database Triggers

1. In Oracle PL/SQL, a trigger is a named block of PL/SQL code that is automatically executed (or "fired") in response to specific events on a particular table, view,
schema, or database. Triggers can enforce business rules, maintain audit trails, or perform automated actions.

2. A Trigger is a PL/SQL block that executes implicitly in response to a specific database event (e.g., INSERT,
UPDATE, DELETE) on a table or view. It is planned for a particular event (such as data changes) and timing
(e.g., BEFORE or AFTER the event).

Characteristics:

▪Executed Implicitly: Trigger fires automatically when the specified event occurs.
▪Event-Driven: Associated with DML operations (e.g., INSERT, UPDATE, DELETE).
▪Centralized Actions: Performs centralized and related actions to enforce data integrity, auditing,
or automation.
▪Complexity: Excessive use of triggers can lead to complex interdependencies between database
objects.

Real-world Example of Trigger Usage:

▪Auditing: Automatically log details of changes (e.g., who modified data, when, and what
changed) into a separate audit table.
▪Data Validation: Ensure specific business rules (e.g., salary cannot exceed a certain threshold)
are enforced before inserting or updating a row.
▪Cascading Updates: Update related tables to maintain data integrity when changes are made. Source Image: https://csveda.com/pl-sql-trigger-basics-uses/

Triggers are a powerful feature in relational databases like Oracle, MySQL, or PostgreSQL, allowing automation and ensuring consistency
within the database. However, overuse can lead to performance overhead, so they should be employed carefully.
4
 PL/SQL Triggers – Advantages

Automation: Automates complex calculations and

validations.

Data Integrity: Maintains consistency between

tables (e.g., referential integrity).

Logging and Auditing: Tracks changes for insights

into data usage patterns.

Real-time Synchronization: Ensures data

consistency across multiple tables.

Security Enforcement: Prevents invalid

transactions and restricts certain actions.

Source Image: https://www.guru99.com/triggers-pl-sql.html

5
 Disadvantage Description Impact
Can slow down database performance,
Performance Triggers add processing time to INSERT,
especially for high-traffic tables or large
Overhead UPDATE, or DELETE operations.
datasets.

Debugging and Triggers execute implicitly, making them Difficult for developers to track issues or
Maintenance hard to debug and trace. understand the flow of data modifications.

PL/SQL Triggers –
Causes confusion and reduces
Business logic in triggers is not
Hidden Logic transparency, leading to unexpected
immediately visible in application code.
behavior.

Disadvantages Increased
Complexity
Triggers can cause unintended cascading
effects (e.g., one trigger firing another).
Creates complex dependencies, making the
database harder to manage.

Requires additional methods (e.g., stored

Triggers are bound to database events and
Limited Use Cases procedures or application logic) for more
cannot handle all types of application logic.
complex scenarios.
Poorly designed triggers may invoke Can lead to infinite loops, database
Recursive Execution
themselves or other triggers recursively. crashes, or data corruption.
Trigger syntax and behavior vary across Challenges arise when migrating to a
Portability Issues
database platforms. different database system.
Triggers execute in the background, Ensuring expected behavior in all scenarios
Difficulty in Testing
making them hard to test independently. requires extensive testing.
Blurs the separation between application
Over-reliance on Developers may use triggers for tasks
and database logic, complicating system
Triggers better handled at the application layer.
maintenance.

6
 Triggers Classification: Application & Database Trigger

CREATE OR REPLACE TRIGGER check_salary

-- The trigger fires before an INSERT operation occurs on the 'employees' table
BEFORE INSERT ON employees
-- The trigger executes for each row affected by the INSERT statement
FOR EACH ROW
BEGIN
Database Triggers provide powerful tools to
automate actions directly in the database layer, -- Check if the new salary being inserted is less than 1000
ensuring data consistency and integrity through DML IF :NEW.salary < 1000 THEN
operations, event monitoring, and system RAISE_APPLICATION_ERROR(-20001, 'Salary must be above 1000');
management. END IF;
END;

// Java pseudo code example to demonstrate application trigger

public void onSalaryInsert(Employee emp) {
if (emp.getSalary() < 1000) {
throw new IllegalArgumentException("Salary must be greater than or
equal to 1000");
Application Triggers are used within the }
application's business logic and user interface layer // Code to insert employee to the database
to respond to user actions or system events, }
ensuring that business rules are applied
appropriately before or after database interactions.

Types:
▪ UI Event Handlers: Trigger actions when a user interacts with the interface (e.g., button clicks).
▪ Business Logic Triggers: Trigger specific actions or calculations based on certain conditions.
▪ Database Interactions: Trigger actions after database operations (e.g., after a record is updated).
▪ Use Cases: Business rule enforcement, user interaction handling, database operation responses.
7
 Trigger Syntax, Behavior of Database Triggers

-- Creates or replaces a trigger to execute logic before/after specific table events.

CREATE [OR REPLACE] TRIGGER trigger_name
Row vs. ▪Row-level: Executes once for each
-- Determines trigger timing relative to the event (BEFORE, AFTER, or INSTEAD OF).
Statement Level: row affected by the operation (use
{BEFORE | AFTER | INSTEAD OF}
Defines how often FOR EACH ROW).
-- Specifies the triggering events (INSERT, UPDATE, DELETE). the trigger executes: ▪Statement-level: Executes once
{INSERT [OR] | UPDATE [OR] | DELETE} for the entire operation, regardless
of the number of rows affected.
-- Monitors specific columns for UPDATE events (optional).
[OF col_name]

-- Defines the table or view the trigger applies to. Trigger Event:
ON table_name Specifies what DML
operation triggers
-- References old and new row values for processing (optional). the execution of the
[REFERENCING OLD AS o NEW AS n] trigger. This can be:
-- Executes the trigger for each affected row (optional).
[FOR EACH ROW]
▪INSERT Trigger Timing:
-- Executes the trigger conditionally if the specified condition is true. ▪UPDATE Specifies when the trigger fires
WHEN (condition) ▪DELETE in relation to the triggering
event. This can be:
BEGIN
-- Define the logic executed when the trigger fires. ▪BEFORE: Trigger fires before the
NULL; -- Replace with actual trigger logic. operation is executed.
EXCEPTION ▪AFTER: Trigger fires after the operation
-- Define logic for handling errors during trigger. execution. is executed.
NULL; -- Replace with actual error handling logic ▪INSTEAD OF: Trigger replaces the
END;
triggering event (used primarily for
/
views).
8
 Where,

CREATE [OR REPLACE] TRIGGER trigger_name − Creates or replaces an existing trigger with When to Use Triggers
the trigger_name.
1. DML Triggers:
Use DML Triggers to automate actions in response to changes in data.
{BEFORE | AFTER | INSTEAD OF} − This specifies when the trigger will be executed. The INSTEAD These are ideal for:
OF clause is used for creating trigger on a view. ▪ Logging changes to tables (e.g., tracking updates or deletions).
▪ Enforcing business rules and integrity constraints automatically when
INSERT, UPDATE, or DELETE operations occur.
{INSERT [OR] | UPDATE [OR] | DELETE} − This specifies the DML operation.
2. INSTEAD OF Triggers:
Use INSTEAD OF Triggers to enable INSERT, UPDATE, or DELETE
operations on views that are otherwise non-updatable. These triggers
[OF col_name] − This specifies the column name that will be updated. allow you to define custom logic for handling DML operations on such
views.

3. DDL Triggers:
[ON table_name] − This specifies the name of the table associated with the trigger. Use DDL Triggers to monitor or control schema-level changes. These
triggers are helpful for:
▪ Auditing schema modifications (e.g., DROP, ALTER, or CREATE
[REFERENCING OLD AS o NEW AS n] − This allows you to refer new and old values for various DML operations).
statements, such as INSERT, UPDATE, and DELETE. ▪ Restricting unauthorized schema changes at the database or schema
level.
[FOR EACH ROW] − This specifies a row-level trigger, i.e., the trigger will be executed for each row
being affected. Otherwise the trigger will execute just once when the SQL statement is executed, which is
called a table level trigger. Caution with Triggers
▪ Avoid excessive use of triggers to prevent complex interdependencies.
▪ Excessive use can degrade performance and make debugging difficult.
WHEN (condition) − This provides a condition for rows for which the trigger would fire. This clause is ▪ Disable or drop triggers temporarily when troubleshooting or performing
valid only for row-level triggers.
bulk operations.
9
 Types of Database Triggers

DML Trigger (Data DDL Trigger (Data

System Database
Manipulation Definition Language Instead of Trigger Compound Triggers
Event Triggers
Language Trigger) Trigger)

▪Description: Activated by ▪Description: Fired by events like ▪Description: Responds to internal ▪Description: Replaces the default ▪Description: Combines multiple
INSERT, UPDATE, or DELETE CREATE, ALTER, or DROP that database events (e.g., action of a triggering event with timing points (before and after) in
operations. change database structures. logon/logoff, startup/shutdown). custom logic. one trigger, allowing row-level and
▪Use Case: Enforces business rules ▪Use Case: Audits schema changes ▪Use Case: Monitors system ▪Use Case: Useful for views to statement-level logic.
and maintains audit trails. and enforces security policies. activity and enforces security. handle updates or inserts. ▪Use Case: Organizes complex
logic in Oracle databases and
avoids conflicts between multiple
triggers.

Summary
These triggers help automate actions, enforce rules, and maintain data integrity in database operations.

10
 Row-Level Trigger

Row-Level Trigger in SQL

A row-level trigger is a type of trigger that is fired for each row affected by a Data Manipulation Language (DML) operation such as INSERT, UPDATE, or DELETE. It executes once for
each row that is modified, and it allows you to perform actions based on the data in the individual rows before or after the operation.
Row-level triggers are defined using the FOR EACH ROW clause, and they have access to the :NEW and :OLD pseudorecords, which store the values of the columns in the affected
rows.

Structure of a Row-Level Trigger

CREATE OR REPLACE TRIGGER trigger_name

{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name ▪ BEFORE: Executes before the DML operation (INSERT, UPDATE, or DELETE).
FOR EACH ROW ▪ AFTER: Executes after the DML operation.
DECLARE ▪ INSERT, UPDATE, DELETE: Specifies the type of DML operation that will fire the trigger.
-- Declare any local variables if needed ▪ :NEW: Refers to the new value in the row during INSERT or UPDATE. For DELETE, this is NULL.
BEGIN ▪ :OLD: Refers to the old value in the row during UPDATE or DELETE. For INSERT, this is NULL.
-- Trigger logic goes here
-- You can access :NEW and :OLD for the values of columns
END;

11
 BEFORE vs AFTER: What’s the difference in this context?

Aspect BEFORE Trigger AFTER Trigger

When it fires Just before the row is updated Just after the row is updated

Can access :OLD? Yes Yes

Can access :NEW? Yes and can modify it Yes (read-only; cannot modify :NEW)

Use case Validation, transformation, custom logic Auditing, logging, sending notifications

12
 Example of a Row-Level Trigger: Logging Salary Changes

In this example, we will create a row-level trigger that logs every salary change to an audit table.

Trigger and Audit Table Definition

Explanation:
▪ Trigger Event: AFTER UPDATE -This trigger is fired after an UPDATE operation on the
employees table.
-- Create an audit table to store salary changes ▪ Row-Level: FOR EACH ROW -This trigger fires once for each row affected by the
CREATE TABLE salary_audit ( UPDATE operation.
employee_id NUMBER, ▪ Audit Action: Inside the trigger body, it logs the employee's ID, old salary, new salary,
old_salary NUMBER, and the date of the change into the salary_audit table using the INSERT statement.
new_salary NUMBER,
change_date DATE Behavior:
); ▪ After Update: After each salary change, the trigger inserts a record into the salary_audit
table. This allows you to keep track of salary changes over time.
-- Create the row-level trigger
CREATE OR REPLACE TRIGGER log_salary_changes
AFTER UPDATE ON employees
FOR EACH ROW Summary
WHEN (OLD.salary != NEW.salary)
BEGIN ▪ Row-level triggers execute once for each affected row in a DML operation.
-- Insert a record into the salary_audit table whenever salary is updated ▪ FOR EACH ROW is used to specify row-level behavior.
INSERT INTO salary_audit (employee_id, old_salary, new_salary, change_date) ▪ The :NEW and :OLD pseudorecords allow you to reference the new and old values of the
VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE); columns being modified.
END; ▪ They are useful for tasks like validation, logging, or enforcing business rules on a row-by-
row basis.

By using row-level triggers effectively, you can ensure that your database enforces business
rules and data integrity in a transparent and automated way. However, it's important to
balance their use with performance considerations.
13
 Row-Level Trigger

What This Trigger Does

Output
▪ Event: BEFORE UPDATE OF SALARY – fires before a salary update on the EMPLOYEES table.
▪ Level: FOR EACH ROW – row-level trigger (runs once per updated row).
▪ Filter: WHEN (NEW.ROLE_ID <> 1) – skips employees with ROLE_ID = 1, e.g., "President".
▪ Logic:
o Computes salary difference.
o Logs output to the console (via DBMS_OUTPUT), which is helpful for debugging or
audit-like feedback during development.

-- Step 1: Enable SERVEROUTPUT

SET SERVEROUTPUT ON SIZE UNLIMITED;

CREATE OR REPLACE TRIGGER log_salary_changes

BEFORE UPDATE OF SALARY ON EMPLOYEES
FOR EACH ROW
WHEN (NEW.ROLE_ID <> 1)
DECLARE
sal_diff NUMBER;
BEGIN
-- Calculate the salary difference
sal_diff := :NEW.SALARY - :OLD.SALARY;

-- Output employee name and salary details

DBMS_OUTPUT.PUT_LINE('Employee: ' || :NEW.LAST_NAME);
DBMS_OUTPUT.PUT_LINE('Old salary = ' || :OLD.SALARY || ', New salary = ' || :NEW.SALARY);
DBMS_OUTPUT.PUT_LINE('Difference: ' || sal_diff);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error in log_salary_changes trigger: ' || SQLERRM);
-- Optionally log the error to a table
-- INSERT INTO ERROR_LOG VALUES (SYSDATE, 'log_salary_changes', SQLERRM);
RAISE; -- Re-raise the exception to prevent the update if desired
END;
/
14
 Possible Enhancements (Optional)
Then modify the trigger: This
1. Log to a Table Instead of Using DBMS_OUTPUT
For persistent tracking: way, you maintain a full audit
trail in a separate table.
CREATE TABLE SALARY_LOG (
CREATE OR REPLACE TRIGGER log_salary_changes
EMP_ID NUMBER,
BEFORE UPDATE OF SALARY ON EMPLOYEES
LAST_NAME VARCHAR2(100),
FOR EACH ROW
OLD_SALARY NUMBER,
WHEN (NEW.ROLE_ID <> 1)
NEW_SALARY NUMBER,
BEGIN
SAL_DIFF NUMBER,
INSERT INTO SALARY_LOG (
CHANGED_AT DATE
EMP_ID,
);

Row-Level Trigger –
LAST_NAME,
OLD_SALARY,
NEW_SALARY,

Optimized
SAL_DIFF,
CHANGED_AT
)
VALUES (
:NEW.EMPLOYEE_ID,
:NEW.LAST_NAME,
:OLD.SALARY,
Output
:NEW.SALARY,
:NEW.SALARY - :OLD.SALARY,
SYSDATE
);
END;
/

15
 Table Auditing using DML Triggers in Oracle PL/SQL

Auditing Using Triggers vs Auditing Vault: A Detailed Comparison

Both triggers and auditing vaults are tools used to track and monitor database activities, but they serve different purposes and operate at distinct levels. Here's a
detailed breakdown:

Feature Auditing Using Triggers Auditing Vault

Scope Table/View-specific DML operations Comprehensive (DML, DDL, and system events)

Performance Impact Can affect application performance Minimal impact with optimized configurations

Ease of Implementation Requires coding and testing for each trigger Requires configuration but no coding

Customizability Fully customizable Limited to predefined templates and policies

Tamper Resistance Can be bypassed by disabling triggers Logs are secured and tamper-proof

Cost No additional cost beyond database licensing Often part of enterprise solutions with extra fees

Regulatory Compliance Limited (manual efforts required) Designed for compliance with built-in tools
16
 I. Scenario Question: Banking Context DDL Trigger

Scenario-Based Question

In a bank's database system, schema-level changes such as creating or altering tables for customer records, transaction logs, or account details must be strictly monitored
for compliance and security purposes. You are tasked with implementing a mechanism to track and log all DDL (Data Definition Language) operations like CREATE,
ALTER, and DROP. This ensures that unauthorized or accidental modifications to critical database structures are detected and auditable.

Objectives:

1. Create an Audit Table

Design a table that logs schema-level events, capturing:
▪ Event type (CREATE, ALTER, DROP).
▪ Object name (e.g., customer_accounts, transaction_history).
▪ User performing the operation (e.g., DB_ADMIN).
▪ Timestamp of the change.
2. Write a DDL Trigger
Implement a trigger that:
▪ Fires automatically after any CREATE, ALTER, or DROP operation on the database.
▪ Inserts event details into the audit table.
3. Test the Solution Example Use Case:
Perform test operations relevant to a bank:
▪ Create a new table for storing loan applications (CREATE TABLE loan_applications...). ▪ A bank's database administrator creates a new table for storing
▪ Alter an existing table to add a column for interest rates (ALTER TABLE...). customer feedback.
▪ Drop an obsolete table for temporary data storage (DROP TABLE temp_data...). ▪ The trigger logs this CREATE operation into the audit table, noting the
4. Validate the Implementation table name, the admin’s username, and the timestamp.
Query the audit log to ensure the trigger: ▪ If unauthorized schema changes occur, the audit log provides a
▪ Captures the operation type and object name (e.g., CREATE TABLE loan_applications). detailed trail for investigation and accountability.
▪ Logs the user performing the operation and the time.

17
 Solution DDL Trigger: Banque Scenario

Step 1: Create the Audit Table Validate: Query the audit log: Test Case 2: Altering a Table
The audit table will store details of all CREATE, ALTER, and DROP operations. Execute the following command:
SELECT * FROM audit_ddl_log;
CREATE TABLE audit_ddl_log ( ALTER TABLE customer_feedback ADD feedback_date DATE;
id NUMBER GENERATED BY DEFAULT AS IDENTITY,
event_type VARCHAR2(50),
object_name VARCHAR2(100), Test Case 3: Dropping a Table
operation_user VARCHAR2(50), Execute the following command:
DROP TABLE customer_feedback;
timestamp DATE Validate: Query the audit log:
);
SELECT * FROM audit_ddl_log
Step 2: Create the DDL Trigger ORDER BY TIMESTAMP DESC;
This trigger will log relevant details whenever a DDL operation occurs.
Expected Output
CREATE OR REPLACE TRIGGER log_ddl_operations
AFTER CREATE OR ALTER OR DROP ON DATABASE
BEGIN
INSERT INTO audit_ddl_log (event_type, object_name, operation_user, timestamp)
VALUES (
ORA_SYSEVENT, -- Captures the type of event (e.g., CREATE, ALTER, DROP)
ORA_DICT_OBJ_NAME, -- Captures the affected object (e.g., table name)
SYS_CONTEXT('USERENV', 'SESSION_USER'), -- Captures the user performing the operation
SYSDATE -- Captures the timestamp of the operation
);
END;
/

Step 3. Testing
Test Case 1: Creating a Table
Execute the following command:

CREATE TABLE customer_feedback (

feedback_id NUMBER,
feedback_text VARCHAR2(255)
18
);
 Solution Summary - DDL Trigger: Banque Scenario

Summary of the Solution

1. Audit Table: Stores details about schema changes for accountability.

2. DDL Trigger: Automatically logs all CREATE, ALTER, and DROP operations.
3. Testing: Verified the trigger by performing sample DDL operations and checking the audit
log.

This implementation provides a robust mechanism to monitor and log critical schema changes in a
bank’s database, ensuring security and compliance.

Note:

▪ You do not manually call a trigger. Instead, it gets triggered automatically when the
defined DML operation (INSERT, UPDATE, DELETE) happens on the specified table (in this
case, EMPLOYEES).
▪ To test it, simply perform the corresponding DML operation (insert, update, or delete) on the
EMPLOYEES table.
▪ To see the output of the trigger, ensure DBMS_OUTPUT is enabled and perform the
operations.

19
 II. Advanced Trigger Creation Assignment - Salary Change
Auditing

Scenario-Based Question :
You are a database developer managing the ICTCHEMBERADMIN.EMPLOYEES table. The HR department wants to track and audit every change made to employee salaries. This is
to ensure transparency and accountability whenever salary updates occur.

Your Task:

Create a BEFORE UPDATE Trigger on the EMPLOYEES table that:

▪ Detects changes only when the salary is modified.
▪ Inserts a record into a new audit table called SALARY_AUDIT every time an employee’s salary is updated.

The inserted record in SALARY_AUDIT should capture the following information:

▪ EMPLOYEE_ID – The ID of the employee whose salary was changed.
▪ OLD_SALARY – The salary value before the update.
▪ NEW_SALARY – The salary value after the update.
▪ CHANGE_DATE – The date and time when the change occurred (SYSDATE).
▪ USERNAME – The database user who made the change (USER).
▪ SESSION_ID – The session ID of the user who made the change (SYS_CONTEXT('USERENV', 'SESSIONID’)).

Additionally
▪ Create a stored procedure to perform a salary update that:
o Accepts employee_id and new_salary as input parameters.
o Performs the update to the EMPLOYEES table.
o Includes exception handling to raise an error when the employee_id does not exist or when any unexpected error occurs.

20
 /*
Step 1: Create the SALARY_AUDIT Table
*/
--Step 1: Create the SALARY_AUDIT Table
CREATE TABLE salary_audit (
employee_id NUMBER(6), -- Employee's ID
old_salary NUMBER(8,2), -- Old salary value
new_salary NUMBER(8,2), -- New salary value
change_date DATE, -- Date (stores date and time)
timestamp_change TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Timestamp for change with default value
username VARCHAR2(30), -- Username of the person making the change
session_id VARCHAR2(30) -- Session ID of the user making the change
);

Solution approach –
Salary Audit
Step 2: Create the Trigger Deliverables:
▪Name the trigger: TRG_SALARY_AUDIT ▪SQL script for creating the SALARY_AUDIT table
▪It should fire only when the salary is actually ▪SQL script for the TRG_SALARY_AUDIT trigger
changed ▪A brief explanation (2–4 lines) of how your trigger works
▪Type of trigger:
BEFORE UPDATE ON EMPLOYEES FOR EACH ROW
▪Inside the trigger body, compare :OLD.salary and
:NEW.salary
▪If they are different, insert a new record into
SALARY_AUDIT with the required fields

21
 CREATE OR REPLACE TRIGGER trg_salary_audit Step 3: Create the Trigger
BEFORE UPDATE ON employees
FOR EACH ROW
WHEN (OLD.salary != NEW.salary) -- Only fire if salary is being changed
BEGIN
BEGIN
-- Insert the salary change into the audit table
INSERT INTO salary_audit (
employee_id,
old_salary,
new_salary,
change_date,
username,
session_id
)
VALUES (
:OLD.employee_id,
:OLD.salary,
:NEW.salary,
Solution – Trigger Salary SYSDATE, -- Current system date and time
USER, -- Current database user
Audit );
SYS_CONTEXT('USERENV', 'SESSIONID') -- Current session ID

EXCEPTION
WHEN OTHERS THEN
-- Handle unexpected errors silently to avoid interrupting the original UPDATE
-- Optionally, log error into an error_log table (not shown here)
NULL; -- Replace with logging if desired
END;
END;
/

22
 Step 3: Procedure to update salary
CREATE OR REPLACE PROCEDURE update_employee_salary (
p_employee_id IN NUMBER,
p_new_salary IN NUMBER
) AS
BEGIN
-- Update employee salary in the employees table
UPDATE employees
SET salary = p_new_salary
WHERE employee_id = p_employee_id;

-- Ensure that the update statement actually modifies a row

IF SQL%ROWCOUNT = 0 THEN
RAISE_APPLICATION_ERROR(-20003, 'No employee found with ID ' || p_employee_id);
END IF;

END;
/*
/
Call the procedure and input id and salary to be changed.
*/
Solution – Procedure to -- Call the procedure
BEGIN

updata salary UPDATE_EMPLOYEE_SALARY(101, 70500);

END;
/

Test Procedure & Trigger ➔ Output

23
 III. Creating a DML Trigger for Auditing Allowances Table
Operations

Scenario-Based Question:
3. Stored Procedures for DML Operations:
You are tasked with implementing an audit mechanism to log all DML (Data Manipulation ▪ Write UPDATE and DELETE procedures for the ALLOWANCES table with exception
Language) operations (INSERT, UPDATE, DELETE) on critical tables in a banking system handling.
database. This ensures accountability and transparency by tracking changes made to key tables. ▪ If an error occurs, log it in an error log table.

Tables to be Audited: 4. Test the Implementation:

▪ ALLOWANCES ▪ Perform INSERT, UPDATE, and DELETE operations on ALLOWANCES.
▪ ATTENDANCE ▪ Verify that the audit table logs the operations correctly.
▪ COUNTRIES ▪ Check the error log table for any logged errors.
▪ DEPARTMENTS
▪ EMPLOYEES Deliverables:
▪ SQL scripts to:
▪ ROLES
o Create the audit table.
o Implement triggers for DML operations.
Objectives:
o Write stored procedures for UPDATE and DELETE operations.
o Test results showing the audit logs and error logs.
1. Create an Audit Table:
▪ The table should capture the following for each DML operation: Evaluation Criteria:
o operation_type: INSERT, UPDATE, DELETE ▪ Correctness: Does the trigger capture all DML operations with appropriate details?
o object_name: Table name ▪ Error Handling: Are errors correctly logged in the error log table?
o operation_user: The user who performed the operation ▪ Efficiency: Are the procedures optimized for performance while handling exceptions?
o timestamp: When the operation occurred
o old_data: Data before the operation (only for UPDATE and DELETE)
o new_data: Data after the operation (only for INSERT and UPDATE)

2. Create a Trigger for Logging DML Operations:

▪ Write a trigger for the ALLOWANCES table (and similar ones for others), which fires after
INSERT, UPDATE, or DELETE.
▪ The trigger should insert logs into the audit table, capturing old data for
DELETE/UPDATE and new data for INSERT/UPDATE operations.
24
 /*
Step 1: Create the Audit Table
First, let's create an audit table that logs the changes made to any of these tables.
*/
CREATE TABLE audit_dml_log (
id NUMBER GENERATED BY DEFAULT AS IDENTITY, -- Unique identifier
operation_type VARCHAR2(50), -- Type of operation (INSERT, UPDATE, DELETE)
object_name VARCHAR2(100), -- Name of the affected table
operation_user VARCHAR2(50), -- User who performed the operation
timestamp DATE, -- Date and time of the operation
old_data VARCHAR2(4000), -- Old data (for UPDATE and DELETE)
new_data VARCHAR2(4000) -- New data (for INSERT and UPDATE)
);

/*
Step 2: Create the DML Trigger

Solution - DML Trigger - Now, let's create a DML Trigger for each table (e.g., ALLOWANCES, ATTENDANCE, COUNTRIES, DEPARTMENTS,
EMPLOYEES, ROLES). This trigger will capture the changes made (insertion, update, deletion) to any of the tables and log
those changes in the audit_dml_log table.
Auditing Allowances */
CREATE OR REPLACE TRIGGER log_dml_operations
Operations (1/4) AFTER INSERT OR UPDATE OR DELETE ON ALLOWANCES -- You can change this table name dynamically if needed
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO audit_dml_log (operation_type, object_name, operation_user, timestamp, new_data)
VALUES ('INSERT', 'ALLOWANCES', SYS_CONTEXT('USERENV', 'SESSION_USER'), SYSDATE,
:NEW.ALLOWANCE_AMOUNT);
ELSIF UPDATING THEN
SYS_CONTEXT('USERENV', 'SESSION_USER') is an INSERT INTO audit_dml_log (operation_type, object_name, operation_user, timestamp, old_data, new_data)
Oracle SQL function used to retrieve information VALUES ('UPDATE', 'ALLOWANCES', SYS_CONTEXT('USERENV', 'SESSION_USER'), SYSDATE,
about the current session, specifically the session
:OLD.ALLOWANCE_AMOUNT, :NEW.ALLOWANCE_AMOUNT);
user (i.e., the user currently connected to the
ELSIF DELETING THEN
database).
INSERT INTO audit_dml_log (operation_type, object_name, operation_user, timestamp, old_data)
VALUES ('DELETE', 'ALLOWANCES', SYS_CONTEXT('USERENV', 'SESSION_USER'), SYSDATE,
:OLD.ALLOWANCE_AMOUNT);
END IF;
END;
/
25
 /*
Step 3: Repeat for Other Tables
You can repeat the process for other tables (ATTENDANCE, COUNTRIES, DEPARTMENTS, EMPLOYEES, ROLES)
by changing the AFTER INSERT OR UPDATE OR DELETE ON part of the trigger, and modifying the field names in the
:NEW and :OLD references according to the specific columns in those tables.

For example, for the ATTENDANCE table, the trigger would look like this:
*/

CREATE OR REPLACE TRIGGER log_dml_operations_attendance

AFTER INSERT OR UPDATE OR DELETE ON ATTENDANCE
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO audit_dml_log (operation_type, object_name, operation_user, timestamp, new_data)
VALUES ('INSERT', 'ATTENDANCE', SYS_CONTEXT('USERENV', 'SESSION_USER'), SYSDATE, :NEW.STATUS);
Solution - DML Trigger - ELSIF UPDATING THEN
INSERT INTO audit_dml_log (operation_type, object_name, operation_user, timestamp, old_data, new_data)
Auditing Attendance VALUES ('UPDATE', 'ATTENDANCE', SYS_CONTEXT('USERENV', 'SESSION_USER'), SYSDATE, :OLD.STATUS,
:NEW.STATUS);

Operations (2/3) ELSIF DELETING THEN

INSERT INTO audit_dml_log (operation_type, object_name, operation_user, timestamp, old_data)
VALUES ('DELETE', 'ATTENDANCE', SYS_CONTEXT('USERENV', 'SESSION_USER'), SYSDATE, :OLD.STATUS);
END IF;
END;
/

26
 CREATE OR REPLACE PROCEDURE update_allowance( update_allowance with exception handling
p_allowance_id IN NUMBER,
p_new_amount IN NUMBER
) AS
e_id_not_found EXCEPTION;
PRAGMA EXCEPTION_INIT(e_id_not_found, -20001);
BEGIN
UPDATE ALLOWANCES
SET ALLOWANCE_AMOUNT = p_new_amount
WHERE ALLOWANCE_ID = p_allowance_id;

IF SQL%ROWCOUNT = 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Allowance ID does not exist.');
END IF;

COMMIT;
Test – Update EXCEPTION
WHEN e_id_not_found THEN
Allowances RAISE;
WHEN OTHERS THEN

Operations (2/3) END;

RAISE;

--Call update_allowance
/

BEGIN
update_allowance(12, 6000);
END;
/

27
 delete_allowance
CREATE OR REPLACE PROCEDURE delete_allowance(
p_allowance_id IN ALLOWANCE_ID NUMBER

) AS
BEGIN
DELETE FROM ALLOWANCES
WHERE ALLOWANCE_ID = p_allowance_id;

COMMIT;
EXCEPTION
WHEN OTHERS THEN
INSERT INTO audit_dml_error_log (error_message, error_time, object_name, operation_user)
VALUES (SQLERRM, SYSDATE, 'ALLOWANCES', SYS_CONTEXT('USERENV', 'SESSION_USER'));
RAISE;
END; BEGIN
/ delete_allowance(12);
END;

Test – Delete Update attendance

CREATE OR REPLACE PROCEDURE update_attendance(
/

Allowances & Update p_attendance_id IN NUMBER,

p_new_status IN VARCHAR2

Attendance (3/4) ) AS
BEGIN
UPDATE ATTENDANCE
SET STATUS = p_new_status
WHERE ATTENDANCE_ID = p_attendance_id;

IF SQL%ROWCOUNT = 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Attendance ID does not exist.');
END IF;

COMMIT;
EXCEPTION
WHEN OTHERS THEN
--Call update_allowance
ROLLBACK;
BEGIN
RAISE;
END; update_attendance(101, 'Present');
/ END;
/ 28
 delete_attendance
CREATE OR REPLACE PROCEDURE delete_attendance(
p_attendance_id IN NUMBER
) AS
BEGIN
DELETE FROM ATTENDANCE
WHERE ATTENDANCE_ID = p_attendance_id;

IF SQL%ROWCOUNT = 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Error: Attendance ID does not exist.');
END IF;

COMMIT;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20002, 'Unexpected error occurred while deleting attendance: ' ||
SQLERRM); BEGIN
END; delete_attendance(101);
/ END;
/
Test – Delete
Attendance (4/4)

29
 /*
Step 4: Test the Trigger
Let's perform some operations on the ALLOWANCES table to verify that the trigger is working as expected.

Test Case 1: Inserting a Row

*/

INSERT INTO ALLOWANCES (ALLOWANCE_ID, ROLE_ID, ALLOWANCE_AMOUNT, EFFECTIVE_DATE, IS_APPLICABLE,

COMMENTS)
VALUES ( 12, 2, 5000, TO_DATE('2024-11-01', 'YYYY-MM-DD'), 'Y', 'Performance-based allowance’);

INSERT INTO ALLOWANCES (ALLOWANCE_ID, ROLE_ID, ALLOWANCE_AMOUNT, EFFECTIVE_DATE, IS_APPLICABLE,

COMMENTS)
VALUES (24, 3, 6000, TO_DATE('2024-11-01', 'YYYY-MM-DD'), 'Y', 'Team lead allowance');

INSERT INTO ALLOWANCES (ALLOWANCE_ID, ROLE_ID, ALLOWANCE_AMOUNT, EFFECTIVE_DATE, IS_APPLICABLE,

Test – Insert Data in COMMENTS)
VALUES (25, 4, 4500, TO_DATE('2024-11-01', 'YYYY-MM-DD'), 'Y', 'Department bonus');
Allowances & INSERT INTO ALLOWANCES (ALLOWANCE_ID, ROLE_ID, ALLOWANCE_AMOUNT, EFFECTIVE_DATE, IS_APPLICABLE,

Results: Audit COMMENTS)

VALUES (26, 5, 5200, TO_DATE('2024-11-01', 'YYYY-MM-DD'), 'Y', 'Special project reward');

Tables (3/3) /*
Output Check the audit log:
*/
SELECT * FROM audit_dml_log;

-- DML logs
SELECT * FROM audit_dml_log ORDER BY timestamp DESC;

30
 IV. Database Error Logging with Trigger – Practical Task

Scenario-Based Question

You are working as a Database Administrator in an organization where it is critical to log all DML (Data Manipulation Language) errors for audit and debugging purposes. Your goal is to
ensure that whenever a DML error occurs (e.g., due to a constraint violation), a trigger automatically logs the error details into a table called dml_error_log.

Task Overview

▪ Review the error log table creation script:

Understand the structure of the dml_error_log table. This table is used to store information about DML errors, such as the error message, time, object name, action type,
session ID, user, and SQL text (if available).
▪ Understand the server error trigger:
Examine the trg_log_dml_errors trigger that fires automatically after a server error occurs. It captures error details and inserts them into the dml_error_log table.
▪ Test the setup by generating a DML error:
Now simulate a real-world scenario by executing an invalid SQL statement (for example, inserting a duplicate value into a table with a PRIMARY KEY or UNIQUE constraint). This
error should be captured and logged automatically by the trigger.

Instructions

▪ Run the table and trigger creation script to set up the environment.
▪ Choose or create a sample table for testing.

31
 CREATE TABLE dml_error_log (
log_id NUMBER GENERATED ALWAYS AS IDENTITY,
error_message VARCHAR2(4000),
error_time TIMESTAMP DEFAULT SYSTIMESTAMP,
object_name VARCHAR2(128),
action VARCHAR2(30),
session_id NUMBER,
username VARCHAR2(128),
sql_text CLOB,
CONSTRAINT pk_dml_error_log PRIMARY KEY (log_id)
);

-- Create index on commonly searched columns

CREATE INDEX idx_dml_error_log_time ON dml_error_log(error_time);
CREATE INDEX idx_dml_error_log_object ON dml_error_log(object_name);

Solution : -- Create a comment on the table

COMMENT ON TABLE dml_error_log IS 'Logs DML errors from database operations';
Error record Table & -- Create comments on columns
Index created COMMENT ON COLUMN dml_error_log.log_id IS 'Primary key - automatically generated';
COMMENT ON COLUMN dml_error_log.error_message IS 'Oracle error message text';
COMMENT ON COLUMN dml_error_log.error_time IS 'Timestamp when the error occurred';
COMMENT ON COLUMN dml_error_log.object_name IS 'Name of the table or object that caused the error';
COMMENT ON COLUMN dml_error_log.action IS 'Type of DML operation (INSERT, UPDATE, DELETE, ALTER)';
COMMENT ON COLUMN dml_error_log.session_id IS 'Database session ID where the error occurred';
COMMENT ON COLUMN dml_error_log.username IS 'Database username that executed the statement';
COMMENT ON COLUMN dml_error_log.sql_text IS 'SQL statement that caused the error (when available)';

-- Grant permissions as needed

-- GRANT SELECT ON dml_error_log TO reporting_role;
-- GRANT INSERT ON dml_error_log TO app_role;

32
 CREATE OR REPLACE TRIGGER trg_log_dml_errors
--fire automatically after any error occurs | AFTER SERVERERROR Trigger activates after an error occurs
AFTER SERVERERROR ON SCHEMA
DECLARE
v_error_stack VARCHAR2(4000);
v_object_name VARCHAR2(128);
v_action VARCHAR2(30);
BEGIN
-- Get the error information
v_error_stack := DBMS_UTILITY.FORMAT_ERROR_STACK;

-- Determine which object and action caused the error | For demonstration, we'll check the current SQL statement

IF INSTR(UPPER(DBMS_UTILITY.FORMAT_CALL_STACK), 'ATTENDANCE') > 0 THEN

v_object_name := 'ATTENDANCE';
ELSIF INSTR(UPPER(DBMS_UTILITY.FORMAT_CALL_STACK), 'ALLOWANCES') > 0 THEN
v_object_name := 'ALLOWANCES';
ELSE
v_object_name := 'UNKNOWN';
END IF;

-- Determine the action type

IF INSTR(UPPER(DBMS_UTILITY.FORMAT_CALL_STACK), 'INSERT') > 0 THEN
v_action := 'INSERT';
ELSIF INSTR(UPPER(DBMS_UTILITY.FORMAT_CALL_STACK), 'UPDATE') > 0 THEN
v_action := 'UPDATE';
ELSIF INSTR(UPPER(DBMS_UTILITY.FORMAT_CALL_STACK), 'DELETE') > 0 THEN
v_action := 'DELETE';
ELSIF INSTR(UPPER(DBMS_UTILITY.FORMAT_CALL_STACK), 'ALTER') > 0 THEN

Trigger Definition :
v_action := 'ALTER';
ELSE
v_action := 'UNKNOWN';
END IF;

Error Records -- Insert into error log table

INSERT INTO dml_error_log (
error_message,
error_time,
object_name,
action,
session_id,
username
) VALUES (
v_error_stack,
SYSTIMESTAMP,
v_object_name,
v_action,
SYS_CONTEXT('USERENV', 'SID'),
SYS_CONTEXT('USERENV', 'SESSION_USER')
);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
-- If error logging itself fails, write to server log
DBMS_OUTPUT.PUT_LINE('Error logging failed: ' || SQLERRM);
END;
/ 33
Basic Setup Testing

Output

34
 System Event Trigger Definition

A system event trigger in a database is used to automatically execute a PL/SQL block of code in response to certain predefined system-level events. These events are not
associated with any specific table but are linked to database operations like user logins, database startup or shutdown, and DDL (Data Definition Language) changes.
Common Events for System Triggers:
▪ LOGON: Triggered when a user logs into the database.
▪ LOGOFF: Triggered when a user logs off from the database.
▪ STARTUP: Triggered when the database is started.
▪ SHUTDOWN: Triggered when the database is shut down.
▪ AFTER SERVERERROR: Triggered after a server error occurs.
▪ AFTER DDL: Triggered after a DDL statement (e.g., CREATE, ALTER, DROP) is executed.
▪ BEFORE DDL: Triggered before a DDL statement is executed.

CREATE OR REPLACE TRIGGER trigger_name

-- Specify the timing of the trigger (BEFORE or AFTER the event)
Explanation of the Components:
AFTER | BEFORE
▪ CREATE OR REPLACE TRIGGER trigger_name: Defines a new trigger or
-- Specify the system event to monitor
replaces an existing trigger with the same name.
[STARTUP | SHUTDOWN | LOGON | LOGOFF | DDL | SERVERERROR]
▪ AFTER | BEFORE: Specifies whether the trigger should fire before or after the
event occurs.
-- Specify the scope of the trigger (DATABASE for all users, SCHEMA for the current
▪ [STARTUP | SHUTDOWN | LOGON | LOGOFF | DDL | SERVERERROR]:
schema)
Specifies the database event that will activate the trigger.
ON [DATABASE | SCHEMA]
▪ ON [DATABASE | SCHEMA]: Defines the scope of the trigger:
o DATABASE: Trigger applies to all users of the database.
BEGIN
o SCHEMA: Trigger applies only to the schema of the user creating the
-- Add the PL/SQL code here to define the trigger's action
trigger.
-- e.g., log information, modify session, etc.
END;
/
35
 Classification of SQL Operations

Activity SQL Category Description

CREATE TABLE, ALTER TABLE, etc. DDL Defines and modifies the structure of database objects

INSERT, UPDATE, DELETE DML (Data Manipulation Language) Changes data inside tables

GRANT, REVOKE DCL (Data Control Language) Manages permissions and access controls

Login/Logoff auditing DBMS auditing or security features Typically handled by database configuration or logs

36
 This trigger captures login events and logs them into a custom USER_LOG table.

-- Create a table to log user activity Create Audit Table for Login Logs
CREATE TABLE USER_LOG (
LOG_ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- Unique identifier for each log entry
USERNAME VARCHAR2(50), -- Stores the username of the logged-in user
SESSION_ID NUMBER, -- Tracks the session ID of the user
LOGON_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- Records the logon time (defaults to the current time)
);

-- Create or replace a trigger named 'trg_logon_audit' Create a trigger

CREATE OR REPLACE TRIGGER trg_logon_audit

-- Specify that the trigger fires AFTER a LOGON event on the entire DATABASE
AFTER LOGON ON DATABASE
BEGIN
-- Insert a record into the USER_LOG table for every user logon

I. Logging User INSERT INTO USER_LOG (USERNAME, SESSION_ID, LOGON_TIME)

-- USER: Captures the username of the logged-in user

Logins -- SYS_CONTEXT('USERENV', 'SESSIONID'): Retrieves the current session ID
-- SYSTIMESTAMP: Captures the current timestamp for the logon
VALUES (USER, SYS_CONTEXT('USERENV', 'SESSIONID'), SYSTIMESTAMP);
END trg_logon_audit;
/

Output

/*
Check the USER_LOG Table:
Log in to the database as the user who created the trigger and
verify the records.
Query the USER_LOG table:
*/

SELECT * FROM USER_LOG;

37
 This trigger tracks all Data Definition Language (DDL) operations (e.g., CREATE, ALTER, DROP)
performed in the schema.
CREATE TABLE DDL_LOG (
LOG_ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
USERNAME VARCHAR2(50),
SCHEMA_NAME VARCHAR2(50), Create Audit Table for DDL Logs
EVENT_TYPE VARCHAR2(50),
OBJECT_NAME VARCHAR2(100),
TIMESTAMP TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Create or replace a trigger named 'trg_ddl_audit'
CREATE OR REPLACE TRIGGER trg_ddl_audit Create a trigger

-- Specify that the trigger fires AFTER any DDL (Data Definition Language) operation
on the current schema
AFTER DDL ON SCHEMA
BEGIN

II. Logging User -- Insert a record into the DDL_LOG table for every DDL operation
INSERT INTO DDL_LOG (
USERNAME, -- The user performing the DDL operation
Logins SCHEMA_NAME,
EVENT_TYPE,
-- The schema where the DDL operation is being executed
-- The type of DDL event (e.g., CREATE, ALTER, DROP)
OBJECT_NAME, -- The name of the object affected by the DDL event (e.g.,
table, view, etc.)
TIMESTAMP -- The timestamp when the DDL operation occurred
)
VALUES (
USER, -- Built-in function returning the username
SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'), -- Retrieves the current
schema name
ORA_SYSEVENT, -- Built-in variable for the type of DDL event
(e.g., CREATE, ALTER, DROP)
ORA_DICT_OBJ_NAME, -- Built-in variable for the name of the
object involved in the DDL operation
SYSTIMESTAMP -- Captures the current date and time
);
END trg_ddl_audit;
/ 38
 /*
Step 1: Ensure the DDL_LOG Table Exists
Before testing, make sure the DDL_LOG table is created and ready to store audit records:
*/
SELECT * FROM DDL_LOG;

Output

Solution Logging DDL

Change – Output

39
 -- Create the logging table
CREATE TABLE shutdown_log (
log_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
event_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
event_type VARCHAR2(100),
event_message VARCHAR2(4000)
);

A pre-shutdown trigger
-- Create a before shutdown trigger (fixed version)
CREATE OR REPLACE TRIGGER trg_before_shutdown
BEFORE SHUTDOWN ON DATABASE
BEGIN
INSERT INTO shutdown_log (event_type, event_message)
VALUES ('PLANNED_SHUTDOWN', 'Database shutdown initiated through normal command');
COMMIT;
EXCEPTION

III. Logging WHEN OTHERS THEN

-- Try to log the error

Shutdown/resta
BEGIN
INSERT INTO shutdown_log (event_type, event_message)
VALUES ('SHUTDOWN_ERROR', 'Error during shutdown trigger: ' ||
rts Events DBMS_UTILITY.FORMAT_ERROR_STACK);
COMMIT;
EXCEPTION A pre-shutdown trigger
WHEN OTHERS THEN •System state views monitoring
NULL; -- Silent failure as last resort •Alert log monitoring
END;
END;
/

-- Create an instance startup trigger to log restarts

CREATE OR REPLACE TRIGGER trg_after_startup Trigger log monitoring
AFTER STARTUP ON DATABASE
BEGIN
INSERT INTO shutdown_log (event_type, event_message)
VALUES ('STARTUP', 'Database started up');
COMMIT;
END;
/ 40
 -- Connect as user with shutdown privileges (like SYSDBA)
sqlus username/password AS SYSDBA

-- Initiate a shutdown Output

SHUTDOWN IMMEDIATE;

-- After restarting the database

STARTUP;

-- Check the log table

SELECT * FROM shutdown_log ORDER BY event_time DESC;

Manual Test with

DBA Privileges

41
 -- Check the log table
SELECT * FROM shutdown_log ORDER BY event_time DESC;

Output

Manual Test with

DBA Privileges -
Output

42
 IV. Trigger Event: Enforcing HR Transaction Restrictions
During Non-Working Hours

Scenario-Based Question:

The Human Resources Department of a large organization manages allowances for its employees. As part of the organizational policy, it is required that any changes to the
ALLOWANCES table—whether it be inserting new allowances, updating existing records, or deleting them—should only be allowed during official working hours (from 6:00 AM to 6:00
PM). This policy is designed to ensure that only authorized personnel, during working hours, can make changes to employee allowances.
If an attempt is made to insert, update, or delete records outside of these hours (e.g., during the night or weekends), the system should automatically block such attempts and raise an
appropriate error message to inform the user of the restriction.

Task:

As a database developer, you are tasked with implementing a PL/SQL trigger that enforces this policy on the ALLOWANCES table. The trigger should:
1. Fire before any INSERT, UPDATE, or DELETE operation on the ALLOWANCES table.
2. Check the system's current time to ensure it falls within the allowed timeframe of 7:00 AM to 6:00 PM.
3. If the operation is attempted outside these hours, raise an exception with the following message:
"Operations on the ALLOWANCES table are only allowed between 6:00 AM and 6:00 PM."

Deliverable:
▪ Write a PL/SQL trigger that implements the described functionality.

43
 CREATE OR REPLACE TRIGGER restrict_allowance_hours
BEFORE INSERT OR UPDATE OR DELETE ON ALLOWANCES
FOR EACH ROW
DECLARE
-- Declare an exception to handle invalid transaction time NEW is a reference to the new values being inserted o
invalid_time EXCEPTION; updated in the table. Specifically, :NEW refers to the
v_current_hour NUMBER; record being affected by the INSERT or UPDATE
BEGIN
-- Check the current time in 24-hour format
operation in the ALLOWANCES table.
v_current_hour := TO_NUMBER(TO_CHAR(SYSDATE, 'HH24'));

-- Check if operation is performed outside working hours (7:00 AM to 6:00 PM)

IF v_current_hour < 7 OR v_current_hour >= 18 THEN
RAISE invalid_time;
END IF;

-- Additional validations only for INSERT and UPDATE operations

Solution: Enforcing IF INSERTING OR UPDATING THEN

-- If allowance amount is invalid (negative), raise an exception
IF :NEW.ALLOWANCE_AMOUNT < 0 THEN

HR Transaction
RAISE_APPLICATION_ERROR(-20003, 'Allowance amount cannot be negative.');
END IF;

Restriction
-- If the effective date is in the past, raise an exception
IF :NEW.EFFECTIVE_DATE < SYSDATE THEN
RAISE_APPLICATION_ERROR(-20004, 'Effective date cannot be in the past.');
END IF;

-- If IS_APPLICABLE is not provided, raise an exception

IF :NEW.IS_APPLICABLE IS NULL THEN
RAISE_APPLICATION_ERROR(-20005, 'Applicability status (IS_APPLICABLE) is required.');
END IF;

-- Check that COMMENTS field is not empty or null

IF :NEW.COMMENTS IS NULL OR TRIM(:NEW.COMMENTS) IS NULL THEN
RAISE_APPLICATION_ERROR(-20006, 'Comments are required.');
END IF;
END IF;

EXCEPTION
WHEN invalid_time THEN
-- Handle invalid time exception with custom message
RAISE_APPLICATION_ERROR(-20002, 'Operations on the ALLOWANCES table are only allowed between 7:00 AM and 6:00 PM.');
WHEN OTHERS THEN
-- Generic exception handler for unexpected errors
RAISE_APPLICATION_ERROR(-20001, 'An unexpected error occurred: ' || SQLERRM);
END;
44
 /*
1. Valid Transaction (Within Working Hours):
*/
-- Example: Insert at 10:00 AM with valid data
INSERT INTO ALLOWANCES (ROLE_ID, ALLOWANCE_AMOUNT, EFFECTIVE_DATE, IS_APPLICABLE, COMMENTS)
VALUES (1, 500, SYSDATE + 1, 'Y', 'Annual Bonus');
-- Success: Operation allowed.

/*
2. Invalid Transaction (Outside Working Hours):

*/
-- Example: Insert at 8:00 PM (outside working hours)
INSERT INTO ALLOWANCES (ROLE_ID, ALLOWANCE_AMOUNT, EFFECTIVE_DATE, IS_APPLICABLE, COMMENTS)
VALUES (2, 600, SYSDATE + 1, 'Y', 'Holiday Allowance');
-- Error: "Operations on the ALLOWANCES table are only allowed between 6:00 AM and 6:00 PM."

Test: Enforcing HR
/*
3. Invalid Transaction (Negative Allowance Amount):
*/
Transaction -- Example: Insert with a negative allowance amount
INSERT INTO ALLOWANCES (ROLE_ID, ALLOWANCE_AMOUNT, EFFECTIVE_DATE, IS_APPLICABLE, COMMENTS)

Restriction Trigger VALUES (3, -200, SYSDATE + 1, 'Y', 'Error Test');

-- Error: "Allowance amount cannot be negative."

/*
4. Invalid Transaction (Effective Date in the Past):
*/
-- Example: Insert with an effective date in the past
INSERT INTO ALLOWANCES (ROLE_ID, ALLOWANCE_AMOUNT, EFFECTIVE_DATE, IS_APPLICABLE, COMMENTS)
VALUES (4, 300, SYSDATE - 1, 'Y', 'Retroactive Allowance');
-- Error: "Effective date cannot be in the past."

/*
5. Invalid Transaction (Null Comments):
*/
-- Example: Insert with no comments provided
INSERT INTO ALLOWANCES (ROLE_ID, ALLOWANCE_AMOUNT, EFFECTIVE_DATE, IS_APPLICABLE, COMMENTS)
VALUES (5, 400, SYSDATE + 1, 'Y', NULL);
-- Error: "Comments are required."
45
 Test out of working hours

Test: Enforcing HR
Transaction
Restriction Trigger

46
 Scenario-Based Question: Trigger Event, Restricting Bank
Transactions Outside Working Hours

Scenario: Enforcing Transaction Timing Policy via PL/SQL Trigger

The Bank of Kigali has introduced a new operational policy aimed at strengthening compliance and enhancing system security. According to this policy, tellers are prohibited from
recording transactions outside official working hours.
Business Requirements:
▪ Transactions may only be recorded Monday through Friday, between 9:00 AM and 6:00 PM.
▪ Any attempt to record a transaction outside these hours or on weekends (Saturday or Sunday) must be automatically blocked by the database system.

Task for the Database Developer:

As a database developer, your responsibilities are as follows:
▪ Create a PL/SQL Trigger
o The trigger must fire BEFORE INSERT or UPDATE on the transactions table.
▪ Implement Validation Logic
o The trigger should check the current system date and time using SYSDATE to ensure the transaction is being recorded within the permitted timeframe.
▪ Handle Exceptions
o If a transaction is attempted outside the allowed days or hours, the trigger must:
✓ Raise an exception with the message:
Now I can confidently audit a bank
“Transactions can only be recorded between 9:00 AM and 6:00 PM, Monday to Friday.” database! Using the concept of logging
This ensures that all transaction records comply with the defined business rules and operational hours. shutdown events, I can track critical
activities, ensure accountability, and
monitor unauthorized actions. This practical
approach to auditing demonstrates how to
enhance security and compliance while
building robust insights for database
management.

47
 -- Step 1: Create a sample transactions table (if it doesn't already exist)
CREATE TABLE transactions (
transaction_id NUMBER PRIMARY KEY,
teller_id NUMBER,
transaction_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
amount NUMBER
);

-- Step 2: Create the trigger to enforce the policy

CREATE OR REPLACE TRIGGER trg_validate_transaction_time
BEFORE INSERT OR UPDATE ON transactions
FOR EACH ROW
BEGIN
-- Get the current day of the week and time

Solution : Trigger IF TO_CHAR(SYSDATE, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') IN ('SAT', 'SUN') THEN

-- Raise an exception for weekends

Event - Restricting
RAISE_APPLICATION_ERROR(-20001,
'Transactions can only be recorded between 9:00 AM and 6:00 PM, Monday to Friday.');
ELSIF TO_CHAR(SYSDATE, 'HH24MI') < '0900' OR TO_CHAR(SYSDATE, 'HH24MI') > '1800' THEN
Bank Transactions -- Raise an exception for outside working hours
RAISE_APPLICATION_ERROR(-20002,
'Transactions can only be recorded between 9:00 AM and 6:00 PM, Monday to Friday.');
END IF;
END;
/

/*
Testing the Trigger Success: Transaction is recorded.
1. Valid Transaction:
*/
-- Example of a valid transaction (Monday at 10:00 AM)
INSERT INTO transactions (transaction_id, teller_id, amount)
VALUES (1, 101, 500);
48
 /*
Testing the Trigger -- Example of a valid transaction (Monday at 10:00 AM)
1. Valid Transaction:
*/
INSERT INTO transactions (transaction_id, teller_id, amount)
VALUES (1, 101, 500);

-- Error: "Transactions can only be recorded between 9:00 AM and 6:00 PM, Monday to Friday."
/*
Invalid Transaction (Weekend): -- Example of a transaction attempted on a Saturday
*/
INSERT INTO transactions (transaction_id, teller_id, amount)
VALUES (2, 102, 1000);

Solution & Test :

-- Example of a transaction attempted at 8:00 PM on a Monday
/*
Invalid Transaction (Outside Working Hours):
Trigger Event - */
INSERT INTO transactions (transaction_id, teller_id, amount)

Restricting Bank VALUES (3, 103, 1500);

-- Error: "Transactions can only be recorded between 9:00 AM and 6:00 PM, Monday to Friday."

Transactions

Output

49
 Trigger Event: Before Update Trigger to Enforce Salary
Protection in Employees Table
-- Trigger to prevent salary reduction in the 'employees' table
Question: CREATE OR REPLACE TRIGGER prevent_salary_reduction
-- The trigger will fire before any update on the salary column in the employees table
Suppose you are managing an employees table where each employee has BEFORE UPDATE OF salary ON employees
a salary that should not be decreased. Write a trigger named FOR EACH ROW -- Specifies that the trigger will execute once for each row being updated
prevent_salary_reduction that prevents any update to the salary column if BEGIN
the new salary is lower than the existing salary. -- Check if the new salary is less than the old salary
IF :NEW.salary < :OLD.salary THEN
The trigger should meet the following requirements: -- Raise an application error if the new salary is less than the old salary
1. It should be a BEFORE UPDATE trigger, so it checks the salary RAISE_APPLICATION_ERROR(-20001, 'Salary reduction is not allowed.');
change before the update is applied. END IF;
2. It should raise an error with the message "Salary reduction is not END;
allowed." if an attempt is made to reduce an employee's salary. /
3. Use RAISE_APPLICATION_ERROR with a custom error code to handle
this restriction.
--Test my trigger
UPDATE employees
Provide the full trigger code and explain each part of the code in
SET salary = 2500000
comments.
WHERE employee_id = 5;

50
Test Trigger Event Salary Reduction

Current Salary Output

Downgrade salary Test Output

51
 Creating a Trigger to Log Employee Salary Changes and
Operations

BEFORE INSERT OR UPDATE OR DELETE

Suppose you are tasked with creating a trigger that logs salary changes for employees, but only for those whose ROLE_ID is not equal to 1 (assuming role 1 is the Manager). The
trigger should work for INSERT, UPDATE, or DELETE operations on the EMPLOYEES table.

Write a trigger named log_salary_changes that fulfills the following requirements:

1. The trigger should be fired before an INSERT, UPDATE, or DELETE operation on the EMPLOYEES table.
2. It should log the following details for each type of operation:
▪ For an UPDATE operation: The old and new salary values and the difference between them.
▪ For an INSERT operation: The name of the new employee and their starting salary.
▪ For a DELETE operation: The name of the deleted employee and their final salary.
3. The trigger should only log changes for employees who do not have the role of Manager (ROLE_ID <> 1).
4. Make use of the DBMS_OUTPUT.PUT_LINE procedure to display the logged information.

You should handle all operations appropriately within the trigger, using the UPDATING, INSERTING, and DELETING conditions to check the operation type.

Hint: Use the :NEW and :OLD references to capture the values of the columns before and after the operations.

52
Trigger Event: Log Employee Salary Changes and Operations
– Solution

CREATE OR REPLACE TRIGGER log_salary_changes

BEFORE INSERT OR UPDATE OR DELETE ON EMPLOYEES
FOR EACH ROW
WHEN (NEW.ROLE_ID <> 1) -- Assuming '1' is the role ID for the Manager
DECLARE
sal_diff NUMBER; -- Variable to hold the salary difference
BEGIN Hint: Use the :NEW and :OLD references to capture the
-- Check if the operation is an update to log salary changes values of the columns before and after the operations.
IF UPDATING THEN
sal_diff := :NEW.SALARY - :OLD.SALARY;
DBMS_OUTPUT.PUT_LINE('Updating Employee: ' || :NEW.LAST_NAME);
DBMS_OUTPUT.PUT_LINE('Old salary = ' || :OLD.SALARY || ', New salary = ' || :NEW.SALARY);
DBMS_OUTPUT.PUT_LINE('Difference: ' || sal_diff);
ELSIF INSERTING THEN
DBMS_OUTPUT.PUT_LINE('Inserting New Employee: ' || :NEW.LAST_NAME);
DBMS_OUTPUT.PUT_LINE('Starting Salary: ' || :NEW.SALARY);
ELSIF DELETING THEN
DBMS_OUTPUT.PUT_LINE('Deleting Employee: ' || :OLD.LAST_NAME);
DBMS_OUTPUT.PUT_LINE('Final Salary: ' || :OLD.SALARY);
END IF;
END;
/ /*Test Insert Operation*/
INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, ROLE_ID, SALARY)
VALUES (1001, 'John', 'Doe', 2, 4000); -- Assuming role ID '2' is for a non-manager

/* Test Update Operation */

UPDATE EMPLOYEES
SET SALARY = 4500
WHERE EMPLOYEE_ID = 1001; 53
Difference Between BEFORE and INSTEAD OF
Triggers
BEFORE Trigger INSTEAD OF Trigger

A BEFORE trigger is executed just before the DML operation (INSERT, UPDATE, or An INSTEAD OF trigger replaces the default behavior of a DML operation on a view.
DELETE) is performed on a table or view.

Purpose: It allows you to define exactly what should happen instead of the normal
Purpose: It’s typically used to validate or modify data before the operation takes INSERT, UPDATE, or DELETE operation.
place.

Important: The DML operation will still execute unless: Why needed: Views generally do not support direct DML operations—especially when
▪You explicitly raise an error (e.g., with RAISE_APPLICATION_ERROR) they are based on multiple tables or contain complex joins. INSTEAD OF triggers make
▪Or take some other action that prevents it such views updatable.

Example Use Case: Example Use Case:

Validating data (e.g., ensuring salary is within an acceptable range) before inserting a new You have a complex view that joins data from multiple tables. When a user tries to insert a
employee. record into that view, an INSTEAD OF trigger can handle how the data should actually be
inserted into the underlying tables.

Summary Table
Trigger Type When It Executes Used On Default Action
BEFORE Before the DML runs Tables Operation proceeds unless stopped

INSTEAD OF Replaces the DML logic Views Custom logic fully replaces default behavior
54
 /*
2. Create the INSTEAD OF Trigger:
*/ /*1. Create the View (if not already created):*/
CREATE OR REPLACE TRIGGER log_salary_changes
INSTEAD OF INSERT OR UPDATE OR DELETE ON EMPLOYEES_VIEW CREATE OR REPLACE VIEW EMPLOYEES_VIEW AS
FOR EACH ROW SELECT EMPLOYEE_ID, LAST_NAME, SALARY
DECLARE FROM EMPLOYEES;
sal_diff NUMBER;
BEGIN
-- Handling INSERT operation
IF INSERTING THEN
DBMS_OUTPUT.PUT_LINE('Inserting New Employee: ' || :NEW.LAST_NAME);
DBMS_OUTPUT.PUT_LINE('Starting Salary: ' || :NEW.SALARY);
-- Perform actual insert action
INSERT INTO EMPLOYEES (EMPLOYEE_ID, LAST_NAME, SALARY)
VALUES (:NEW.EMPLOYEE_ID, :NEW.LAST_NAME, :NEW.SALARY);

ELSIF UPDATING THEN

sal_diff := :NEW.SALARY - :OLD.SALARY;
DBMS_OUTPUT.PUT_LINE('Updating Employee: ' || :NEW.LAST_NAME);
DBMS_OUTPUT.PUT_LINE('Old salary = ' || :OLD.SALARY || ', New salary = ' || :NEW.SALARY);
DBMS_OUTPUT.PUT_LINE('Difference: ' || sal_diff);

INSTEAD OF Trigger -- Perform actual update action

UPDATE EMPLOYEES
SET SALARY = :NEW.SALARY
WHERE EMPLOYEE_ID = :OLD.EMPLOYEE_ID;

ELSIF DELETING THEN

DBMS_OUTPUT.PUT_LINE('Deleting Employee: ' || :OLD.LAST_NAME);
DBMS_OUTPUT.PUT_LINE('Final Salary: ' || :OLD.SALARY);
-- Perform actual delete action
DELETE FROM EMPLOYEES WHERE EMPLOYEE_ID = :OLD.EMPLOYEE_ID;
END IF;
END;
/

55
 Instead Of Event: Log Employee Salary Changes
and Operations – Solution
View-Based Operations
▪ The trigger is defined on a view named EMPLOYEES_VIEW, not directly on the base table EMPLOYEES.
▪ This approach allows controlled manipulation of data presented through the view, especially when the view is complex or combines multiple tables.

INSTEAD OF Trigger
▪ The INSTEAD OF trigger intercepts INSERT, UPDATE, and DELETE operations on the view.
▪ It allows you to define custom logic that replaces the default behavior—like logging, validating, or rerouting the changes to the underlying table (EMPLOYEES in this case).

Use of DBMS_OUTPUT
▪ The DBMS_OUTPUT.PUT_LINE procedure is used for logging purposes.
▪ It prints useful information (like salary differences) to help with debugging or auditing.

Custom Actions
▪ The trigger can include custom logic, such as logging operations to a separate audit table, enforcing rules, or notifying users.
▪ After logging or validation, the actual DML operation is performed manually on the EMPLOYEES table from within the trigger body.

Important Considerations

1. No Direct Changes to the View:

▪ INSTEAD OF triggers don't modify the view's data directly.
▪ They handle the logic needed to manipulate the underlying base table(s) instead.
2. Only for Views:
▪ INSTEAD OF triggers are only applicable to views.
▪ They are especially useful for making non-updatable views behave like updatable ones by
defining what should happen during DML operations.

56
 Oracle Trigger Execution Order with FOLLOWS Clause

In Oracle, you can define multiple triggers for the same timing point (e.g., BEFORE INSERT, AFTER UPDATE). However, before Oracle 11g, their execution order was not
guaranteed, which could cause inconsistent behavior in complex systems.
Starting with Oracle 11g, the FOLLOWS clause allows you to explicitly control the execution order of such triggers. This ensures predictable and consistent behavior when multiple triggers
are defined for the same timing point.

Example: The following demonstrates how to create a table and two BEFORE INSERT triggers, using FOLLOWS to define their execution order.

▪ Table Created
▪ Trigger 1 created ▪ Trigger 2 created

-- Create a table to test trigger execution

CREATE TABLE trigger_follows_test ( -- Create the second trigger with FOLLOWS clause
id NUMBER, -- Unique identifier for each row CREATE OR REPLACE TRIGGER trigger_follows_test_trg_2
description VARCHAR2(50) -- Description for each row BEFORE INSERT ON trigger_follows_test -- Trigger fires before an insert
); FOR EACH ROW -- Fires for each row inserted
FOLLOWS trigger_follows_test_trg_1 -- Specifies that this trigger executes after
-- Create the first trigger trigger_1
CREATE OR REPLACE TRIGGER trigger_follows_test_trg_1 BEGIN
BEFORE INSERT ON trigger_follows_test -- Trigger fires before an insert -- Print message indicating this trigger was executed
FOR EACH ROW -- Fires for each row inserted DBMS_OUTPUT.put_line('TRIGGER_FOLLOWS_TEST_TRG_2 - Executed');
BEGIN END;
-- Print message indicating this trigger was executed /
DBMS_OUTPUT.put_line('TRIGGER_FOLLOWS_TEST_TRG_1 - Executed');
END;
/

57
 SET SERVEROUTPUT ON; ▪ Insert data in table trigger_follows_test
▪ Verify our records from the table
-- Test the triggers by inserting a record ▪ Trigger 3 created
INSERT INTO trigger_follows_test (id, description) ▪ Insert again data in table trigger_follows_test
VALUES (1, 'Testing trigger execution order');

-- Verify the inserted record

SELECT * FROM trigger_follows_test;

-- Create a third trigger to demonstrate multiple FOLLOWS relationships

CREATE OR REPLACE TRIGGER trigger_follows_test_trg_3
BEFORE INSERT ON trigger_follows_test
FOR EACH ROW
FOLLOWS trigger_follows_test_trg_2
BEGIN
DBMS_OUTPUT.put_line('TRIGGER_FOLLOWS_TEST_TRG_3 - Executed');
Trigger Execution END;
/ -- Test with the third trigger
Order with - Test INSERT INTO trigger_follows_test (id, description)
VALUES (2, 'Testing with three triggers');

Triggers

Output

Clean up (optional)
DROP TABLE trigger_follows_test CASCADE
CONSTRAINTS;

58
 Oracle Trigger Execution Order with FOLLOWS
Clause - Summary

▪ Without the FOLLOWS clause: The execution order of triggers at the

same timing point is unpredictable.
▪ With the FOLLOWS clause: You can explicitly define the execution order,
ensuring consistent and predictable behavior.

The FOLLOWS clause is especially useful in complex systems where triggers rely on
the output of one another.

59
 Compound Triggers in Oracle PL/SQL

Share Data Across Timing Improve Performance with Avoid Mutating Table
Points Bulk Operations Errors
A compound trigger in PL/SQL (introduced in Oracle 11g) is a
powerful feature that allows you to define multiple trigger timing
points—such as:
▪ BEFORE STATEMENT
▪ BEFORE EACH ROW
▪ AFTER EACH ROW By deferring certain
Instead of processing operations and using
▪ AFTER STATEMENT Compound triggers let
each row individually, temporary storage,
—within a single trigger block on the same table. you share variables and
you can collect data compound triggers help
temporary data across
during row-level events prevent mutating
different timing
This unified structure helps you manage complex business logic more and process it in bulk in table errors, which
sections, enabling
efficiently and avoids common issues like the mutating table the AFTER STATEMENT occur when a trigger
consistent and
section—boosting accesses or modifies
error, which can occur with regular row-level triggers. simplified processing.
performance. the table that caused it
to fire.

60
 Restrictions of Compound Triggers

Compound Structure Required:

DML-Only:
The trigger body must follow the
Compound triggers can only be defined for
compound trigger syntax, defining sections
INSERT, UPDATE, or DELETE operations
like BEFORE STATEMENT, BEFORE EACH
on tables or views.
ROW, etc.

:OLD and :NEW Scope Limited:

No Initialization Block:
These pseudorecords are only accessible
You cannot include an initialization
in BEFORE EACH ROW and AFTER EACH
block. All variables must be initialized in
ROW sections—not in DECLARE, BEFORE
the declaration or within timing sections.
STATEMENT, or AFTER STATEMENT.

Unpredictable Firing Order:

If multiple compound triggers exist on the
same table, their firing order is not
guaranteed unless controlled using
FOLLOWS or PRECEDES. 61
 Best Practices for Writing Compound Triggers

1 2 3 4

Use Shared Variables: Apply Bulk Processing: Keep Logic Modular: Prevent Mutating Table
Declare variables in the Collect data during row-level Distribute logic cleanly across Errors:
trigger body to share data sections and process it in bulk timing points. Avoid Temporarily store row-level
across timing points. in the AFTER STATEMENT overloading any single section data and act on it in AFTER
section for better with complex code. STATEMENT to avoid
performance. accessing the table while
it's being modified.

62
 Advantages of Compound Triggers

Simplified Maintenance:
Performance Boost: Error Prevention: Enhanced Flexibility:
Combines logic across multiple
Improves performance with bulk Helps avoid mutating table Allows logic to execute at
timing points into one unified
processing, especially in high- errors and other common trigger different stages of the DML
trigger, reducing code
volume operations. pitfalls. lifecycle.
duplication.

63
 CREATE OR REPLACE TRIGGER trigger_name
FOR INSERT OR UPDATE OR DELETE ON object_name
[REFERENCING OLD AS old NEW AS new]
COMPOUND TRIGGER
Optional Section
-- Declaration Section (optional)
-- Shared between all timing points
variable_name datatype;

-- Before Statement Section (optional)

BEFORE STATEMENT IS
BEGIN
-- Code executed once before the statement
EXCEPTION
WHEN OTHERS THEN
-- Error handling code (optional)
END BEFORE STATEMENT;

-- Before Each Row Section (optional)

BEFORE EACH ROW IS

Compound
BEGIN
-- Code executed before each row operation
EXCEPTION

Triggers Syntax WHEN OTHERS THEN

-- Error handling code (optional)
END BEFORE EACH ROW;

-- After Each Row Section (optional)

AFTER EACH ROW IS
BEGIN
-- Code executed after each row operation
EXCEPTION
WHEN OTHERS THEN Source Image: https://www.guru99.com/triggers-pl-sql.html
-- Error handling code (optional)
END AFTER EACH ROW;
Syntax Explanation:
-- After Statement Section (optional)
AFTER STATEMENT IS
▪ This syntax shows the creation of ‘COMPOUND’ trigger.
BEGIN
▪ Declarative section is common for all the execution block in
-- Code executed once after the statement
the trigger body.
EXCEPTION
▪ These 4 timing blocks can be in any sequence. It is not
WHEN OTHERS THEN
mandatory to have all these 4 timing blocks. We can create a
-- Error handling code (optional)
COMPOUND trigger only for the timings which are required.
END AFTER STATEMENT;

END trigger_name; 64
“The function of education is to teach one
to think intensively and to think critically"
— Martin Luther King Jr.

65
 CREATE OR REPLACE TRIGGER trg_student_changes
FOR INSERT OR UPDATE OR DELETE ON students
COMPOUND TRIGGER

-- Declaration Section: Shared between all timing points

row_counter INTEGER := 0;

-- Before Statement Section

BEFORE STATEMENT IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Starting DML operation on STUDENTS table...');
EXCEPTION -- After Each Row Section
WHEN OTHERS THEN AFTER EACH ROW IS
DBMS_OUTPUT.PUT_LINE('Error in BEFORE STATEMENT: ' || SQLERRM); BEGIN
END BEFORE STATEMENT; row_counter := row_counter + 1;
-- Before Each Row Section
BEFORE EACH ROW IS IF INSERTING THEN
BEGIN DBMS_OUTPUT.PUT_LINE('Inserted: ' || :NEW.student_name || ',
-- Validate GPA range GPA: ' || :NEW.gpa);

Example of
IF INSERTING OR UPDATING THEN ELSIF UPDATING THEN
IF :NEW.gpa < 0 OR :NEW.gpa > 5 THEN DBMS_OUTPUT.PUT_LINE('Updated: From GPA ' || :OLD.gpa || ' to '
RAISE_APPLICATION_ERROR(-20001, 'GPA must be between 0 and 5');
|| :NEW.gpa);

Compound (e.g.
END IF;
ELSIF DELETING THEN
-- Ensure student name is not NULL DBMS_OUTPUT.PUT_LINE('Deleted: ' || :OLD.student_name);

Student Table) IF :NEW.student_name IS NULL THEN

RAISE_APPLICATION_ERROR(-20002, 'Student name cannot be NULL');
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
END IF;
DBMS_OUTPUT.PUT_LINE('Error in AFTER EACH ROW: ' ||
EXCEPTION
WHEN OTHERS THEN SQLERRM);
DBMS_OUTPUT.PUT_LINE('Error in BEFORE EACH ROW: ' || SQLERRM); END AFTER EACH ROW;
END BEFORE EACH ROW;
-- After Statement Section
AFTER STATEMENT IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Total rows processed: ' || row_counter);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error in AFTER STATEMENT: ' ||
SQLERRM);
END AFTER STATEMENT;

END trg_student_changes;
/
66
Example of
Compound (e.g. Test by updating

Student Table) → UPDATE students

SET gpa = 3.9

Test WHERE student_id = 1;

Test by delete

DELETE students
WHERE studentId = 15;

67
 Example: Auditing Data Changes with a Compound Trigger

How can we use a compound trigger to track and log salary changes (inserts, updates, and deletes) on the EMPLOYEES table into an AUDIT_EMP table, while ensuring performance
and avoiding mutating table errors?

➔ Purpose
The compound_salary_audit trigger tracks salary changes on the EMPLOYEES table and stores them in a separate salary_audit table using bulk processing for better performance and
to avoid mutating table errors. write this clearly as a question
Object Type and Nested Table
Objects Involved
-- Audit Table -- Create an object type to represent a single employee's salary change
CREATE OR REPLACE TYPE employee_salary_obj AS OBJECT (
CREATE TABLE salary_audit ( employee_id NUMBER, -- Employee's ID
employee_id NUMBER, old_salary NUMBER, -- Salary before the update
old_salary NUMBER, new_salary NUMBER -- Salary after the update
new_salary NUMBER, );
change_date DATE /
);
-- Create a nested table type to store multiple employee salary change records
CREATE OR REPLACE TYPE employee_salary_tab IS TABLE OF employee_salary_obj;
/

68
 -- Create a compound trigger to audit salary updates in the EMPLOYEES table
CREATE OR REPLACE TRIGGER compound_salary_audit
FOR UPDATE OF SALARY ON EMPLOYEES
COMPOUND TRIGGER

-- Declare a nested table variable to collect salary changes during the statement
salary_changes employee_salary_tab := employee_salary_tab();

-- BEFORE EACH ROW section: captures the salary change before each row is updated
BEFORE EACH ROW IS
BEGIN
-- Extend the nested table to add a new entry
salary_changes.EXTEND;

-- Store the current row's employee ID, old salary, and new salary
salary_changes(salary_changes.COUNT) := employee_salary_obj(

Audit Salary with

:OLD.EMPLOYEE_ID,
:OLD.SALARY,
:NEW.SALARY

Shared Object );
END BEFORE EACH ROW;

Type - Trigger -- AFTER STATEMENT section: runs once after all row updates are done
AFTER STATEMENT IS

Logic
BEGIN
-- Loop through the collected changes and insert them into the audit table
FOR i IN 1 .. salary_changes.COUNT LOOP
INSERT INTO salary_audit (
employee_id,
old_salary,
new_salary,
change_date
) VALUES (
salary_changes(i).employee_id,
salary_changes(i).old_salary,
salary_changes(i).new_salary,
SYSDATE
); -- Update salaries by 10% for employees in department 3
END LOOP; UPDATE EMPLOYEES
SET SALARY = SALARY * 1.10
-- Clear the nested table after inserting audit records WHERE DEPARTMENT_ID = 3;
salary_changes.DELETE;
END AFTER STATEMENT;
-- Verify audit logs
END compound_salary_audit; SELECT * FROM salary_audit;
/ 69
 Key Benefits
▪ Performance: Uses bulk logging instead of row-by-row INSERTs.
▪ Prevents Mutating Table Errors: Defers DML to AFTER STATEMENT.
▪ Structured Data: Uses object types and nested tables for clean handling.

Output

Audit Salary with

Shared Object
Type - Test Case

Output

70
 Question: Compound of Maintaining Employee Salary
Records Automatically
Scenario: Tasks:
Your company maintains two tables: 1. Write a Compound Trigger:
1. EMPLOYEES: Stores EMPLOYEE_ID, SALARY, and BONUS. ▪ Use a compound trigger to ensure the following:
2. EMPLOYEE_SALARIES: Tracks employee salary and bonus details for payroll. o During the statement-level operation, initialize necessary structures (if
needed).
Requirement: o At the row-level operation, update or insert into the EMPLOYEE_SALARIES
Whenever an employee is added or their SALARY or BONUS is updated in the EMPLOYEES table as required.
table, ensure that the EMPLOYEE_SALARIES table is updated to reflect the changes. Use a o Log any errors explicitly.
compound trigger to manage this logic efficiently. If the employee does not exist in ▪ Handle duplicate records gracefully to avoid conflicts in the EMPLOYEE_SALARIES
table.
EMPLOYEE_SALARIES, insert a new record.
2. Add Exception Handling:
Database Schema:
EMPLOYEES: ▪ Explicitly handle exceptions for UPDATE or INSERT operations.
▪ EMPLOYEE_ID (Primary Key)
▪ Log meaningful error messages for failures (e.g., primary key violations).
▪ SALARY
▪ BONUS 3. Test the Trigger:
EMPLOYEE_SALARIES: ▪ Insert a new employee into the EMPLOYEES table and verify changes in
▪ EMPLOYEE_ID (Primary Key) EMPLOYEE_SALARIES.
▪ SALARY ▪ Update an existing employee's SALARY or BONUS in EMPLOYEES and ensure the
changes are reflected in EMPLOYEE_SALARIES.
▪ BONUS
Validation Steps:
▪ Insert a new employee into EMPLOYEES and check the insertion into EMPLOYEE_SALARIES.
▪ Update an employee's salary or bonus and verify the update in EMPLOYEE_SALARIES.
▪ Test duplicate handling by attempting to insert an already existing record in
EMPLOYEE_SALARIES.

71
 Scenario-Based Question: Auditing Student Data with a
Compound Trigger at AUCA
Problem Statement: Student Data Auditing with Compound Triggers
You are the database administrator for Adventist University of Central Africa (AUCA). Your objective is to track and audit changes made to student records using a compound
trigger. This ensures that any updates to the student data are automatically logged in an audit table for accountability and historical tracking.

Task Overview
Follow the steps below to design and implement the auditing system:

Create the Required Tables

▪ students Table:
This table will store student records such as student_id, name, department, etc.
▪ students_audit Table:
This table will log updates made to the students table. Each log entry should capture:
o student_id
o action_type (e.g., 'UPDATE')
o change_date (timestamp of the update)

2. Insert Sample Data

▪ Populate the students table with several sample records for testing purposes.

3. Define a Compound Trigger

▪ Create a compound trigger on the students table that:
o Detects UPDATE operations.
o Uses a collection (nested table) to store student changes during the row-level operation.
o Performs bulk logging of these changes in the AFTER STATEMENT section by inserting entries into the students_audit table.
o Stores relevant data such as student_id, action_type, and change_date.

4. Test the Implementation

▪ Execute UPDATE statements on the students table.
▪ Verify that each change is accurately logged in the students_audit table. 72
 /*1. Create Student Table */

CREATE TABLE students (

student_id NUMBER PRIMARY KEY,
student_name VARCHAR2(100),
major VARCHAR2(50),
gpa NUMBER(3, 2)
);

/*2. Student audit table */

CREATE TABLE students_audit (
audit_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
student_id NUMBER,
action VARCHAR2(50),
change_date DATE
Auditing Student
Note:
);
In this setup, we are generating randomized sample data
for the students table using a loop. Each student is

Data with a /*
Step 2: Insert Sample Data
Populate the students table with sample data:
assigned:
▪ A unique student_id from 1 to 1000,

Compound
▪ A name in the format "Student 1", "Student 2",
*/ ..., "Student 1000",
BEGIN ▪ A major chosen cyclically from Computer
Optional FOR i IN 1..1000 LOOP
INSERT INTO students (student_id, student_name, major, gpa)
Science, Engineering, Business, and Arts,
▪ A GPA ranging from 2.5 to 2.9, calculated using
VALUES ( a simple formula to add variation.
i, This approach is used to simulate realistic and diverse
'Student ' || i, student data for testing the audit logging system.
CASE MOD(i, 4)
WHEN 0 THEN 'Computer Science'
WHEN 1 THEN 'Engineering'
WHEN 2 THEN 'Business'
ELSE 'Arts'
END,
2.5 + MOD(i, 5) * 0.1
);
END LOOP;
END;

-- Display data in students_audit;

SELECT * FROM students_audit;

73
 -- Step 4: Create necessary types for bulk processing
CREATE OR REPLACE TYPE student_audit_obj AS OBJECT (
student_id NUMBER
);
/
CREATE OR REPLACE TYPE student_audit_tab IS TABLE OF student_audit_obj;
/
-- Create the compound trigger
CREATE OR REPLACE TRIGGER compound_students_audit
FOR UPDATE ON students
COMPOUND TRIGGER
-- Declaration section: define the collection to hold audit info
student_changes student_audit_tab := student_audit_tab();

Solution: BEFORE EACH ROW IS

BEGIN

Compound Trigger -- Store only if there's a change in data

IF :OLD.gpa != :NEW.gpa THEN

to Track Updates
student_changes.EXTEND;
student_changes(student_changes.COUNT) := student_audit_obj(:OLD.student_id);
END IF;

on the students END BEFORE EACH ROW;

Table
AFTER STATEMENT IS
BEGIN
-- Bulk insert audit records for changed students
FOR i IN 1 .. student_changes.COUNT LOOP
INSERT INTO students_audit (
student_id,
action,
change_date
) VALUES (
student_changes(i).student_id,
'UPDATE',
SYSDATE
);
END LOOP;

-- Clear the collection

student_changes.DELETE;
END AFTER STATEMENT;

END compound_students_audit;
/ 74
 Output

-- Step 1: Update GPAs for all students whose GPA is currently below 4.0
-- This will trigger the compound trigger to log each update into the audit
table
UPDATE students
SET gpa = gpa + 0.5
WHERE gpa < 4.0;

Solution:
Compound Trigger Output

to Track Updates -- Step 2: Check the total number of records inserted into the
students_audit table
Test -- This should match the number of rows updated in the students
table
SELECT COUNT(*) FROM students_audit;

-- Step 3: Retrieve and review the first 10 audit records

-- Useful for verifying that the audit entries were correctly logged
SELECT * FROM students_audit
WHERE ROWNUM <= 10;

75
 Scenario Question: Managing Allowances Based on
Attendance
Here's a scenario that incorporates the creation and use of a compound trigger to manage related data across the ALLOWANCES and ATTENDANCE tables. The trigger
ensures that when an employee's attendance record indicates absence, an allowance record is automatically marked as not applicable (IS_APPLICABLE = 'N').
Scenario: Managing Allowances Based on Attendance
Objective:
Ensure that allowances are correctly updated based on employee attendance records. If an employee is marked absent on a particular date, all allowance entries linked
to their role and effective on or before that date are marked as not applicable (IS_APPLICABLE = 'N').

Steps

1. Tables Involved:
▪ ALLOWANCES: Tracks allowances assigned to roles.
▪ ATTENDANCE: Records daily attendance for employees.
2. Business Rule:
▪ If an employee is marked as absent (STATUS = 'Absent') on a specific date:
o All allowances tied to the employee's role (ROLE_ID) and effective on or before the absence date (EFFECTIVE_DATE) must have their IS_APPLICABLE field
updated to 'N'.
3. Technical Requirements:
▪ Use a compound trigger to handle the update in a transaction-safe manner.
▪ Use an object type to manage allowances updates as part of the trigger logic.

76
 Scenario Question: PL/SQL Compound Trigger Question:
Instagram System

Scenario:
You're working on the backend of an Instagram-like system where users can post pictures, like, comment, and follow other users. Your task is to implement compound triggers to
manage the operations within the system. A compound trigger allows you to group multiple trigger actions (such as INSERT, UPDATE, and DELETE) into a single trigger, which can
help to avoid potential issues with firing multiple triggers for the same event.

Requirements:
1.Create a Compound Trigger to Track New Posts:
▪ Every time a user posts a new picture, you need to automatically record the date and time of the post in a posts_audit table. This table has the following columns:
post_id, user_id, post_date.
Question:
▪ Write a compound trigger that will:
o Insert a record into the posts_audit table when a new post is added to the posts table.
2.Create a Compound Trigger for Comments on Posts:
▪ When a new comment is added to a post, you need to update the comments_count column in the posts table to reflect the new number of comments on that post.
Question:
▪ Write a compound trigger that will:
o Update the comments_count in the posts table whenever a new comment is added to a post in the comments table.
3.Create a Compound Trigger for User Follows:
▪ Every time a user follows another user, a notification should be sent to the followed user by inserting a new record into the notifications table. This table has the
following columns: notification_id, user_id, follower_id, notification_message, and notification_date.
Question:
▪ Write a compound trigger that will:
o Insert a record into the notifications table whenever a user follows another user.
4.Create a Compound Trigger to Prevent Multiple Posts in a Short Timeframe:
▪ If a user tries to post more than 5 times within 1 minute, the system should prevent the post from being added and raise an error message.
Question:
▪ Write a compound trigger that will:
o Restrict a user from posting more than 5 times within a minute and raise an appropriate error if the condition is met.

77
 Scenario Question: PL/SQL Compound Trigger Question:
Instagram System

Evaluation Criteria:
▪ Use of Compound Triggers: Understanding how to use the AFTER STATEMENT, AFTER EACH ROW, and BEFORE STATEMENT sections effectively within a
compound trigger.
▪ Trigger Logic: Correct handling of multiple actions (insert, update, delete) within a single trigger body.
▪ Error Handling: Proper error handling and raising exceptions when necessary (e.g., for the restriction on multiple posts).
▪ Table Relationships: Correct understanding of the relationships between tables (e.g., posts, comments, notifications).
▪ Performance Considerations: Efficient use of compound triggers to manage multiple actions at once, avoiding redundant or unnecessary triggers.

Bonus Question:
▪ How would you modify the compound trigger to include a logging mechanism that tracks the status of the post (whether it's public or private) every time a new post is
added to the system?

78
 Recap: PLSQL - Trigger

1. DML Triggers (Data 4. DDL Triggers (Data

2. INSTEAD OF Triggers 3. Compound Triggers 5. System Triggers
Manipulation Language) Definition Language)

These triggers are fired in Combine multiple DML events These are fired when DDL Triggered based on system-
response to DML ▪Specifically designed for (INSERT, UPDATE, DELETE) statements are executed: level events, such as:
operations on tables or views. into a single trigger block. ▪CREATE, ALTER, DROP ▪LOGON / LOGOFF
views: Useful to:
▪Allow performing INSERT, ▪TRUNCATE, RENAME ▪STARTUP / SHUTDOWN
▪INSERT UPDATE, or DELETE on views ▪Handle complex business ▪GRANT, REVOKE Often used for monitoring,
▪UPDATE that normally don’t support rules, Mostly used for auditing or security checks, or logging
▪DELETE direct DML operations. ▪Prevent mutating table enforcing database user sessions.
They can be defined as ▪Executed instead of the errors, policies.
BEFORE, AFTER, or DML operation on the view. ▪Use bulk processing for
INSTEAD OF (on views). better performance.

79
 Question:

You need to create a trigger in an Oracle database that automatically increases the salary of employees who were hired
in 2022 by 22.5% of their base salary. This trigger should activate after an INSERT or UPDATE operation on the
employees table to ensure that any new employee hired in 2022 receives the salary increase right away.

Problem Statement –
Create a Trigger

80
 Solution
To accomplish this, you will define a DML Trigger that will execute after a new employee is inserted or an existing
employee's hiring date is updated. The trigger will check if the hiring date falls within the year 2022 and, if so, apply the
22.5% increase to the employee's salary.

CREATE TABLE employees_test (

employee_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50) NOT NULL,
employees_test
hire_date DATE NOT NULL,
salary NUMBER(10, 2)
);

Insert record 1 employees_test c

INSERT INTO employees (employee_id, first_name, hire_date, salary)
VALUES (1, 'John', TO_DATE('2022-06-15', 'YYYY-MM-DD'), 50000);

Solution: Salary ▪ Drop Procedure

▪ Create new procedure

Increment
DROP TRIGGER trg_increase_salary;

CREATE OR REPLACE TRIGGER trg_increase_salary

BEFORE INSERT OR UPDATE ON employees_test
FOR EACH ROW
BEGIN
BEGIN
IF EXTRACT(YEAR FROM :NEW.hire_date) = 2022 THEN
:NEW.salary := :NEW.salary * 1.225;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error in trg_increase_salary: ' || SQLERRM);
RAISE;
END;
END;
/

81
 Insert record 1 employees_test
INSERT INTO employees_test (employee_id, first_name, hire_date, salary)
VALUES (3, 'Kaka', TO_DATE('2022-03-10', 'YYYY-MM-DD'), 7000);

-- Check result
SELECT * FROM employees_test WHERE employee_id = 3;

UPDATE employees_test
SET hire_date = TO_DATE('2022-09-01', 'YYYY-MM-DD'),
salary = 40000
WHERE employee_id = 3;

-- Check result

Solution: Salary
SELECT * FROM employees_test WHERE employee_id = 3;

Increment &
Update & Test

Output

82
 Exercise: Enhancing Accountability and Insights Through
Employee Salary Change Tracking
Exercise: Enhancing Accountability and Insights Through Employee Salary Change Tracking

Problem Statement
Context:
In a fast-paced business environment, managing employee compensation is critical for retaining talent and fostering employee satisfaction. Salary adjustments—whether due to
performance evaluations, promotions, or market shifts—occur frequently and must be tracked transparently. A clear mechanism for monitoring these changes helps HR ensure
accountability and make data-driven compensation decisions.

Challenge:
How can the organization implement an effective strategy to monitor and analyze salary changes recorded in the EMPLOYEES table, ensuring transparency, accountability, and strategic
decision-making?

Questions to Explore:
1.How can we systematically log each salary change—including the old salary, new salary, and the difference—to create a reliable historical record of compensation adjustments?
2.What potential effects might salary changes have on employee performance, satisfaction, and organizational equity?
3.How can HR utilize the recorded salary change data to identify trends, assess fairness, and optimize compensation policies?
4.How can we implement this logging mechanism (e.g., using a trigger like display_salary_changes) in a way that minimizes performance overhead, especially during bulk data
operations?

Objective
Design and implement a robust mechanism (such as a trigger) that automatically logs all salary changes made to the EMPLOYEES table. This mechanism should support real-time
monitoring, empower HR with actionable insights, and promote fairness and transparency across the organization.

Exercise - Problem statement that relates to the trigger functionality

and its implications in a business context:
83
 Question: Tracking Gold Stock Changes in Ngali Mining Ltd: Logging Updates for Mines Except CEO-Owned Operations

In a mining company like Ngali Mining Ltd, especially in a gold mining operation, it is essential to track changes related to the
management and allocation of resources like gold production and compensation adjustments. The company aims to ensure
transparency in its operations, monitor resources effectively, and maintain fair practices.
Given the MINES table in Ngali Mining Ltd's Gold Mining System, create a conditional trigger named log_gold_changes that
fires before any DELETE, INSERT, or UPDATE operation on the table. The trigger should only log gold-related changes for mines that
do not belong to the CEO or top executives. For other mines, it should log the mine name, old gold stock, new gold stock, and
the difference in gold.

Specifications:
▪ Trigger Activation:
The trigger should fire only when the mine’s owner is not the 'CEO' (or any top executives).
▪ Output Format:
The output should log the mine’s name, old gold stock, new gold stock, and the difference in gold.

Objective:
The goal of this trigger is to ensure that Ngali Mining Ltd can easily track and review changes in gold stock levels for all mines except
those owned by the CEO or top executives. This mechanism will help the company maintain a transparent record of resource changes,

Question: Tracking
aiding in operational decision-making and fair distribution of gold production.

Gold Change

84
 Difference Between Cursor and Trigger in PL/SQL

Cursor Trigger 4. Conclusion

▪ Cursors: Best for controlled, row-by-row data
processing.
Processes rows one by one. Executes automatically on events.
▪ Triggers: Ideal for automating responses to database
events to maintain data consistency.
Can be defined inside a trigger. Cannot be created within a cursor.

Created during SQL execution. Stored in the database, triggered by events.

Activated by SQL statements. Triggered by DDL/DML events.

85
 Application of Triggers

Triggers are used to automatically execute PL/SQL code in response to specific database events like INSERT, UPDATE, DELETE, or DDL operations (CREATE, ALTER, DROP). They help
maintain data integrity, enforce business logic, audit activity, and automate data processes without user intervention.

Enforcing Business Maintaining Derived Complex Data Data

Auditing Changes:
Rules: Values: Validation: Synchronization:

Common Uses of Log data modifications Prevent invalid Recalculate fields on Apply rules beyond Keep related tables
Triggers automatically. transactions. changes. standard constraints. consistent.
E.g., Track old and new E.g., Block order E.g., Update BONUS or E.g., Prevent deleting a E.g., Delete linked
salaries when an insertion if a customer TAX when salary is parent record if related records when an
employee’s salary is exceeds their credit modified. child records exist. employee is removed.
updated. limit.

86
 Be Cautious: What to Know Before Using Triggers

Triggers are used to automatically execute PL/SQL code in response to specific database events like INSERT, UPDATE, DELETE, or DDL operations (CREATE, ALTER, DROP). They help
maintain data integrity, enforce business logic, audit activity, and automate data processes without user intervention.

Triggers are special procedures that automatically execute in response to specific events on a table or view. They are commonly used to
enforce business rules, ensure data integrity, audit data changes, and automate workflows without manual intervention.

However, while triggers are powerful, they should be used with care:

Avoid complex logic that can affect performance.

Be mindful of mutating table errors.

Ensure that triggers do not create unintended recursive effects.

Always test thoroughly to confirm expected behavior.

87
You will never change your life until you change something you do daily. The secret of
your success is found in your daily routine.
– John C. Maxwell

88
Keep Learning!

89