This topic describes the BEFORE row-level triggers in the PolarDB-O partition tables, and their compatibility settings.

When you update data across partitions, PostgreSQL and Oracle use different trigger behaviors.

  • PostgreSQL: The triggers of multiple partition tables are fired.
  • Oracle: The relevant triggers are fired only once.

By default, PolarDB-O is compatible with the trigger behavior of Oracle.

Note
  • AFTER row-level triggers are not compatible with the trigger behavior of Oracle.
  • Statement-level triggers are used in the same way as standard tables.

Parameter settings

You can control the compatibility of BEFORE row-level triggers by using polar_compatible_oracle_trigger.

  • polar_compatible_oracle_trigger = off

    This setting indicates that PolarDB-O uses the trigger behavior of PostgreSQL. If data is updated across partitions, the triggers of multiple tables are fired.

    To set polar_compatible_oracle_trigger to off, execute the following statement:

    SET polar_compatible_oracle_trigger = off;

    The following code block provides an example on how to use this setting:

    create trigger t before insert or update or delete on parted
      for each row execute function trigger_function();
    insert into parted values (1, 1, 'uno uno v1');
    NOTICE:  parted_1: BEFORE ROW INSERT
    update parted set a = 2;
    NOTICE:  parted_1: BEFORE ROW UPDATE
    NOTICE:  parted_1: BEFORE ROW DELETE
    NOTICE:  parted_2: BEFORE ROW INSERT
    delete from parted;
    NOTICE:  parted_2: BEFORE ROW DELETE
  • polar_compatible_oracle_trigger = on

    This setting indicates that PolarDB-O uses the trigger behavior of Oracle. If data is updated across partitions, the relevant triggers are fired only once.

    To set polar_compatible_oracle_trigger to on, execute the following statement:

    SET polar_compatible_oracle_trigger = on;

    The following code block provides an example on how to use this setting:

    insert into parted values (1, 1, 'uno uno v1');
    NOTICE:  parted_1: BEFORE ROW INSERT
    update parted set a = 2;
    NOTICE:  parted_1: BEFORE ROW UPDATE
    delete from parted;
    NOTICE:  parted_2: BEFORE ROW DELETE