Background information

PolarDB common triggers do not support DDL statements, such as DROP, CREATE, and ALTER statements. You must change the statement to an event trigger in PolarDB.

New solution

The following table describes the syntax of PolarDB event triggers.
CREATE EVENT TRIGGER name
    ON event
    [ WHEN filter_variable IN (filter_value [, ... ]) [ AND ... ] ]
    EXECUTE { FUNCTION | PROCEDURE } function_name()
Note
  • Supported events include ddl_command_start, ddl_command_end, table_rewrite, and sql_drop.
  • ddl_command_start event occurs right before the execution of a CREATE, ALTER, DROP, SECURITY LABEL, COMMENT, GRANT, or REVOKE command. The system does not check whether the affected objects exist before the event trigger is raised.
  • ddl_command_end event occurs right after the execution of the same set of commands.
  • The sql_drop event occurs before the event trigger is ddl_command_end for any drop of database objects.
  • The table_rewrite event occurs before the TABLE is overridden by certain actions of the command ALTER TABLE and ALTER TYPE.

Examples

In Oracle:
create or replace trigger apps_no_ddl
before create or alter or drop or truncate
on database
begin
raise_application_error(-20001, 'The object of the app user is not allowed to operate by using DDL ');
end;
In PolarDB:
CREATE OR REPLACE FUNCTION abort_any_command()
  RETURNS event_trigger
 LANGUAGE plpgsql
  AS $$
BEGIN
  RAISE EXCEPTION 'command % is disabled', tg_tag;
END;
$$;

CREATE EVENT TRIGGER apps_no_ddl ON ddl_command_start
   EXECUTE FUNCTION abort_any_command();