CREATE触发器帮助Oracle
我正在尝试创建一个触发器来执行以下操作。
在表 A 上插入后,根据 id (TableA.id=TableB.id) 查询表 B 并将相应的信息插入表 A
我有一种感觉,到目前为止我还很遥远,所以任何帮助将不胜感激
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;
I'm trying to create a trigger that will do the following.
After insert on Table A, query Table B based on an id (TableA.id=TableB.id) and insert corresponding info into TableA
I have a feeling I'm way off so far so any help would be appreciated
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;
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
这是行不通的。想想你在问什么。您需要一个在将行插入 tableA 时触发的触发器,以将行插入 tableA。扳机什么时候会停止射击?
Oracle 足够聪明,可以介入并防止触发器陷入无限:
以下是发生的情况:
如果“更新”实际上意味着更新,并且 - 至关重要的是 - 取决于您希望实现的精确逻辑,那么也许是的:
仍然是一个坏主意。触发器很难理解,并且可能对性能产生有害影响因此,我建议您尝试找出一种将逻辑构建到应用程序主体中的方法,而不是尝试使用触发器。
This will not work. Just think about what you are asking. You want a trigger that fires when a row is inserted into tableA to insert a row into tableA. When would the trigger stop firing?
Oracle is smart enough to step in and prevent the trigger spiralling into infinity:
Here's what happens:
If by "update" you actually do mean UPDATE, and - crucially - depending on the precise logic you wish to implement then perhaps yes:
However this remains a bad idea. Triggers are hard to understand and can have a deleterious nimpact on the performance of our SQL. So I suggest you try to figure out a way of building your logic into the main body of your application, rather than trying to use a trigger.