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