Skip to content

Instantly share code, notes, and snippets.

@johnhonan

johnhonan/autonomous_audit.sql

Created November 30, 2017 19:33
Show Gist options
  • Save johnhonan/f361f70c3978a4b73bf9a3d17e9b67ee to your computer and use it in GitHub Desktop.
Save johnhonan/f361f70c3978a4b73bf9a3d17e9b67ee to your computer and use it in GitHub Desktop.
Download ZIP
Oracle example of autonomous transaction for logging audit messages
Raw
autonomous_audit.sql
-- 1) Create a table in the hr database called 'log'
-- with two fields; log_msg (text) and time_of_msg (datetime)
-- 2) Modify the procedure below to insert a record into
-- the log table;
CREATE OR REPLACE PROCEDURE hr.log_details (msg IN VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION; -- run a commit here regardless of any other transaction state
BEGIN
INSERT INTO hr.log(msg_id,log_msg,time_of_msg)
VALUES(log_seq.NEXTVAL,msg,SYSDATE);
COMMIT; -- must commit or rollback in an autonomous_transaction
END;
-- 3) Run the following statement and see what the 'employees' table
-- and the 'log' table look like afterwards.
BEGIN
DELETE hr.employees;
log_msg('Deleting all employees');
ROLLBACK;
log_msg('after rollback of delete employees');
END;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment