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