A Comprehensive Guide to SQL Triggers: DDL Triggers - PART II

In the previous blog, we learned about SQL triggers and discussed DML triggers in detail, covering all the combinations of BEFORE and AFTER triggers, row-level and statement-level triggers, and with examples for INSERT, UPDATE, and DELETE operations. If you haven't read it already, please check out PART 1 of SQL Triggers blog to get a solid understanding of DML triggers before diving into DDL triggers.

In this blog, we’ll explore DDL triggers, which respond to schema-related changes, and we’ll clarify how they differ from DML triggers. We can also see whether DDL triggers support concepts like "FOR EACH ROW" or "FOR EACH STATEMENT", and whether DDL triggers can be set to fire "BEFORE" or "AFTER" the action.

Understanding DDL Triggers

DDL triggers are special kinds of triggers that fire when Data Definition Language (DDL) commands, like CREATE, ALTER, and DROP, are executed. They are primarily used for auditing, logging, or controlling schema changes in a database.

Do We Have "FOR EACH ROW" or "FOR EACH STATEMENT" in DDL Triggers?

Unlike DML triggers, which can be row-level (executing for each row affected) or statement-level (executing once per SQL statement), DDL triggers are exclusively statement-level. This means that DDL triggers fire once for each DDL command, regardless of how many schema objects are affected by that command.

• For DDL triggers, "FOR EACH ROW" does not apply.

• DDL triggers are always statement-level, meaning they will execute only once per DDL statement.

  
  

Do We Have "BEFORE" and "AFTER" Timing in DDL Triggers?

DDL triggers differ from DML triggers in that they do not support "BEFORE" triggers. They only support "AFTER" triggers, which means that DDL triggers execute after the schema-altering command has been completed.

• "BEFORE" DDL triggers: Not supported.

• "AFTER" DDL triggers: Supported, meaning DDL triggers will always execute after the DDL event (e.g., CREATE, ALTER, DROP) has occurred.

1.DDL Trigger for "AFTER" ALTER TABLE

Let’s begin with an example of an AFTER trigger for an ALTER TABLE operation, which logs every time a table is altered.

Step 1: Create a Function

We first define a function that will log a message whenever the structure of a table is altered:

CREATE OR REPLACE FUNCTION log_alter_table() 
RETURNS EVENT_TRIGGER AS $$
DECLARE
table_name TEXT;
command TEXT := current_query();  -- Get the current SQL command
BEGIN
-- Extract the table name from the current query command
SELECT regexp_replace(command, '.*ALTER TABLE ([^ ]+).*', '\1') INTO table_name;
RAISE NOTICE 'The table % has been altered', table_name; 
END; $$ LANGUAGE plpgsql;

Step 2: Create the Trigger

Next, we create an event trigger that will execute after an ALTER TABLE statement:

CREATE EVENT TRIGGER after_alter_trigger 
ON ddl_command_end 
WHEN TAG IN ('ALTER TABLE') 
EXECUTE FUNCTION log_alter_table();

Step 3: Test the Trigger

We can test the trigger by altering an existing table:

ALTER TABLE film ADD COLUMN new_column VARCHAR(50);

Expected Output:

NOTICE: The table film has been altered

2. DDL Trigger for "AFTER" DROP TABLE

Here’s an example for logging when a table is dropped from the database.

Step 1: Create a Function

CREATE OR REPLACE FUNCTION log_drop_table() 
RETURNS EVENT_TRIGGER AS $$
DECLARE
table_name TEXT;
command TEXT := current_query();  -- Get the current SQL command
BEGIN 
-- Extract the table name from the current query command
SELECT regexp_replace(command, '.*DROP TABLE\s+([^ ;]+).*', '\1') INTO table_name;
RAISE NOTICE 'The table % has been dropped', table_name; 
END; $$ LANGUAGE plpgsql;

Step 2: Create the Trigger

CREATE EVENT TRIGGER after_drop_trigger 
ON ddl_command_end 
WHEN TAG IN ('DROP TABLE') 
EXECUTE FUNCTION log_drop_table();

Step 3: Test the Trigger

Perform a DROP TABLE operation for any existing table to trigger the event:

DROP TABLE film;

Expected Output:

NOTICE: Table film has been dropped

3.DDL Trigger for "AFTER" CREATE TABLE

You can create a trigger to log whenever a new table is created:

Step 1: Create the Function

CREATE OR REPLACE FUNCTION log_create_table() 
RETURNS EVENT_TRIGGER AS $$
DECLARE
table_name TEXT;
command TEXT := current_query();  -- Get the current SQL command
BEGIN 
-- Extract the table name from the current query command
SELECT regexp_replace(command, '.*CREATE TABLE\s+([^ (]+).*', '\1') INTO table_name;
RAISE NOTICE 'The table % has been created', table_name; 
END; $$ LANGUAGE plpgsql;

Step 2: Create the Trigger

CREATE EVENT TRIGGER after_create_trigger 
ON ddl_command_end 
WHEN TAG IN ('CREATE TABLE') 
EXECUTE FUNCTION log_create_table();

Step 3: Test the Trigger

Create a new table and observe the trigger:

CREATE TABLE test_table (id INT);

Expected Output:

NOTICE: Table test_table has been created

Enforcing Business Rules or Security Policies

DDL triggers can prevent unauthorized or accidental schema changes. For example, you can use a DDL trigger to block users from dropping or altering important tables.

• Example: Preventing the dropping of critical tables.

  
  

CREATE OR REPLACE FUNCTION prevent_critical_drop_table() 
RETURNS EVENT_TRIGGER AS $$ 
DECLARE
command TEXT := current_query();  -- Get the current SQL command
BEGIN 
-- Check if the command is a DROP TABLE statement
IF command ILIKE 'DROP TABLE to_check_create%' THEN 
RAISE EXCEPTION 'Dropping critical_table is not allowed!';
END IF; 
END; $$ LANGUAGE plpgsql;


CREATE EVENT TRIGGER prevent_drop_trigger
ON ddl_command_start
WHEN TAG IN ('DROP TABLE')
EXECUTE FUNCTION prevent_critical_drop_table();


DROP TABLE to_check_critical_drop;

Validating Schema Changes

You can validate schema modifications to ensure that they adhere to certain rules or standards. For example, you can enforce naming conventions on newly created tables or columns.

• Example: Ensuring that newly created table names follow a certain convention (e.g., they should start with "tbl_").

  
  

CREATE OR REPLACE FUNCTION validate_table_name()
RETURNS EVENT_TRIGGER AS $$
DECLARE
command TEXT := current_query();  -- Get the current SQL command
BEGIN
-- Check if the command is a CREATE TABLE statement
IF command ILIKE 'CREATE TABLE%' THEN
-- Extract the table name from the command
IF NOT (regexp_replace(command, '.*CREATE TABLE ([^ ]+).*', '\1') LIKE 'tbl_%') THEN
RAISE EXCEPTION 'Table names must start with "tbl_"!';
END IF;
END IF;
END; $$ LANGUAGE plpgsql;


CREATE EVENT TRIGGER validate_table_name_trigger
ON ddl_command_start
WHEN TAG IN ('CREATE TABLE')
EXECUTE FUNCTION validate_table_name();


CREATE TABLE my_table (
    id INT
);

While logging is a primary use case for DDL triggers, they are far more versatile. You can use them to enforce policies, validate schema changes, automate tasks, and much more. By utilizing DDL triggers, you can enhance your database’s security, maintain consistency, and automate complex processes that would otherwise require manual intervention.

Happy Triggers Day!