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();
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot