Oracle uses stored procedures for logging. sql

-This SQL statement is used to implement the Oracle background record operation log, instead of or supplementing the application's operational logs.

--1. Corresponding Log Record table
-------------------------------------------------------------------
CREATE TABLE TEST
(
T_ID Number (4),
T_name VARCHAR2 (20),
T_age Number (2),
T_sex CHAR (1)
);
----------------------the corresponding Log record table---------------------------END

--2. Setting up triggers (trigger implementation logging)

--2.1 turn on the screen output: (need to be executed after the command line login Sqlplus)
Sql>set Serveroutput on;--can capture these output information in the Doc window and under Tomcat.
------------------------------------------------------------------

CREATE OR REPLACE TRIGGER test_trigger 
after DELETE or INSERT or UPDATE on test for each row--the For each row is the Used: New and old
DECLARE
V_type VARCHAR2 ();
BEGIN
IF INSERTING then 
  V_type: = ' INSERT ' | |:new. T_name;--oracle adding a link string using | |
  Dbms_output. Put_Line (' record ' | |:new. t_name| | ' has been successfully inserted and logged to the log ');--Add only new
elsif UPDATING then 
  V_type: = ' UPDATE ' | |:o Ld. T_name;
  Dbms_output. Put_Line (' record ' | |:o Ld. t_name| | ' has been successfully updated and logged to the log ')--updated with new and old
Elsif DELETING then
  v_type: = ' DELETE ' | |:o Ld. T_name;
  Dbms_output. Put_Line (' record ' | |:o Ld. t_name| | ' has been successfully deleted and logged to the log ');--delete only old
END if;
  My_pro (V_type);
END;
----------------------CREATE trigger------------------------------------END

--3. Create a log table.
----------------------------------------------------------------
CREATE TABLE Test_log_1
(
L_user VARCHAR2 (15),
L_type VARCHAR2 (15),
L_date VARCHAR2 (30)
);
--------------------Create a log table---------------------------------END

--4. Build stored Procedures (Note that you must add pragma autonomous_transaction; Let this stored procedure have autonomous transaction control, otherwise it will affect the transaction control of a)
----------------------------------------------------------------
Create or Replace procedure My_pro (V_type varchar2)
As
PRAGMA autonomous_transaction;--is set to an autonomous transaction and must be accompanied by a commit, otherwise the commit is illegal.
Begin
INSERT into Test_log_1 VALUES (User,v_type,to_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss '));
Commit
End
-------------------Build Stored Procedure-------------------------------------END

--Finally, we enter the following test statement in a:
INSERT into Test VALUES (1, ' Zhao ', +, ' M ');
UPDATE Test SET t_name = ' WHERE t_id = 1;
DELETE Test WHERE t_id = 1;

SELECT * from Test;
SELECT * from test_log_1;

Oracle uses stored procedures for logging. sql