How to Use Event Triggers in PostgreSQL

How do I monitor DDL changes without native event triggers in Redshift?

Event triggers fire a function automatically when specific DDL events (CREATE, ALTER, DROP) occur, enabling auditing, security checks, or automation.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Does Amazon Redshift support SQL event triggers?

Amazon Redshift is PostgreSQL-compatible, but it does not implement the CREATE EVENT TRIGGER command. You can, however, mimic event-trigger behavior with classic table triggers, system tables, and external services like Amazon EventBridge.

How can I audit DDL changes without native event triggers?

Create AFTER triggers on catalog tables (e.g., pg_table_def) inside a separate auditor schema.Combine the trigger with an SNS or EventBridge rule to push notifications when objects are added, altered, or dropped.

Example catalog-watch trigger

Ecommerce teams often want to know when the Orders table changes. The code below logs any DDL touching that table.

CREATE TABLE audit.ddl_log ( id bigint IDENTITY(1,1), ddl_time timestamp default SYSDATE, ddl_user varchar(100), object_name varchar(200), ddl_sql text );. CREATE OR REPLACE FUNCTION audit.log_ddl() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN INSERT INTO audit.ddl_log(ddl_user,object_name,ddl_sql) VALUES (current_user, TG_TABLE_NAME, pg_last_query()); RETURN NULL; END; $$; -- Attach AFTER trigger to system catalog table CREATE TRIGGER orders_ddl_watch AFTER INSERT OR UPDATE OR DELETE ON pg_table_def FOR EACH STATEMENT EXECUTE PROCEDURE audit.log_ddl();

What is the PostgreSQL syntax for event triggers?

If you migrate to PostgreSQL 15, the canonical pattern looks like:

CREATE EVENT TRIGGER ddl_audit ON ddl_command_end WHEN TAG IN ('CREATE TABLE','ALTER TABLE','DROP TABLE') EXECUTE PROCEDURE audit.log_ddl();

Best practices for Redshift DDL monitoring

Store audit tables in a dedicated schema, restrict GRANTs, and set WLM queues so audit code never blocks production queries.Use federated queries to pipe logs into S3 or CloudWatch for long-term retention.

Encourage automated reviews

Have CI/CD jobs read the audit table and flag unexpected DDL in pull requests. This keeps your ecommerce database stable and PCI-compliant.

Why How to Use Event Triggers in PostgreSQL is important

How to Use Event Triggers in PostgreSQL Example Usage


-- Alert when Products table structure changes
CREATE OR REPLACE FUNCTION audit.product_ddl_notify()
RETURNS trigger AS $$
BEGIN
  INSERT INTO audit.ddl_log(ddl_user,object_name,ddl_sql)
  VALUES (current_user, 'Products', pg_last_query());
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER products_ddl_watch
AFTER INSERT OR UPDATE OR DELETE ON pg_table_def
FOR EACH STATEMENT
EXECUTE PROCEDURE audit.product_ddl_notify();

How to Use Event Triggers in PostgreSQL Syntax


-- PostgreSQL event trigger syntax (not available in Redshift)
CREATE EVENT TRIGGER trigger_name
ON { ddl_command_start | ddl_command_end | sql_drop }
  [ WHEN tag IN ( 'CREATE TABLE', 'ALTER TABLE', ... ) ]
EXECUTE PROCEDURE function_name();

-- Emulating in Redshift with table triggers
CREATE OR REPLACE FUNCTION audit.log_ddl()
RETURNS trigger AS $$ ... $$ LANGUAGE plpgsql;

CREATE TRIGGER orders_ddl_watch
AFTER INSERT OR UPDATE OR DELETE ON pg_table_def
FOR EACH STATEMENT
EXECUTE PROCEDURE audit.log_ddl();

Common Mistakes

Relying on <code>CREATE EVENT TRIGGER</code> directly in Redshift. The command runs in PostgreSQL but fails in Redshift, causing confusing syntax errors. Fix: switch to catalog-based triggers or external event pipelines.
Attaching table triggers directly to user tables for DDL auditing. These fire on data changes, not DDL. Fix: target <code>pg_table_def</code> and other system tables instead, or move to PostgreSQL proper.