Triggers in SQL

The Automated Event Engine: Triggers A Trigger is a specialized, named database object that compiles procedural code designed to execute automatically whenever a specific modification event occurs on a table. Unlike stored procedures, which must be called manually by an application backend using a CALL statement, triggers are event-driven. They watch for Data Manipulation Language (DML) actions ( INSERT , UPDATE , DELETE ) and run their code automatically behind the scenes, ensuring strict business rules are enforced regardless of how a database write is initiated. 1. Trigger Execution Timelines: BEFORE vs. AFTER The database engine allows you to control exactly when your automated code runs relative to the data modification event. THE DML TRIGGER LIFECYCLE WINDOW ┌──────────────────────────────┼──────────────────────────────┐ ▼ ▼ ▼ BEFORE Trigger DML Engine Execution AFTER Trigger • Runs *prior* to data write. • Validates constraints. • Runs *post* successful write. • Can mutate incoming values. • Writes row to table storage. • Read-only snapshot access. • Can cancel the operation. • Updates active indexes. • Ideal for audit trails & logs. A. BEFORE Triggers (Pre-Processing Layer) A BEFORE trigger executes prior to the database engine validating table constraints, allocating memory, or writing the row data to the disk block. Primary Use Case: Sanitizing incoming data, enforcing complex validation checks, or dynamically overriding cell values before they are permanently written to disk. Special Capability: A BEFORE trigger can modify incoming values on the fly, or cancel a write operation entirely by throwing a custom exception. B. AFTER Triggers (Post-Processing Layer) An AFTER trigger executes following the successful completion of the DML action. At this point, the row has already passed all structural constraint checks and has been safely written to table storage. Primary Use Case: Writing historical audit trail logs, maintaining separate analytics summary tables, or broadcasting events to external caching systems. Special Limitation: An AFTER trigger can access the newly saved row data, but it is strictly read-only; it cannot modify the row that triggered it. 2. Target Mutation Events: INSERT , UPDATE , and DELETE Triggers can listen to specific modification events, and they use special context variables to let you inspect the row data exactly as it changes: NEW : A pseudo-row variable holding the incoming data being written to the database (available in INSERT and UPDATE events). OLD : A pseudo-row variable holding the existing data as it sits on disk before the change (available in UPDATE and DELETE events). DML Target Event Available Context Variables Primary Operational Focus INSERT NEW variable only (since no prior data existed). Assigning default calculated fields or validating incoming payloads. UPDATE Both OLD and NEW variables are accessible. Comparing exactly which data fields shifted to detect state modifications. DELETE OLD variable only (since the row is being removed). Archiving purged records straight into cold-storage history logs. 3. Production Implementation Blueprint Writing a trigger is a two-step process in engines like PostgreSQL: Define a Trigger Function that contains the procedural code block to execute. Build the Trigger Binding that attaches that function to a specific target table and event configuration. A. Pre-Processing Blueprint ( BEFORE UPDATE ) This pattern automatically monitors an incoming data modification, standardizes its strings, and updates a tracking timestamp field before the data hits the disk storage array. SQL -- Step 1: Compile the reusable trigger function logic CREATE OR REPLACE FUNCTION clean_and_timestamp_node() RETURNS TRIGGER LANGUAGE plpgsql AS $ BEGIN -- Force incoming text configurations to always store as uppercase strings NEW.node_status := UPPER(TRIM(NEW.node_status)); -- Automatically refresh our operational modification timestamp NEW.updated_at := CURRENT_TIMESTAMP; -- In BEFORE triggers, returning 'NEW' allows the modified row to pass through to disk RETURN NEW; END; $; -- Step 2: Bind the trigger directly to our production table event CREATE TRIGGER trg_pre_update_node_cleanup BEFORE UPDATE ON cluster_nodes FOR EACH ROW -- Executes separately for every single row modified in a bulk update EXECUTE FUNCTION clean_and_timestamp_node(); B. Audit Logging Blueprint ( AFTER DELETE ) This pattern automatically captures a hard deletion event and archives a copy of the destroyed row data straight into a separate security log table for compliance tracking. SQL -- Step 1: Create our tracking compliance function container CREATE OR REPLACE FUNCTION log_deleted_node_history() RETURNS TRIGGER LANGUAGE plpgsql AS $ BEGIN -- Capture our destroyed data using the OLD context variable record mapping INSERT INTO system_audit_logs (deleted_node_id, snapshot_node_name, dropped_by_user) VALUES (OLD.node_id, OLD.node_name, CURRENT_USER); -- In AFTER triggers, the return state does not affect disk data; returning 'OLD' is standard RETURN OLD; END; $; -- Step 2: Establish the automated AFTER binding CREATE TRIGGER trg_post_delete_node_audit AFTER DELETE ON cluster_nodes FOR EACH ROW EXECUTE FUNCTION log_deleted_node_history(); 4. Granular Execution Controls: Row-Level vs. Statement-Level When defining a trigger binding, you must choose how many times its code block should run during a bulk database operation using the FOR EACH modifier: Row-Level Triggers ( FOR EACH ROW ): The trigger code executes repeatedly— once for every single row affected by the SQL statement. If an application fires an update query that modifies 5,000 rows at once, a row-level trigger will run exactly 5,000 times. This is necessary when you need to inspect or mutate individual row values using the NEW or OLD keywords. Statement-Level Triggers ( FOR EACH STATEMENT - Default): The trigger code executes exactly once per SQL statement , regardless of whether that statement modifies 1 row, 10,000 rows, or 0 rows. This is highly efficient for general tasks, like notifying an external application that a batch data import script has finished. Database Trigger Reference Matrix Trigger Lifecycle State Supported DML Targets Key Context Variables Critical Production Guardrail BEFORE INSERT , UPDATE , DELETE NEW (Inserts/Updates) OLD (Updates/Deletes) Never run slow network API calls or complex subqueries inside a BEFORE trigger. Because these triggers run before a database write completes, any performance delay inside the trigger will instantly block active database connections, slowing down your entire application. AFTER INSERT , UPDATE , DELETE NEW (Inserts/Updates) OLD (Updates/Deletes) Avoid creating cascading trigger loops. If an AFTER UPDATE trigger on Table A automatically runs an update query on Table B, and Table B has a trigger that updates Table A, you can easily lock up your database engine in an infinite, crashing loop.