?

? ?终于修改好了触发器，对于使用自治事务的行级触发器，需要判断正在修改的数据的比较，之前一直写错了，这回来个最终版本的，之前的错误是rownum引起的。

select * From (select rownum rn,表.* from 表) where rn = 2  rownum使用=号时需要使用别名

===============================================

CREATE OR REPLACE TRIGGER TB_PER_POSITONLEVEL_TRI

AFTER INSERT OR UPDATE OF c_effectivedate,c_postionlevel,c_postioncluster OR DELETE ON TB_PER_POSITONLEVEL

FOR EACH ROW

DECLARE

position_effdate DATE;

position_level varchar2(64);

position_order varchar2(64);

position_oid varchar2(128);

cou NUMBER(3);

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

? ? ?--SELECT COUNT(*) INTO cou FROM (SELECT a.c_oid FROM TB_PER_POSITONLEVEl a,tb_inf_employee b WHERE a.c_empoid = b.c_employeeid AND ?a.c_empoid = :NEW.c_empoid ORDER BY nvl(a.C_EFFECTIVEDATE,TO_DATE('1900/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS') ) DESC,a.c_operatetime DESC);

? ? ?--INSERT INTO A_TEMP VALUES(cou);

? ? ?IF inserting THEN

? ? ? ? SELECT COUNT(*) INTO cou FROM (SELECT a.c_oid FROM TB_PER_POSITONLEVEl a,tb_inf_employee b WHERE a.c_empoid = b.c_employeeid AND ?a.c_empoid = :NEW.c_empoid ORDER BY nvl(a.C_EFFECTIVEDATE,TO_DATE('1900/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS') ) DESC,a.c_operatetime DESC);

?

? ? ? ?-- INSERT INTO A_TEMP VALUES('行级别触发器after');

? ? ? ? --INSERT INTO A_TEMP VALUES(position_oid);

? ? ? ? --INSERT INTO A_TEMP VALUES( to_char(position_effdate,'fmdd month yyyy') );

? ? ? ? --INSERT INTO A_TEMP VALUES(position_level);

? ? ? ? --INSERT INTO A_TEMP VALUES(position_order);

? ? ? ? --INSERT INTO A_TEMP VALUES(cou);

? ? ? ? --以前有记录，插入记录需要与以往记录的最大日期比较。

? ? ? ? IF cou <> 0 THEN

? ? ? ? ? ?SELECT CASE WHEN :new.c_oid = c_oid THEN :NEW.c_postionlevel ELSE c_postionlevel END INTO position_level FROM (SELECT a.c_oid, a.c_postionlevel FROM TB_PER_POSITONLEVEl a,tb_inf_employee b WHERE a.c_empoid = b.c_employeeid AND ?a.c_empoid = :NEW.c_empoid ORDER BY nvl(a.C_EFFECTIVEDATE,TO_DATE('1900/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS') ) DESC,a.c_operatetime DESC) ?WHERE ROWNUM =1 ;

? ? ? ? ? ?SELECT ?c_effectivedate ?INTO position_effdate FROM (SELECT a.c_oid,a.c_effectivedate FROM TB_PER_POSITONLEVEl a,tb_inf_employee b WHERE a.c_empoid = b.c_employeeid AND ?a.c_empoid = :NEW.c_empoid ORDER BY nvl(a.C_EFFECTIVEDATE,TO_DATE('1900/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS') ) DESC,a.c_operatetime DESC) WHERE ROWNUM =1 ;

? ? ? ? ? ?SELECT CASE WHEN :new.c_oid = c_oid THEN :new.c_postioncluster ELSE c_postioncluster END INTO position_order FROM (SELECT a.c_oid,a.c_postioncluster FROM TB_PER_POSITONLEVEl a,tb_inf_employee b WHERE a.c_empoid = b.c_employeeid AND ?a.c_empoid = :NEW.c_empoid ORDER BY nvl(a.C_EFFECTIVEDATE,TO_DATE('1900/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS') ) DESC,a.c_operatetime DESC) WHERE ROWNUM =1 ;

? ? ? ? ? ?SELECT c_oid INTO position_oid FROM (SELECT a.c_oid FROM TB_PER_POSITONLEVEl a,tb_inf_employee b WHERE a.c_empoid = b.c_employeeid AND ?a.c_empoid = :NEW.c_empoid ORDER BY nvl(a.C_EFFECTIVEDATE,TO_DATE('1900/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS') ) DESC,a.c_operatetime DESC) WHERE ROWNUM =1 ;

?

? ? ? ? ? ?IF position_effdate <= :NEW.c_effectivedate THEN

? ? ? ? ? ? ? --INSERT INTO A_TEMP VALUES('插入的是最新的');

? ? ? ? ? ? ? UPDATE tb_inf_employee t SET t.c_positionlevel = :NEW.c_postionlevel,

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? t.c_positionorder = :NEW.c_postioncluster WHERE t.c_employeeid = :NEW.C_EMPOID;

? ? ? ? ? ? END IF;

? ? ? ? ? ? ELSIF position_effdate > :NEW.c_effectivedate THEN

? ? ? ? ? ? ? --INSERT INTO A_TEMP VALUES('插入的不是最新的，没有影响');

? ? ? ? ? ? ? UPDATE tb_inf_employee t SET t.c_positionlevel = position_level,

? ? ? ? ? ? ? ? ? ? ? ? ?