Event Trigger

A trigger automatically executes functions when the specified DDL event occurs. Currently, event triggers are available only in PG-compatible mode.

Syntax

• Create an event trigger.

  CREATE EVENT TRIGGER name
      ON event
      [ WHEN filter_variable IN (filter_value [, ... ]) [ AND ... ] ]
      EXECUTE PROCEDURE function_name()
  

• Modify an event trigger.

  ALTER EVENT TRIGGER name DISABLE
  ALTER EVENT TRIGGER name ENABLE [ REPLICA | ALWAYS ]
  ALTER EVENT TRIGGER name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
  ALTER EVENT TRIGGER name RENAME TO new_name
  

• Delete an event trigger.

  DROP EVENT TRIGGER [ IF EXISTS ] name [ CASCADE | RESTRICT ];
  

Parameter Description

• name

  Specifies the event trigger name.

• filter_variable

  Specifies the variable used by the event trigger for filtering. Currently, only TAG is supported.

• event

  Specifies the events supported by the event trigger. Currently, ddl_command_start, ddl_command_end, sql_drop and table_rewrite are supported.

• function_name

  Specifies a user-defined function, which must be declared as taking no parameters and returning data of event_trigger type. This function is executed when an event trigger fires.

• new_name

  Specifies the new event trigger name.

• disable

  Disables the event trigger.

• ENABLE [ REPLICA | ALWAYS ]

  This event trigger is available when session_replication_role is set to REPLICA or any value.

Examples

--Create an event trigger function (for ddl_command_start and ddl_command_end events).
openGauss=# create function test_event_trigger() returns event_trigger as $$
BEGIN
    RAISE NOTICE 'test_event_trigger: % %', tg_event, tg_tag;
END
$$ language plpgsql;

--Create an event trigger function (for the sql_drop event).
openGauss=# CREATE OR REPLACE FUNCTION drop_sql_command()
RETURNS event_trigger AS $$
BEGIN
RAISE NOTICE '% - sql_drop', tg_tag;
END;
$$ LANGUAGE plpgsql;

--Create an event trigger function (for the table_rewrite event).
openGauss=# CREATE OR REPLACE FUNCTION test_evtrig_no_rewrite() RETURNS event_trigger
LANGUAGE plpgsql AS $$
BEGIN
  RAISE EXCEPTION 'rewrites not allowed';
END;
$$;

--Create an event trigger whose event type is ddl_command_start.
openGauss=# create event trigger regress_event_trigger on ddl_command_start
   execute procedure test_event_trigger();

--Create an event trigger whose event type is ddl_command_end.
openGauss=# create event trigger regress_event_trigger_end on ddl_command_end
   execute procedure test_event_trigger();

--Create an event trigger whose event type is sql_drop.
openGauss=# CREATE EVENT TRIGGER sql_drop_command ON sql_drop
    EXECUTE PROCEDURE drop_sql_command();

--Create an event trigger whose event type is table_rewrite.
openGauss=# create event trigger no_rewrite_allowed on table_rewrite
  when tag in ('alter table') execute procedure test_evtrig_no_rewrite();

--Run the DDL statement to check the event trigger effect (ddl_command_start and ddl_command_end are triggered).
openGauss=# create table event_trigger_table (a int);

--Run the alter table statement to check the event trigger effect. ddl_command_start and table_rewrite,ddl_command_end are not triggered because rewrite is disabled.
openGauss=# alter table event_trigger_table alter column a type numeric;

--Run the drop statement to check the event trigger effect (ddl_command_start, sql_drop, and ddl_command_end are triggered).
openGauss=# drop table event_trigger_table;

--Modify an event trigger.
openGauss=# create role regress_evt_user WITH ENCRYPTED PASSWORD 'EvtUser123';
openGauss=# ALTER EVENT TRIGGER regress_event_trigger RENAME TO regress_event_trigger_start;
--This operation should fail. The owner of the event trigger can only be the super user.
openGauss=# ALTER EVENT TRIGGER regress_event_trigger_start owner to regress_evt_user;
openGauss=# ALTER EVENT TRIGGER regress_event_trigger_start disable;
openGauss=# ALTER EVENT TRIGGER regress_event_trigger_start enable always;

--Delete a trigger.
openGauss=# DROP EVENT TRIGGER regress_event_trigger_start;
openGauss=# DROP EVENT TRIGGER regress_event_trigger_end;
openGauss=# DROP EVENT TRIGGER sql_drop_command;
openGauss=# DROP EVENT TRIGGER no_rewrite_allowed;