我有一个触发自主，但只在同一会话中执行一次 [英] I have a trigger autonomous but only execute one time in the same session

问题描述

  CREATE OR REPLACE TRIGGER tdw_insert_unsus 
 BEFORE INSERT ON unsuscription_fact每个ROW 
 DECLARE 
 PRAGMA AUTONOMOUS_TRANSACTION; 
 v_id_suscription SUSCRIPTION_FACT.ID_SUSCRIPTION％TYPE; 
 v_id_date_suscription SUSCRIPTION_FACT.ID_DATE_SUSCRIPTION％TYPE; 
 v_id_date_unsuscription SUSCRIPTION_FACT.ID_DATE_UNSUSCRIPTION％TYPE; 
 v_susciced DATE; 
 v_unsuscription DATE; 
 v_live_time SUSCRIPTION_FACT.LIVE_TIME％TYPE; 
 BEGIN 
 SELECT id_suscription，id_date_suscription 
 INTO v_id_suscription，v_id_date_suscription 
 FROM（
 SELECT id_suscription，id_date_suscription 
 FROM suscription_fact 
 WHERE id_mno =：NEW .ID_MNO 
 AND id_provider =：NEW.ID_PROVIDER 
 AND ftp_service_id =：NEW.FTP_SERVICE_ID 
 AND msisdn =：NEW.MSISDN 
 AND id_date_unsuscription IS NULL 
 ORDER BY id_date_suscription DESC 
）
 WHERE ROWNUM = 1; 
 
  - 计算时间
 v_unsuscription：= to_date（：NEW.id_date_unsuscription，'yyyymmdd'）; 
 v_suscription：= to_date（v_id_date_suscription，'yyyymmdd'）; 
 v_live_time：=（v_unsuscription  -  v_suscription）; 
 
 UPDATE suscription_fact SET id_date_unsuscription =：NEW.id_date_unsuscription，
 id_time_unsuscription =：NEW.id_time_unsuscription，live_time = v_live_time 
 WHERE id_suscription = v_id_suscription; 
 
 COMMIT; 
 
 EXCEPTION 
 WHEN NO_DATA_FOUND THEN 
 ROLLBACK; 
 END; 
 / 
  

如果我插入值工作顺利，第一次第二次，但是如果我注销会话和登录工作的第一次或第二次插入
是什么问题？我使用oracle 10g

解决方案

您正在使用自动事务来解决触发器无法查询其表本身的事实。你遇到了臭名昭着的mutating表错误，你发现声明触发器作为自治事务会使错误消失。

没有运气，没有解决问题：

• 首先，任何事务逻辑都会丢失。您不能回滚 suscription_fact 表上的更改，它们已提交，而您的主要事务不能回滚。


• 触发器无法查看新行，因为新行尚未提交！由于触发在独立事务中运行，因此无法查看主事务所做的未提交更改：您将遇到完全错误的结果。

这就是为什么你不应该在自主事务中做任何业务逻辑。 （有合法的应用程序，但他们几乎完全限于日志/调试）。

在您的情况下，您应该：

1. 更新您的逻辑，使其不需要查询您的表（更新 suscription_fact 仅当新行比忘记在触发器中使用业务逻辑，并使用一个可以正确更新所有表的过程，或者使用一个可以使用的方法来使用一个过程，该过程可以在 id_date_unsuscription


2. 使用实际工作的解决方法（由Tom Kyte提供）。

（2）这里。不要使用触发器来编写业务逻辑。他们很难写没有错误，更难以维护。使用过程保证所有相关代码都集中在一个位置（包或过程），易于阅读和跟踪，并且没有不可预见的后果。

I have a trigger autonomous but only execute one time in the same session, then do nothing

CREATE OR REPLACE TRIGGER tdw_insert_unsus
BEFORE INSERT ON unsuscription_fact FOR EACH ROW
DECLARE
 PRAGMA AUTONOMOUS_TRANSACTION;
 v_id_suscription               SUSCRIPTION_FACT.ID_SUSCRIPTION%TYPE;
 v_id_date_suscription          SUSCRIPTION_FACT.ID_DATE_SUSCRIPTION%TYPE;
 v_id_date_unsuscription        SUSCRIPTION_FACT.ID_DATE_UNSUSCRIPTION%TYPE;
 v_suscription                  DATE;
 v_unsuscription                DATE; 
 v_live_time                    SUSCRIPTION_FACT.LIVE_TIME%TYPE;
BEGIN
    SELECT id_suscription, id_date_suscription 
    INTO v_id_suscription, v_id_date_suscription
    FROM(
        SELECT id_suscription, id_date_suscription
        FROM suscription_fact
        WHERE id_mno = :NEW.ID_MNO
        AND id_provider = :NEW.ID_PROVIDER
        AND ftp_service_id = :NEW.FTP_SERVICE_ID
        AND msisdn = :NEW.MSISDN
        AND id_date_unsuscription IS NULL
        ORDER BY id_date_suscription DESC
    )
    WHERE ROWNUM = 1;

    -- calculate time
    v_unsuscription := to_date(:NEW.id_date_unsuscription,'yyyymmdd');
    v_suscription := to_date(v_id_date_suscription,'yyyymmdd');
    v_live_time := (v_unsuscription - v_suscription);

    UPDATE suscription_fact SET id_date_unsuscription = :NEW.id_date_unsuscription, 
    id_time_unsuscription = :NEW.id_time_unsuscription, live_time = v_live_time
    WHERE id_suscription = v_id_suscription;

    COMMIT;

    EXCEPTION
        WHEN NO_DATA_FOUND THEN
        ROLLBACK;
END;
/

if I insert values works well the first o second time but after not work, but if I logout the session and login works for the first or second insertion what is the problem?, I use oracle 10g

解决方案

You're using an autonomous transaction to work around the fact that a trigger can not query its table itself. You've run into the infamous mutating table error and you have found that declaring the trigger as an autonomous transaction makes the error go away.

No luck for you though, this does not solve the problem at all:

• First, any transaction logic is lost. You can't rollback the changes on the suscription_fact table, they are committed, while your main transaction is not and could be rolled back. So you've also lost your data integrity.
• The trigger can not see the new row because the new row hasn't been committed yet! Since the trigger runs in an independent transaction, it can not see the uncommitted changes made by the main transaction: you will run into completely wrong results.

This is why you should never do any business logic in autonomous transactions. (there are legitimate applications but they are almost entirely limited to logging/debugging).

In your case you should either:

1. Update your logic so that it does not need to query your table (updating suscription_fact only if the new row is more recent than the old value stored in id_date_unsuscription).
2. Forget about using business logic in triggers and use a procedure that updates all tables correctly or use a view because here we have a clear case of redundant data.
3. Use a workaround that actually works (by Tom Kyte).

I would strongly advise using (2) here. Don't use triggers to code business logic. They are hard to write without bugs and harder still to maintain. Using a procedure guarantees that all the relevant code is grouped in one place (a package or a procedure), easy to read and follow and without unforeseen consequences.

这篇关于我有一个触发自主，但只在同一会话中执行一次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！