Can I commit a transaction inside a database trigger?
February 16th, 2006 By Eddie Awad
Short answer: No. However, using autonomous transactions you can start a “sub-transaction” and commit without affecting the main transaction. An example of when to use autonomous transactions is when you want to audit secured infromation such as payroll or financial data. For more information about autonomous transactions please read the documentation.
February 17th, 2006 at 2:55 am
Hi,
is use autonomous transactions to log errors/warnings/infos independent if the main transaction fails or not (see for WriteEventlog):
PROCEDURE Logapperror(p_Appcode IN Eventlog.Appcode%TYPE, p_Eventmessage IN Eventlog.Eventmessage%TYPE, p_Modul IN Eventlog.Modul%TYPE, p_Method IN Eventlog.Method%TYPE, p_Section IN Eventlog.Section%TYPE, p_Raise_Application_Error IN BOOLEAN, p_Objectid IN t_Objectid, p_Objectentity IN t_Objectentity) IS BEGIN Initentry(p_Eventmessage, p_Modul, p_Method, p_Severity => c_Severity_Error, p_Component => c_Component_App, p_Objectid => p_Objectid, p_Objectentity => p_Objectentity); Eventlog_Entry.Appcode := p_Appcode; Eventlog_Entry.Section := Substr(p_Section, 1, 64); Writeeventlog; IF (p_Raise_Application_Error) THEN Raise_Application_Error(-20001, p_Modul || '.' || p_Method || ' : ' || p_Eventmessage); END IF; END Logapperror;PROCEDURE Writeeventlog IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO Eventlog VALUES Eventlog_Entry; COMMIT; EXCEPTION WHEN OTHERS THEN -- Housten we have a problem ROLLBACK; RAISE; END Writeeventlog;i would not use this feature to modify application data via trigger and commit them. There must be a better way/design to avoid this.
Karl