今天测试关于触发器问题,把更改的数据保存到另一个表。这个比较简单。
---------------------
CREATE TABLE EMP(
empno NUMBER(10) PRIMARY KEY,
ename VARCHAR2(10) NOT NULL,
job VARCHAR2(10),
sal NUMBER(10)
);
---------------------------
CREATE TABLE logs(
LOG_ID NUMBER(10) PRIMARY KEY,
LOG_TABLE VARCHAR2(10) NOT NULL,
LOG_DML VARCHAR2(10),
LOG_KEY_ID NUMBER(10),
LOG_DATE DATE,
LOG_USER VARCHAR2(15)
);
-----------------------
CREATE SEQUENCE logs_id_squ INCREMENT BY 1
START WITH 1 MAXVALUE 9999999 NOCYCLE NOCACHE;
----------------------
CREATE OR REPLACE TRIGGER DML_LOG
BEFORE --触发时间为操作前
DELETE OR INSERT OR UPDATE -- 由三种事件触发
ON emp
FOR EACH ROW -- 行级触发器
DECLARE tNAME_TEMP VARCHAR2(10) ;
BEGIN
select ename into tNAME_TEMP from emp where empno = :new.empno;
if(!tNAME_TEMP.equls(xxxx)) then
IF INSERTING THEN
INSERT INTO logs VALUES(logs_id_squ.NEXTVAL,'EMP','INSERT',:new.empno,SYSDATE,USER);
ELSIF DELETING THEN
INSERT INTO logs VALUES(logs_id_squ.NEXTVAL,'EMP','DELETE',:old.empno,SYSDATE,USER);
ELSE
INSERT INTO logs VALUES(logs_id_squ.NEXTVAL,'EMP','UPDATE',:new.empno,SYSDATE,USER);
END IF;
END;
可以操作。
后来,有位哥们提出一个问题。就是在同一个表里面备份原来数据
写的测试如下。我知道这样会有问题,只是想试着找条出路出路
CREATE TABLE EMP (
ENO NUMBER(10) NOT NULL PRIMARY KEY ,
ENAME VARCHAR(20) NOT NULL,
EJOB VARCHAR(20) ,
ENAMEB VARCHAR(20) ---如果ename改变了,备份原来的ename到enameb
);
CREATE SEQUENCE logs_id_squ INCREMENT BY 1
START WITH 1 MAXVALUE 9999999 NOCYCLE NOCACHE;
CREATE TABLE logs(
LOG_ID NUMBER(10) PRIMARY KEY,
LOG_TABLE VARCHAR2(10) NOT NULL,
LOG_DML VARCHAR2(10),
LOG_KEY_ID NUMBER(10),
LOG_NAME_NEW VARCHAR(20),
LOG_DATE DATE,
LOG_USER VARCHAR2(15)
);
INSERT INTO EMP VALUES (8001,'B','JOB1','') ;
COMMIT ;
SELECT * FROM EMP ;
-------------------------------
CREATE OR REPLACE TRIGGER DML_LOG BEFORE DELETE OR INSERT OR UPDATE ON emp
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO logs VALUES(logs_id_squ.NEXTVAL,'EMP','INSERT',:new.eno,:new.ename,SYSDATE,USER);
ELSIF DELETING THEN
update emp set enameb = :old.ename where eno = :old.eno ;
INSERT INTO logs VALUES(logs_id_squ.NEXTVAL,'EMP','DELETE',:old.eno,:old.ename,SYSDATE,USER);
ELSE
IF :new.ename<>:old.ename then
update emp set enameb = :old.ename where eno = :old.eno ;
INSERT INTO logs VALUES(logs_id_squ.NEXTVAL,'EMP','UPDATE',:old.eno,:old.ename,SYSDATE,USER);
ELSE
INSERT INTO logs VALUES(logs_id_squ.NEXTVAL,'EMP','UPDATE',:old.eno,:old.ename,SYSDATE,USER);
END IF;
END IF;
END;
----------------------------------
select * from emp ;
select * from logs ;
update emp set ejob = 'job2' where eno = '8001' ;
update emp set ename = 'C' where eno = '8001' ;
delete emp where eno = '8001'
发现果然不通过,锁起emp表不让进行操作。不过,如果我想改应该怎么办呢??留下这个问题,等待解决吧