首页> 疑难解答

SQL Update触发器未按预期工作

withpy 2021-06-23

简介我的oracle表上有一个更新触发器..但似乎它不起作用。创建触发器和表的模式是HR创建或替换触发器TR_FinlStatAssetDesignation_U之前...

我在我的oracle表上有一个更新触发器..但似乎它不起作用..在哪个触发器和表创建的解决方案是HR

CREATE OR REPLACE Trigger TR_FinlStatAssetDesignation_U
BEFORE update
on FINLSTATASSETDESIGNATION FOR EACH ROW
   DECLARE
   v_AtDateTime  TIMESTAMP(3);
   v_LogOperation  NUMBER(3,0);
   v_UserName  VARCHAR2(255);
   v_AppName  VARCHAR2(255);
   SWV_error NUMBER(10,0) DEFAULT 0;
BEGIN

   begin
      select USERNAME INTO v_UserName FROM v$session  WHERE (audsid = SYS_CONTEXT('userenv','sessionid')) AND ROWNUM <=1;
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
         NULL;
   end;

   SELECT program INTO v_AppName FROM v$session WHERE audsid=userenv('sessionid'); 
   if (LENGTH(v_AppName) = 0) then 
      v_AppName := 'Unknown';
   end if; 

   SELECT SYSTIMESTAMP INTO v_AtDateTime FROM dual;

   if UPDATING('FinlStatAssetDesignation') then
      RAISE_APPLICATION_ERROR(-20000,'Invalid attempt to update OID FinlStatAssetDesignation in FinlStatAssetDesignation');
      /*    
      ROLLBACK */
      return;
   end if;

   if not UPDATING('UpdDate') then
      SWV_error := 0;
      begin
         UPDATE FinlStatAssetDesignation a SET(UpdDate) =(SELECT distinct v_AtDateTime FROM dual  where a.FinlStatAssetDesignation = :NEW.FinlStatAssetDesignation)
         WHERE ROWID IN(SELECT a.ROWID FROM FinlStatAssetDesignation a where a.FinlStatAssetDesignation = :NEW.FinlStatAssetDesignation);
         EXCEPTION
         WHEN OTHERS THEN
            SWV_error := SQLCODE;
      end;
      if SWV_error <> 0 then
         /* 
ROLLBACK */
return;

在这个触发器..第二部分,即下面的不工作的触发器...请帮助....它没有更新upddate列中的时间戳

SQL> select * from finlstatassetdesignation;

FINLSTATAS FINLSTATASSETDESIGNATIONDESC                       UPDOPERATION
---------- -------------------------------------------------- ------------
UPDDATE
---------------------------------------------------------------------------
one19      anyt                                                          0
           hinh
01-JAN-17 08.00.00.000000 AM


SQL> update finlstatassetdesignation set finlstatassetdesignationdesc ='nothing';

1 row updated.

SQL> select * From finlstatassetdesignation;

FINLSTATAS FINLSTATASSETDESIGNATIONDESC                       UPDOPERATION
---------- -------------------------------------------------- ------------
UPDDATE
---------------------------------------------------------------------------
one19      nothing                                                       0
01-JAN-17 08.00.00.000000 AM
2
投票

你想在ON INSERT触发器中完成什么?从你的评论

我仍然可以作为用户“hello”插入一行..因此触发器不起作用..

您似乎想要阻止添加名为HELLO的用户 - 但是如果找到HELLO的用户名,则在触发器代码中,您只需从触发器返回。这没有任何成就。要向系统发出不希望INSERT继续运行的信号,必须在触发器中引发异常。例如:

CREATE OR REPLACE Trigger TR_FinlStatAssetDesignation_I
  BEFORE Insert on FINLSTATASSETDESIGNATION
BEGIN
  if USER = 'HELLO' or USER = 'SYSTEM' then
    RAISE_APPLICATION_ERROR(-20000, 'Invalid user in TR_FinlStatAssetDesignation_I');
  end if;
END TR_FinlStatAssetDesignation_I;

以这种方式引发异常将阻止INSERT继续成功完成。您的代码负责提供适当的错误处理程序。

祝你好运。

相关文章