更新触发触发器的同一记录

发布于 2024-09-29 13:22:05 字数 816 浏览 3 评论 0原文

我想更新触发触发器的同一记录。我已经使用“BEFORE INSERT”做到了这一点选项。但请注意，如果出现任何故障，我会使用事务来回滚操作。

CREATE OR REPLACE TRIGGER GANUKA.INTF_CONTROLLER_UPLOADER
BEFORE insert ON GANUKA.INTF_CONTROLLER for each row
DECLARE 
    PRAGMA AUTONOMOUS_TRANSACTION;
    max_id INTEGER;
    stat VARCHAR2(32);
begin

select :new.id into max_id from dual;
select :new.status into stat from dual;



 IF STAT = 'NEW' THEN   --ONLY NEW UPLOADS WILL CONTINUE FOR PROCESS
    :NEW.STATUS := 'STARTED';
     max_id := GANUKA.BACKOFFICE_UPDATE(max_id); --PL/SQL function
    :NEW.STATUS := 'COMPLETED';

ELSE
   :NEW.STATUS := 'ABORTED';
    :NEW.REMARKS :='STATUS IS NOT RECONGNIZED';

END IF;

COMMIT;

EXCEPTION
    WHEN OTHERS
     THEN
       ROLLBACK;
        RAISE;

end;
/

问题是，如果出现任何异常，我想更新记录以将状态设置为“失败”。谁能告诉我该怎么做。

原文

I want to update the same record which fires a trigger. I have done that using "BEFORE INSERT"
option. But note that I have use a transaction to rollback the operation if there is an any faliure.

CREATE OR REPLACE TRIGGER GANUKA.INTF_CONTROLLER_UPLOADER
BEFORE insert ON GANUKA.INTF_CONTROLLER for each row
DECLARE 
    PRAGMA AUTONOMOUS_TRANSACTION;
    max_id INTEGER;
    stat VARCHAR2(32);
begin

select :new.id into max_id from dual;
select :new.status into stat from dual;



 IF STAT = 'NEW' THEN   --ONLY NEW UPLOADS WILL CONTINUE FOR PROCESS
    :NEW.STATUS := 'STARTED';
     max_id := GANUKA.BACKOFFICE_UPDATE(max_id); --PL/SQL function
    :NEW.STATUS := 'COMPLETED';

ELSE
   :NEW.STATUS := 'ABORTED';
    :NEW.REMARKS :='STATUS IS NOT RECONGNIZED';

END IF;

COMMIT;

EXCEPTION
    WHEN OTHERS
     THEN
       ROLLBACK;
        RAISE;

end;
/

Problem is if there is an an any exception I want to update the record to set the state as 'Failed'. Can any one tell me how to do that.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

浅笑依然 2024-10-06 13:22:05

我不确定为什么在这里使用自治事务以及为什么必须在触发器中提交/回滚...

回复收藏 0 原文

才能让你更想念 2024-10-06 13:22:05

这个有作用吗？

CREATE OR REPLACE TRIGGER GANUKA.INTF_CONTROLLER_UPLOADER
BEFORE insert ON GANUKA.INTF_CONTROLLER for each row
DECLARE 
    max_id INTEGER;
    stat VARCHAR2(32);
begin

 max_id := :new.id;
 stat := :new.status;

 IF STAT = 'NEW' THEN   --ONLY NEW UPLOADS WILL CONTINUE FOR PROCESS
    DECLARE 
      PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
      max_id := GANUKA.BACKOFFICE_UPDATE(max_id); --PL/SQL function
      COMMIT;
      :NEW.STATUS := 'COMPLETED';
    EXCEPTION
      WHEN OTHERS THEN
        ROLLBACK;
        :new.status := 'FAILED';
    END;

 ELSE
    :NEW.STATUS := 'ABORTED';
    :NEW.REMARKS :='STATUS IS NOT RECONGNIZED';

 END IF;

end;
/

Does this do it?

CREATE OR REPLACE TRIGGER GANUKA.INTF_CONTROLLER_UPLOADER
BEFORE insert ON GANUKA.INTF_CONTROLLER for each row
DECLARE 
    max_id INTEGER;
    stat VARCHAR2(32);
begin

 max_id := :new.id;
 stat := :new.status;

 IF STAT = 'NEW' THEN   --ONLY NEW UPLOADS WILL CONTINUE FOR PROCESS
    DECLARE 
      PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
      max_id := GANUKA.BACKOFFICE_UPDATE(max_id); --PL/SQL function
      COMMIT;
      :NEW.STATUS := 'COMPLETED';
    EXCEPTION
      WHEN OTHERS THEN
        ROLLBACK;
        :new.status := 'FAILED';
    END;

 ELSE
    :NEW.STATUS := 'ABORTED';
    :NEW.REMARKS :='STATUS IS NOT RECONGNIZED';

 END IF;

end;
/

回复收藏 0 原文

~没有更多了~