What is Trigger in Oracle with Example?

Trigger in Oracle

Modern relational database systems, like Oracle Database, have the essential capacity of implementing triggers, which enables the codification and automatic enforcement of business rules. A trigger is a particular kind of stored program unit, often written in PL/SQL, that “fires,” or runs automatically, in response to predetermined database events, usually Data Manipulation Language (DML) statements like INSERT, UPDATE, or DELETE. For the user carrying out the DML operation, the triggers’ execution is transparent.

The timing (BEFORE or AFTER) and execution scope (Row-Level or Statement-Level) of triggers can be used to categorise them. Common in auditing systems, row-level triggers run once for each row impacted by a DML statement. Statement-level triggers only run once for the whole DML statement, regardless of the number of rows they impact.

The primary DML trigger types depend on the timing in relation to the transaction that started:

DML Trigger Timing and Types

Triggers can be set to occur immediately before or after the DML event. To facilitate conditional logic and data manipulation within row-level triggers, Oracle provides pseudo-records named :OLD and :NEW. The :OLD pseudo-record holds the values of the columns prior to the DML operation (available for UPDATE and DELETE), while the :NEW pseudo-record holds the values the row will contain after the DML (available for INSERT and UPDATE). Note that these pseudo-records must be preceded by a colon (:) when referenced inside the PL/SQL body of the trigger.

Before Insert Trigger

A Before Insert Trigger executes before a new row is permanently added to the table. This timing is essential if you need to set the value of a column in the inserted row based on logic defined in the trigger body.

Common Use Case: Automatically populating audit columns (like creation date or default values) or deriving column values before the row is finalized.

In this context, only the :NEW pseudo-record is populated, and its values can be modified.

Code Example (Conceptual Setup and Before Insert):

First, define a sample table (EMPLOYEES) and a log table (EMPLOYEE_LOG) for tracking changes:

-- Setup Tables
CREATE TABLE EMPLOYEES (
    emp_id       NUMBER PRIMARY KEY,
    emp_name     VARCHAR2(50),
    salary       NUMBER(10, 2),
    created_date DATE,
    modified_date DATE
);
/

CREATE TABLE EMPLOYEE_LOG (
    log_id          NUMBER GENERATED ALWAYS AS IDENTITY,
    emp_id          NUMBER,
    operation_type  VARCHAR2(10),
    old_salary      NUMBER(10, 2),
    new_salary      NUMBER(10, 2),
    audit_date      DATE
);
/

-- Before Insert Trigger (Populating CREATED_DATE)
CREATE OR REPLACE TRIGGER EMP_BIU_ROW
BEFORE INSERT OR UPDATE ON EMPLOYEES
FOR EACH ROW
BEGIN
    -- Only set created_date if inserting OR if it is not already specified
    IF INSERTING THEN
        IF :NEW.created_date IS NULL THEN
            :NEW.created_date := SYSDATE;
        END IF;
    END IF;
    
    -- Always set modified_date on insert or update
    IF INSERTING OR UPDATING THEN
        :NEW.modified_date := SYSDATE;
    END IF;
END;
/
-- Output: Trigger created.

This single trigger is a combination of Before Insert and Before Update logic. When inserting, if created_date is null, the trigger explicitly sets the :NEW.created_date to SYSDATE.

After Insert Trigger

An After Insert Trigger executes only after the row has been successfully inserted (and has therefore passed any declarative referential integrity constraints).

Common Use Case: Auditing, logging the successful action, or replicating the change asynchronously to another table.

Both :NEW and :OLD pseudo-records are available, but only :NEW contains meaningful data related to the inserted row.

Code Example (After Insert Logging, integrated with the above BIU trigger):

-- After Insert Trigger (Logging insertion to EMPLOYEE_LOG)
CREATE OR REPLACE TRIGGER EMP_AFI_LOG
AFTER INSERT ON EMPLOYEES
FOR EACH ROW
BEGIN
    INSERT INTO EMPLOYEE_LOG (emp_id, operation_type, new_salary, audit_date)
    VALUES (:NEW.emp_id, 'INSERT', :NEW.salary, SYSDATE);
END;
/
-- Output: Trigger created.

-- Test Insert:
INSERT INTO EMPLOYEES (emp_id, emp_name, salary) VALUES (101, 'JILL', 60000);
COMMIT;
-- Output: 1 row created. Commit complete.

-- Check data and audit log output:
SELECT emp_id, created_date, modified_date FROM EMPLOYEES WHERE emp_id = 101;
-- Output (dates will reflect current SYSDATE/time):
-- EMP_ID  CREATED_DATE       MODIFIED_DATE
-- 101     [SYSDATE]          [SYSDATE]

SELECT emp_id, operation_type, new_salary FROM EMPLOYEE_LOG WHERE emp_id = 101;
-- Output:
-- EMP_ID  OPERATION_TYPE  NEW_SALARY
-- 101     INSERT          60000

Before Update Trigger

A Before Update Trigger executes before the existing row is modified. This type of trigger is highly flexible because both the original (:OLD) and proposed new values (:NEW) are available and modifiable.

Common Use Case: Implementing complex validation rules, ensuring specific columns are updated only under certain conditions (preventing changes), or performing conditional auditing before the change is finalized.

Code Example (Before Update Validation/Derivation):

We use the previous EMP_BIU_ROW trigger definition to illustrate update functionality. When an update occurs, it guarantees modified_date is updated to SYSDATE.

We can add logic to restrict updates to the salary column only if the new salary is higher than the old salary, or log the change if it is lower:

-- Before Update Trigger (Conditional check based on old vs new salary)
CREATE OR REPLACE TRIGGER EMP_SALARY_CHECK
BEFORE UPDATE OF salary ON EMPLOYEES
FOR EACH ROW
WHEN (NEW.salary < OLD.salary) -- Note: No colon needed in WHEN clause 
BEGIN
    -- Log a warning or raise an application error if salary is lowered 
    -- (This logic uses the audit log, typically this might raise an error)
    RAISE_APPLICATION_ERROR(-20003, 'Salary cannot be lowered via direct update.');
END;
/
-- Output: Trigger created.

-- Test Update Attempt (Lowers salary):
UPDATE EMPLOYEES SET salary = 50000 WHERE emp_id = 101;
-- Output:
-- ERROR at line 1: ORA-20003: Salary cannot be lowered via direct update.

After Update Trigger

The row must successfully insert (and hence pass any declarative referential integrity requirements) before an After Insert Trigger can be executed.

Common Use Case: There are pseudo-records with the suffixes :NEW and :OLD, but only :NEW has useful information about the inserted row.

Code Example (After Update Logging):

-- After Update Trigger (Logging salary changes)
CREATE OR REPLACE TRIGGER EMP_AFU_LOG
AFTER UPDATE OF salary ON EMPLOYEES
FOR EACH ROW
BEGIN
    -- Log the old and new salaries only if they are different (recommended practice)
    IF :OLD.salary <> :NEW.salary THEN
        INSERT INTO EMPLOYEE_LOG (emp_id, operation_type, old_salary, new_salary, audit_date)
        VALUES (:NEW.emp_id, 'UPDATE', :OLD.salary, :NEW.salary, SYSDATE);
    END IF;
END;
/
-- Output: Trigger created.

-- Test successful update (increases salary):
UPDATE EMPLOYEES SET salary = 70000 WHERE emp_id = 101;
COMMIT;
-- Output: 1 row updated. Commit complete.

-- Check audit log output:
SELECT operation_type, old_salary, new_salary FROM EMPLOYEE_LOG WHERE operation_type = 'UPDATE';
-- Output:
-- OPERATION_TYPE  OLD_SALARY  NEW_SALARY
-- UPDATE          60000       70000

Before Delete Trigger

A Before Delete Trigger executes just prior to the row being deleted.

Common Use Cases: Carrying out conditional auditing prior to the change being finalised, implementing intricate validation rules, or making sure particular columns are updated only in specific circumstances (preventing changes).

In this context, only the :OLD pseudo-record is available, as the :NEW row will cease to exist.

Code Example (Before Delete Security Check):

-- Before Delete Trigger (Preventing deletion on weekends, conceptual example adapted from )
CREATE OR REPLACE TRIGGER EMP_BFD_SECURITY
BEFORE DELETE ON EMPLOYEES
BEGIN
    -- This is a statement-level trigger (FOR EACH ROW clause omitted)
    IF TO_CHAR(SYSDATE, 'DY') IN ('SAT', 'SUN') THEN
        RAISE_APPLICATION_ERROR(-20004, 'Deletions not allowed on weekends.');
    END IF;
END;
/
-- Output: Trigger created.

After Delete Trigger

Once the row alteration attempt has been successful, an After Update Trigger is executed.

Common Use Case: Auditing modifications by recording both the :OLD and NEW values in a different table.

Code Example (After Delete Logging):

-- After Delete Trigger (Logging deleted records)
CREATE OR REPLACE TRIGGER EMP_AFD_LOG
AFTER DELETE ON EMPLOYEES
FOR EACH ROW
BEGIN
    INSERT INTO EMPLOYEE_LOG (emp_id, operation_type, old_salary, audit_date)
    VALUES (:OLD.emp_id, 'DELETE', :OLD.salary, SYSDATE);
END;
/
-- Output: Trigger created.

-- Test deletion:
DELETE FROM EMPLOYEES WHERE emp_id = 101;
COMMIT;
-- Output: 1 row deleted. Commit complete.

-- Check audit log output:
SELECT operation_type, old_salary FROM EMPLOYEE_LOG WHERE operation_type = 'DELETE';
-- Output:
-- OPERATION_TYPE  OLD_SALARY
-- DELETE          70000

Trigger Management Commands

To manage the existence and state of triggers, Oracle offers a number of SQL commands that let developers and administrators enable, disable, or completely delete them.

Disable Trigger (Specific) and Enable Trigger (Specific)

By utilising the ALTER TRIGGER command in conjunction with the ENABLE or DISABLE keywords, individual triggers can be explicitly enabled or disabled. Either owning the trigger or possessing the ALTER ANY TRIGGER system privilege are necessary for this.

Disabling a Specific Trigger:

ALTER TRIGGER EMP_AFI_LOG DISABLE;
-- Output: Trigger altered.

Enabling a Specific Trigger:

ALTER TRIGGER EMP_AFI_LOG ENABLE;
-- Output: Trigger altered.

The database won’t fire a trigger that is disabled, even if the triggering circumstance is met.

Disable All Triggers and Enable All Triggers

In replication circumstances or when handling huge data loads (loading, copying, or moving data), it is frequently required to temporarily manage all triggers connected to a table. During large loads, performance can be significantly enhanced by turning off triggers. Disabling triggers on target tables is essential for replication technologies such as Oracle GoldenGate because the replication system already sends the output of trigger operations from the database, preventing duplicate changes.

The ALTER TABLE command with the ALL TRIGGERS clause is used to do this management.

Disable All Triggers:

This command disables all triggers associated with a specified table.

ALTER TABLE EMPLOYEES DISABLE ALL TRIGGERS;
-- Output: Table altered.

Enable All Triggers:

This command enables all triggers associated with a specified table.

ALTER TABLE EMPLOYEES ENABLE ALL TRIGGERS;
-- Output: Table altered.

You need to have the ALTER ANY TABLE system privilege or own the table in order to utilise the ALTER TABLE command for trigger management. If triggers were disabled during a data load, the administrator will need to manually carry out the data modification that would have been carried out by the disabled triggers.

Drop Trigger

A trigger definition can be permanently deleted from the database by using the Drop Trigger command. After being dumped, the trigger is no longer present and will not react to events.

You need to either own the trigger or have the system privilege DROP ANY TRIGGER in order to drop it.

Code Example:

DROP TRIGGER EMP_AFI_LOG;
-- Output: Trigger dropped.

The trigger name is all that is needed in the syntax for the DROP TRIGGER statement. An error or the incorrect trigger may be dropped if the trigger name is not explicitly supplied, depending on the environment and schema context.