Theory
The midnight ticket tamperer
Imagine a system user logs into TicketDesk at 2 AM. They manually update a critical bug ticket from Open to Closed without doing any actual work, simply to artificially boost their performance score. The team leader arrives in the morning to discover a broken system and has no record of who modified the data. How can we force the database engine to automatically catch bad updates, track down the culprit, and write an immutable audit log without depending on human honesty?
Theory
The Motion-Activated Security Camera
If you want to protect a jewelry showroom, you do not hire a guard who sits waiting for you to call them when a thief breaks in. Instead, you install a motion-activated security camera. The camera lies completely dormant until someone crosses the sensor path. The moment movement occurs, it activates instantly and takes a snapshot. A database trigger is that exact security camera: it sits silently inside your tables and fires automatically the split second a data alteration occurs.
Theory
Defining Database Triggers
In the Oracle database dialect, a database trigger is a named, stored PL/SQL block that automatically executes, or fires, in direct response to a specific table event. These events consist of Data Manipulation Language (DML) actions like INSERT, UPDATE, or DELETE. Triggers are completely separate from stored procedures because they cannot be manually invoked or executed by user applications. Instead, the Oracle database engine runs them transparently whenever table modifications happen.
At a glance
Availability of :OLD and :NEW qualifiers during different table events
| DML Operation | Old Values (:OLD) | New Values (:NEW) |
|---|---|---|
| INSERT | Entirely NULL because no previous record exists | Contains the newly incoming data fields |
| UPDATE | Contains data fields as they existed before the query | Contains the proposed replacement data fields |
| DELETE | Contains data fields as they existed before removal | Entirely NULL because the record no longer exists |
Practical
Building a Ticket Audit Log Trigger
-- Enable terminal output
SET SERVEROUTPUT ON;
-- Create trigger to automatically track status modifications
CREATE OR REPLACE TRIGGER trg_audit_ticket_status
BEFORE UPDATE OF status ON tickets
FOR EACH ROW
BEGIN
-- Insert old and new values into a history table
INSERT INTO ticket_logs (ticket_id, old_status, new_status, changed_on)
VALUES (:OLD.id, :OLD.status, :NEW.status, SYSDATE);
END;
/Theory
Deconstructing the Audit Trigger Syntax
Let us examine the execution mechanics. The instruction BEFORE UPDATE OF status ON tickets instructs Oracle to execute our custom logic just before the update statement is saved permanently. The FOR EACH ROW qualifier is essential: it specifies a row-level trigger, allowing us to inspect every separate record modified by the query. Inside the block body, we use the :OLD keyword to read historical values and the :NEW keyword to examine the incoming data fields before they overwrite the table.
Quiz
If an INSERT statement runs inside a table containing a row-level trigger, what data will the pseudo-record attribute :OLD.title contain?
- The title string of the last row added previously
- It will evaluate to NULL
- A default string value generated by Oracle
- The compiler will fail and raise a syntax error
Show the answer
It will evaluate to NULL
During an INSERT operation, a fresh record is being introduced where no historical row existed before. Because there is no old data to point to, all attributes within the :OLD pseudo-record automatically evaluate to NULL without throwing any error.
Watch out
The Missing FOR EACH ROW Trap
The most frequent error Indian BCA students make in laboratory practical exams is omitting the FOR EACH ROW phrase. Without this specific clause, Oracle creates a statement-level trigger. Statement-level triggers execute exactly once per query, whether you modify 1 row or 1000 rows. If you try to reference row-specific qualifiers like :NEW or :OLD inside a statement-level trigger, the Oracle compiler will instantly crash and throw a compilation error.
Think first
Intercepting and Altering Data
Imagine a user submits a ticket with the priority typed in lowercase letters. If we want our trigger to intercept the record and force the value to uppercase automatically before it saves, should we use a BEFORE trigger or an AFTER trigger? Work out the answer mentally before tapping.
Show the answer
You must use a BEFORE trigger! A BEFORE row-level trigger gives you the unique ability to modify incoming records directly using assignment statements like :NEW.priority := UPPER(:NEW.priority);. If you try to change a :NEW attribute inside an AFTER trigger, Oracle will throw a severe execution error because the data has already been finalized and committed to the storage block.
Theory
Enterprise Compliance and Next Semester Mobile Data
In production banking infrastructures, backend engineers rely on database triggers to enforce audit compliance and catch financial fraud transparently. You will encounter this event-driven architecture again next semester in your Sem 3 BCA303 SQLite mobile development course. Triggers will allow your smartphone applications to instantly run background tasks and synchronize offline local data records the moment a user performs an update.
Summary
Key takeaways
- Triggers are specialized named PL/SQL blocks executed automatically by database events.
- Unlike stored procedures, triggers cannot be manually invoked or called by external applications.
- BEFORE triggers allow verification and modification of incoming records before disk commit.
- AFTER triggers are optimal for logging audit data to auxiliary tracking tables.
- The FOR EACH ROW modifier converts a statement trigger into a row-level handler.
- Row-level triggers gain exclusive access to :OLD and :NEW data qualifiers.
- Memory hook: Procedures are run by choice, but triggers fire from actions!