关于oracle触发器锁表问题-20081218

今天测试关于触发器问题，把更改的数据保存到另一个表。这个比较简单。

---------------------
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表不让进行操作。不过，如果我想改应该怎么办呢？？留下这个问题，等待解决吧