HomeMogDBMogDB StackUqbar
v5.0

Documentation:v5.0

Supported Versions:

Other Versions:

Function

CREATE EVENT TRIGGER creates an event trigger to execute a specified event trigger function when a specified event occurs.

Precautions

  • Only the super user or system administrator has the permission to create event triggers.
  • If multiple event triggers of the same kind are defined for the same event, they will be fired in alphabetical order by name.
  • Event triggers may affect the performance of DDL operations, depending on the number of event triggers and the complexity of executing the function.

Syntax

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

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.

Examples

--Create an event trigger function (for ddl_command_start and ddl_command_end events).
MogDB=# 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).
MogDB=# 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).
MogDB=# 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.
MogDB=# 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.
MogDB=# 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.
MogDB=# CREATE EVENT TRIGGER sql_drop_command ON sql_drop
    EXECUTE PROCEDURE drop_sql_command();

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

--Modify an event trigger.
MogDB=# create role regress_evt_user WITH ENCRYPTED PASSWORD 'EvtUser123';
MogDB=# 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.
MogDB=# ALTER EVENT TRIGGER regress_event_trigger_start owner to regress_evt_user;
MogDB=# ALTER EVENT TRIGGER regress_event_trigger_start disable;
MogDB=# ALTER EVENT TRIGGER regress_event_trigger_start enable always;

--Delete an event trigger.
MogDB=# DROP EVENT TRIGGER regress_event_trigger_start;
MogDB=# DROP EVENT TRIGGER regress_event_trigger_end;
MogDB=# DROP EVENT TRIGGER sql_drop_command;
MogDB=# DROP EVENT TRIGGER no_rewrite_allowed;

ALTER EVENT TRIGGER, DROP EVENT TRIGGER

Copyright © 2011-2024 www.enmotech.com All rights reserved.