U0100016: GaussDB does not support autonomous transactions in triggers

Description

GaussDB does not support autonomous transactions in triggers.

Database Type and Version

• Source database type and version: Oracle versions supported by UGO
• Target database type and version: GaussDB versions supported by UGO

Syntax Example

Oracle script:

CREATE OR REPLACE TRIGGER log_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
DECLARE PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
      INSERT INTO trigger_log(log_id,employee_id, action_type) VALUES (:NEW.id,100, 'INSERT');
    COMMIT;
END;
/

Suggestion

1. To create a procedure with autonomous transactions, extract INSERT, UPDATE, and DELETE operations, commit or rollback transactions, and add IN parameters new and old.

If there are multiple keywords (INSERT, UPDATE, DELETE, and COMMIT/ROLLBACK), create a package containing multiple procedures with autonomous transactions.

2. Create a trigger function to call the procedure with autonomous transactions. Configure new and old.

3. Create a trigger to execute the trigger function.

Script compatible with GaussDB:

--1. Create a procedure with autonomous transactions.
create or replace procedure log_employee_insert( v_id NUMBER)
as   
 DECLARE  PRAGMA AUTONOMOUS_TRANSACTION;
begin
      INSERT INTO trigger_log(log_id,employee_id, action_type) VALUES (v_id,100, 'INSERT');
     COMMIT;
end;
--2. Create a trigger function to call the procedure.
CREATE OR REPLACE FUNCTION dsc_log_employee_insert_employees_xp()
RETURN TRIGGER AS
BEGIN
      PERFORM log_employee_insert(new.id);
   IF ((TG_OP = 'INSERT') OR (TG_OP = 'UPDATE')) THEN
      RETURN NEW;
   ELSE
      RETURN OLD;
   END IF; 
END;
--3. Create a trigger.
DROP TRIGGER if exists log_employee_insert on employees;
CREATE TRIGGER log_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
EXECUTE PROCEDURE dsc_log_employee_insert_employees_xp();