System triggers - PolarDB - Alibaba Cloud Documentation Center

System triggers are provided by PolarDB for PostgreSQL (Compatible with Oracle) to monitor database events. They are triggered when system events occur, facilitating complex audit logic, rule validation, or automated O&M operations. This topic describes the syntax, property functions, and behavioral characteristics of system triggers.

Supported versions

System triggers are only supported on clusters that run PolarDB for PostgreSQL (Compatible with Oracle) 2.0 with revision version 2.0.14.15.31.0 or later.

Note

You can view the minor engine version in the console or use the SHOW polardb_version; statement. If your minor engine version does not meet the requirements, upgrade the minor engine version.

Basic syntax

The following syntax defines a system trigger:

CREATE [OR REPLACE] TRIGGER [schema_name.]trigger_name
{ BEFORE | AFTER | INSTEAD OF }
{ CREATE | ALTER | DROP }
ON { [schema_name.]SCHEMA | DATABASE }
DECLARE
    -- Optional variable declarations
BEGIN
    -- Trigger logic
END;

Parameter	Description
`[schema_name.]trigger_name`	The system trigger name. PolarDB system triggers are database-level objects that do not belong to any schema. The `schema_name` here is only for syntax compatibility. Custom system trigger names cannot use `_polar_evt_tg_on_db` and `_polar_evt_tg_on_schema`, because these names are reserved by the system. If a system trigger has already been created, you cannot create a function or procedure named `_polar_evt_tg_trigger_name`.
{ BEFORE \| AFTER \| INSTEAD OF }	Defines when the system trigger is triggered. Valid values: BEFORE: triggered before the event. AFTER: triggered after the event. INSTEAD OF: replaces the original logic.
{ CREATE \| ALTER \| DROP }	The trigger event type. Only CREATE, ALTER, DROP, and DDL event types are supported, where DDL encompasses CREATE, ALTER, and DROP events. The following DDL commands do not trigger system triggers: DDL commands for global objects such as ROLE and USER. DDL commands such as ALTER SESSION, ALTER SYSTEM, and VACUUM. INSTEAD OF triggers only support CREATE events.
ON { [schema_name.]SCHEMA \| DATABASE }	The trigger scope. `ON DATABASE`: indicates that the trigger is valid for all events occurring in each schema within the current database. `ON [schema_name.]SCHEMA`: limits the trigger only for operations in a specific schema. `ON SCHEMA`: indicates that the trigger is valid for the current schema. The current schema refers to the first valid one in the current `search_path`.

The following example shows a trigger that prints logs when an object in the database is created or deleted:

CREATE OR REPLACE TRIGGER audit_ddl_trigger
AFTER CREATE OR DROP
ON DATABASE
BEGIN
    DBMS_OUTPUT.PUT_LINE('DDL Trigger fired: ' || ORA_SYSEVENT || ' on ' || ORA_DICT_OBJ_NAME || ' of type ' || ORA_DICT_OBJ_TYPE);
END;

Event property functions

PolarDB for PostgreSQL (Compatible with Oracle) system triggers provide a series of event property functions to obtain context information about the current trigger event, typically used for auditing and log recording.

ora_sysevent: The type of DDL statement, including CREATE, ALTER, and DROP.
ora_dict_obj_type: The object type operated on by the DDL statement, including TABLE, VIEW, INDEX, and other objects.
ora_dict_obj_name: The name of the object operated on by the DDL statement.
ora_dict_obj_owner: The owner of the object operated on by the DDL statement. Currently, only retrieving owners of object types such as TABLE, VIEW, and INDEX is supported.

The following example creates a system trigger that prints logs before DDL statements are executed:

CREATE OR REPLACE EDITIONABLE TRIGGER trigger_before_ddl before ddl on schema
BEGIN
    dbms_output.put_line('before ddl ora_sysevent: ' || ora_sysevent);
    dbms_output.put_line('before ddl ora_dict_obj_owner: ' || ora_dict_obj_OWNER);
    dbms_output.put_line('before ddl ora_dict_obj_type: ' || ora_dict_obj_type);
    dbms_output.put_line('before ddl ora_dict_obj_name: ' || ora_dict_obj_name);
END;

After executing the CREATE TABLE test_table (id int); statement, the trigger outputs the following:

before ddl ora_sysevent: CREATE
before ddl ora_dict_obj_owner: test_user
before ddl ora_dict_obj_type: TABLE
before ddl ora_dict_obj_name: test_table

Trigger management

Modify trigger definitions
Execute the CREATE OR REPLACE TRIGGER <trigger_name> ... statement to replace the definition of a previously created trigger with the same name, effectively modifying the trigger definition.
Enable/disable a trigger
Triggers created using the CREATE TRIGGER statement are enabled by default.
- Execute the following statement to disable a trigger: ALTER TRIGGER <trigger_name> DISABLE;
- Execute the following statement to enable a trigger: ALTER TRIGGER <trigger_name> ENABLE;
Delete a trigger
Execute the DROP EVENT TRIGGER <trigger_name>; statement to delete a trigger.