CREATE COMMAND TRIGGER 7 SQL - Language Statements CREATE COMMAND TRIGGER define a new trigger CREATE COMMAND TRIGGER CREATE TRIGGER name { BEFORE | AFTER } ANY COMMAND EXECUTE PROCEDURE function_name () CREATE TRIGGER name { BEFORE | AFTER } COMMAND command [, ... ] EXECUTE PROCEDURE function_name () where command can be one of: CREATE SCHEMA CREATE EXTENSION CREATE LANGUAGE CREATE FUNCTION CREATE TABLE CREATE SERVER CREATE FOREIGN TABLE CREATE FOREIGN DATA WRAPPER CREATE USER MAPPING CREATE INDEX CREATE SEQUENCE CREATE VIEW CREATE RULE CREATE AGGREGATE CREATE OPERATOR CREATE COLLATION CREATE TEXT SEARCH PARSER CREATE TEXT SEARCH DICTIONARY CREATE TEXT SEARCH TEMPLATE CREATE TEXT SEARCH CONFIGURATION CREATE TYPE_P CREATE DOMAIN_P CREATE TRIGGER CREATE CONVERSION_P CREATE CAST CREATE OPERATOR CLASS CREATE OPERATOR FAMILY ALTER SCHEMA ALTER EXTENSION ALTER FUNCTION ALTER TABLE ALTER SERVER ALTER FOREIGN TABLE ALTER FOREIGN DATA WRAPPER ALTER USER MAPPING ALTER AGGREGATE ALTER OPERATOR ALTER OPERATOR CLASS ALTER OPERATOR FAMILY ALTER COLLATION ALTER TEXT SEARCH PARSER ALTER TEXT SEARCH DICTIONARY ALTER TEXT SEARCH TEMPLATE ALTER TEXT SEARCH CONFIGURATION ALTER TYPE_P ALTER DOMAIN_P ALTER TRIGGER DROP TABLE DROP SEQUENCE DROP VIEW DROP INDEX DROP TYPE_P DROP DOMAIN_P DROP COLLATION DROP CONVERSION_P DROP SCHEMA DROP EXTENSION DROP TEXT SEARCH PARSER DROP TEXT SEARCH DICTIONARY DROP TEXT SEARCH TEMPLATE DROP TEXT SEARCH CONFIGURATION DROP LANGUAGE DROP SERVER DROP FOREIGN TABLE DROP FOREIGN DATA WRAPPER DROP USER MAPPING DROP TRIGGER DROP ASSERTION DROP OPERATOR CLASS DROP OPERATOR FAMILY DROP FUNCTION DROP AGGREGATE DROP OPERATOR DROP CAST DROP RULE REINDEX VACUUM CLUSTER LOAD CREATE COMMAND TRIGGER creates a new command trigger. The trigger will be associated with the specified command and will execute the specified function function_name when that command is run. The command trigger can be specified to fire before or after the command is executed. A command trigger's function must return void, the only it can aborts the execution of the command is by raising an exception. Refer to for more information about triggers. name The name to give the new trigger. This must be distinct from the name of any other trigger for the same table. The name cannot be schema-qualified — the trigger inherits the schema of its table. For a constraint trigger, this is also the name to use when modifying the trigger's behavior using SET CONSTRAINTS. BEFORE AFTER Determines whether the function is called before or after the command is executed. command The tag of the command the trigger is for. Supported commands are mainly those acting on database objects, plus some more facilities. That leaves out the following list of non supported commands. Commands that refers to global objects, such as databases, tablespaces and roles are not supported. As command triggers are per-database, it would be weird to affect e.g. a tablespace depending on which database you are connected to. Commands that exercize their own transaction control are only supported in BEFORE command triggers, that's the case for VACUUM, CLUSTER CREATE INDEX CONCURRENTLY, and REINDEX DATABASE. Commands that are related to transaction control (such as BEGIN or COMMIT), related to prepared plans (e.g. PREPARE, DEALLOCATE), cursors management (e.g. DECLARE, FETCH), setting variables (SET), the LISTEN feature, and security are not supported either. Command triggers on CREATE COMMAND TRIGGER, ALTER COMMAND TRIGGER and DROP COMMAND TRIGGER are not supported so as not to be able to take over control from a superuser. Triggers on ANY command support more commands than just this list, and will only provide the command tag argument as NOT NULL. Supporting more commands is made so that you can actually block commands in one go. function_name A user-supplied function that is declared as taking 5 arguments of type text, text, oid, text, text and returning void. If your command trigger is implemented in C then it will be called with yet another argument, of type internal, which is a pointer to the Node * parse tree. The command trigger function is called with the parameters tg_when (which is set to either 'BEFORE' or 'AFTER'), command tag, objectid (can be null in case of a BEFORE CREATE or an AFTER DROP command trigger timing), schemaname (can be null for objects not living in a schema, and for sequences due to an implementation limit) and object name (can be null for any command triggers). The command CREATE SEQUENCE lacks support for the schemaname command trigger argument, it provides NULL in all cases. To create a trigger on a command, the user must be superuser. Use to remove a command trigger. Forbids the execution of any DDL command: CREATE OR REPLACE FUNCTION abort_any_command (tg_when text, cmd_tag text, objectid oid, schemaname text, objectname text) RETURNS void LANGUAGE plpgsql AS $$ BEGIN RAISE EXCEPTION 'command % is disabled' % cmd_tag; END; $$; CREATE TRIGGER abort_ddl BEFORE COMMAND CREATE TABLE EXECUTE PROCEDURE abort_any_command(); Execute the function enforce_local_style each time a CREATE TABLE command is run: CREATE OR REPLACE FUNCTION enforce_local_style (tg_when text, cmd_tag text, objectid oid, schemaname text, objectname text) RETURNS bool LANGUAGE plpgsql AS $$ BEGIN IF substring(objectname, 0, 4) NOT IN ('ab_', 'cz_', 'fr_') THEN RAISE EXCEPTION 'invalid relation name: %', objectname; END IF; END; $$; CREATE TRIGGER check_style BEFORE COMMAND CREATE TABLE EXECUTE PROCEDURE enforce_local_style(); CREATE COMMAND TRIGGER is a PostgreSQL extension of the SQL standard.