oracle增量脚本（记录）创建触发器监控对一张表的增删改

删除表
drop table test_1;
drop table test_1_log;
drop table A1;
drop table B1;
drop table C1;

–创建测试表
CREATE TABLE test_1(
t_id NUMBER(4),
t_name VARCHAR2(20),
t_age NUMBER(2),
t_sex CHAR
);

–创建记录测试表
CREATE TABLE test_1_log(
l_user VARCHAR2(15),
l_type VARCHAR2(15),
l_date VARCHAR2(30)
);

–创建记录新产生数据记录表a1
create table a1 (
T_ID NUMBER(4),
TYPE CHAR,
TIME VARCHAR2(30)
);

–创建记录新产生数据记录表b1
create table b1 (
T_ID NUMBER(4),
TYPE CHAR,
TIME VARCHAR2(30)
);

–创建记录新产生数据记录表c1
create table c1 (
T_ID NUMBER(4),
TYPE CHAR,
TIME VARCHAR2(30)
);

创建触发器
CREATE OR REPLACE TRIGGER test_1_TRIGGER
AFTER DELETE OR INSERT OR UPDATE ON test_1
FOR EACH ROW
DECLARE
V_TYPE test_1_LOG.L_TYPE%TYPE;
BEGIN

IF INSERTING THEN
–INSERT触发
V_TYPE := ‘I’;
INSERT INTO A1 VALUES (:NEW.T_ID,‘I’,TO_CHAR(SYSDATE, ‘yyyy-mm-dd hh24:mi:ss’));
DBMS_OUTPUT.PUT_LINE(‘记录已经成功插入，并已记录到日志’);
ELSIF UPDATING THEN
–UPDATE触发
V_TYPE := ‘U’;
INSERT INTO B1 VALUES (:NEW.T_ID,‘U’,TO_CHAR(SYSDATE, ‘yyyy-mm-dd hh24:mi:ss’));
DBMS_OUTPUT.PUT_LINE(‘记录已经成功更新，并已记录到日志’);
ELSIF DELETING THEN
–DELETE触发
V_TYPE := ‘D’;
INSERT INTO C1 VALUES (:OLD.T_ID,‘D’,TO_CHAR(SYSDATE, ‘yyyy-mm-dd hh24:mi:ss’));
DBMS_OUTPUT.PUT_LINE(‘记录已经成功删除，并已记录到日志’);
END IF;

INSERT INTO test_1_LOG VALUES (USER, V_TYPE, TO_CHAR(SYSDATE, ‘yyyy-mm-dd hh24:mi:ss’));

–USER表示当前用户名
END;
/

–测试
INSERT INTO test_1 VALUES(10,‘zhao’,22,‘M’);
INSERT INTO test_1 VALUES(11,‘zhao’,22,‘M’);
INSERT INTO test_1 VALUES(12,‘zhao’,22,‘M’);
UPDATE test_1 SET t_age = 30 WHERE t_id = 10;
DELETE test_1 WHERE t_id = 10;

–然后查看效果
SELECT * FROM test_1;
SELECT * FROM test_1_log;
SELECT * FROM A1;
SELECT * FROM b1;
SELECT * FROM c1;

清空数据
truncate table test_1;
truncate table test_1_log;
truncate table a1;