create_command_trigger.sgml

<!--
doc/src/sgml/ref/create_trigger.sgml
PostgreSQL documentation
-->

<refentry id="SQL-CREATECOMMANDTRIGGER">
 <refmeta>
  <refentrytitle>CREATE COMMAND TRIGGER</refentrytitle>
  <manvolnum>7</manvolnum>
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>

 <refnamediv>
  <refname>CREATE COMMAND TRIGGER</refname>
  <refpurpose>define a new trigger</refpurpose>
 </refnamediv>

 <indexterm zone="sql-createcommandtrigger">
  <primary>CREATE COMMAND TRIGGER</primary>
 </indexterm>

 <refsynopsisdiv>
<synopsis>
CREATE TRIGGER <replaceable class="PARAMETER">name</replaceable> { BEFORE | AFTER  } ANY COMMAND
    EXECUTE PROCEDURE <replaceable class="PARAMETER">function_name</replaceable> ()

CREATE TRIGGER <replaceable class="PARAMETER">name</replaceable> { BEFORE | AFTER  } COMMAND <replaceable class="PARAMETER">command</replaceable> [, ... ]
    EXECUTE PROCEDURE <replaceable class="PARAMETER">function_name</replaceable> ()

<phrase>where <replaceable class="parameter">command</replaceable> can be one of:</phrase>

    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
	
</synopsis>
 </refsynopsisdiv>

 <refsect1>
  <title>Description</title>

  <para>
   <command>CREATE COMMAND TRIGGER</command> creates a new command trigger.
   The trigger will be associated with the specified command and will
   execute the specified
   function <replaceable class="parameter">function_name</replaceable> when
   that command is run.
  </para>

  <para>
   The command trigger can be specified to fire before or after the command
   is executed. A command trigger's function must
   return <literal>void</literal>, the only it can aborts the execution of
   the command is by raising an exception.
  </para>

  <para>
   Refer to <xref linkend="triggers"> for more information about triggers.
  </para>
 </refsect1>

 <refsect1>
  <title>Parameters</title>

  <variablelist>
   <varlistentry>
    <term><replaceable class="parameter">name</replaceable></term>
    <listitem>
     <para>
      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 &mdash; 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
      <command>SET CONSTRAINTS</>.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>BEFORE</literal></term>
    <term><literal>AFTER</literal></term>
    <listitem>
     <para>
      Determines whether the function is called before or after the command
      is executed.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">command</replaceable></term>
    <listitem>
     <para>
      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.
     </para>
     <para>
      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.
     </para>
     <para>
      Commands that exercize their own transaction control are only
      supported in <literal>BEFORE</literal> command triggers, that's the
      case for <literal>VACUUM</literal>, <literal>CLUSTER</literal>
      <literal>CREATE INDEX CONCURRENTLY</literal>, and <literal>REINDEX
      DATABASE</literal>.
     </para>
     <para>
      Commands that are related to transaction control (such
      as <literal>BEGIN</literal> or <literal>COMMIT</literal>), related to
      prepared plans
      (e.g. <literal>PREPARE</literal>, <literal>DEALLOCATE</literal>),
      cursors management
      (e.g. <literal>DECLARE</literal>, <literal>FETCH</literal>), setting
      variables (<literal>SET</literal>), the <literal>LISTEN</literal>
      feature, and security are not supported either.
     </para>
     <para>
      Command triggers on <literal>CREATE COMMAND
      TRIGGER</literal>, <literal>ALTER COMMAND TRIGGER</literal>
      and <literal>DROP COMMAND TRIGGER</literal> are not supported so as
      not to be able to take over control from a superuser.
     </para>
     <para>
      Triggers on <literal>ANY</literal> command support more commands than
      just this list, and will only provide the <literal>command
      tag</literal> argument as <literal>NOT NULL</literal>. Supporting more
      commands is made so that you can actually block <xref linkend="ddl">
      commands in one go.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">function_name</replaceable></term>
    <listitem>
     <para>
      A user-supplied function that is declared as taking 5 arguments of
      type text, text, oid, text, text and returning void.
     </para>
     <para>
      If your command trigger is implemented in <literal>C</literal> then it
      will be called with yet another argument, of
      type <literal>internal</literal>, which is a pointer to
      the <literal>Node *</literal> parse tree.
     </para>
     <para>
      The command trigger function is called with the
      parameters <literal>tg_when</literal> (which is set to either 'BEFORE'
      or 'AFTER'), <literal>command
      tag</literal>, <literal>objectid</literal> (can be null in case of a
      BEFORE CREATE or an AFTER DROP command trigger
      timing), <literal>schemaname</literal> (can be null for objects not
      living in a schema, and for sequences due to an implementation limit)
      and <literal>object name</literal> (can be null for any command
      triggers).
     </para>
     <para>
      The command <literal>CREATE SEQUENCE</literal> lacks support for
      the <literal>schemaname</literal> command trigger argument, it
      provides <literal>NULL</literal> in all cases.
     </para>
    </listitem>
   </varlistentry>

  </variablelist>
 </refsect1>

 <refsect1 id="SQL-CREATECOMMANDTRIGGER-notes">
  <title>Notes</title>

  <para>
   To create a trigger on a command, the user must be superuser.
  </para>

  <para>
   Use <xref linkend="sql-dropcommandtrigger"> to remove a command trigger.
  </para>
 </refsect1>

 <refsect1 id="SQL-CREATECOMMANDTRIGGER-examples">
  <title>Examples</title>

  <para>
   Forbids the execution of any DDL command:
  </para>

<programlisting>
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();
</programlisting>

  <para>
   Execute the function <function>enforce_local_style</> each time
   a <literal>CREATE TABLE</literal> command is run:
  </para>

<programlisting>
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();
</programlisting>
 </refsect1>

 <refsect1 id="SQL-CREATECOMMANDTRIGGER-compatibility">
  <title>Compatibility</title>

  <para>
   <command>CREATE COMMAND TRIGGER</command> is a
   <productname>PostgreSQL</productname> extension of the <acronym>SQL</>
   standard.
  </para>

 </refsect1>

 <refsect1>
  <title>See Also</title>

  <simplelist type="inline">
   <member><xref linkend="sql-createfunction"></member>
   <member><xref linkend="sql-altercommandtrigger"></member>
   <member><xref linkend="sql-dropcommandtrigger"></member>
  </simplelist>
 </refsect1>
</refentry>