CREATE触发器帮助Oracle

我正在尝试创建一个触发器，它将执行以下操作。 在表A上插入后，根据id（TableA.id = TableB.id）查询表B并将相应的信息插入TableA 到目前为止，我有一种感觉，所以任何帮助都会受到赞赏 
CREATE OR REPLACE TRIGGER myTrig
AFTER INSERT
ON TABLEA
BEGIN
  INSERT INTO TABLEA
  SELECT TABLEB.FIRST_NAME, TABLEB.LAST_NAME, SYSDATE
  FROM TABLEA JOIN TABLEB ON 
  TABLEA.STUDENT_ID=TABLEB.STUDENT_ID
  insert into TABLEA values (....);
END;
    
2019-05-22 2 条评论
分享

没有找到相关结果

    已邀请:

    恋裂

    这不行。想想你在问什么。您希望在将一行插入tableA以将行插入tableA时触发该触发器。触发器何时停止发射？ Oracle非常聪明，可以介入并阻止触发器进入无限远： 
    SQL> create or replace trigger t69_after_ins
  2      after insert on t69
  3  begin
  4      insert into t69 values ('blah', 'blah', 99);
  5  end;
  6  /

Trigger created.

SQL>
    这是发生的事情： 
    SQL> insert into t69 values ('this', 'that', 1)      
   2  /

insert into t69 values ('this', 'that', 1)
            *
ERROR at line 1:
ORA-00036: maximum number of recursive SQL levels (50) exceeded
ORA-06512: at "APC.T69_AFTER_INS", line 2
ORA-04088: error during execution of trigger 'APC.T69_AFTER_INS'
ORA-06512: at "APC.T69_AFTER_INS", line 2
ORA-04088: error during execution of trigger 'APC.T69_AFTER_INS'
ORA-06512: at "APC.T69_AFTER_INS", line 2
ORA-04088: error during execution of trigger 'APC.T69_AFTER_INS'
ORA-06512: at "APC.T69_AFTER_INS", line 2
ORA-04088: error during execution of trigger 'APC.T69_AFTER_INS'
ORA-06512: at "APC.T69_AFTER_INS", line 2
ORA-04088: error during execution of trigger 'APC.T69_AFTER_INS'
ORA-06512: at "APC.T69_AFTER_INS", line 2
ORA-04088: error during execution of trigger 'APC.T69_AFTER_INS'
ORA-06512: at "APC.T69_AFTER_INS", line 2
ORA-04088: error during execution of trigger 'APC.T69_AFTER_INS'
ORA-06512: at "APC.T69_AFTER_INS", line 2
ORA-04088: error during execution of trigger 'APC.T69_AFTER_INS'
ORA-06512: at "APC.T69_AFTER_INS", line 2
ORA-04088: error during execution of trigger 'APC.T69_AFTER_I


SQL>
      “有什么方法可以更新   TableA基于TableB给出的信息   两个表中都有一个id？ “ 如果通过“更新”你实际上意味着UPDATE，并且 - 至关重要 - 根据你希望实现的精确逻辑，那么也许是： 
    SQL> create or replace trigger t69_after_ins
  2      after insert on t69
  3  begin
  4      update t69
  5          set name = ( select name from t23
  6                       where t23.id = t69.id )
  7          where name is null;
  8  end;
  9  /

Trigger created.

SQL> insert into t69 (id, name) values (122, null)
  2  /

1 row created.

SQL> select name from t69
  2  where id = 122
  3  /

NAME
----------
MAISIE

SQL>
    然而，这仍然是一个坏主意。触发器很难理解，并且可能对我们的SQL性能产生有害的影响。因此，我建议您尝试找出将逻辑构建到应用程序主体中的方法，而不是尝试使用触发器。     
    2019-05-22 0 0
    分享
    为什么被折叠? 0 个回复被折叠

    要回复问题请先登录或注册

    或代码 OrCode.com 备案号：粤ICP备15020848号-1
    Escape time: 0.036687850952148