如何在每次插入后触发触发器 - Oracle（批量插入）
我希望触发器在插入的每条记录之后运行。
如果我有一个像这样的简单插入，则此验证工作正常：
insert into g_dossier values
(112334, 'BBT', 'LPO','TTS','Y') ;
但是，当它像这样的批量插入时：
INSERT INTO g_piece(
refpiece,
typpiece,
class_piece
group_piece
flag_piere)
SELECT :new.element_num,
PROC_TYPE,
DECODE( piece_it, 'F', 'FTTR', 'N', 'FTTR', NULL ),
DECODE( piece_it, 'T', 'TTSN', 'N', 'TTSN', NULL ),
'N'
FROM t_elements
WHERE :new.db_piece_flag = 'Y';
触发器会发生变化。我希望验证在作为批量插入完成时也能起作用。
导致此问题的查询是
SELECT COUNT(*)
INTO existing_cmcl_cnt
FROM g_piece cmcl
WHERE cmcl.class_piece= :new.class_piece
问题是在应用于同一表“g_piece”的触发器中调用此查询。当我继续进行简单的插入（插入 g_piece value(...)）时，我没有这个问题。
我怎样才能避免这个问题？ 谢谢。
I want the trigger to run after each and every record that gets inserted.
This validation works fine if I have a simple insert like this:
insert into g_dossier values
(112334, 'BBT', 'LPO','TTS','Y') ;
However, when it’s like a bulk insert like this:
INSERT INTO g_piece(
refpiece,
typpiece,
class_piece
group_piece
flag_piere)
SELECT :new.element_num,
PROC_TYPE,
DECODE( piece_it, 'F', 'FTTR', 'N', 'FTTR', NULL ),
DECODE( piece_it, 'T', 'TTSN', 'N', 'TTSN', NULL ),
'N'
FROM t_elements
WHERE :new.db_piece_flag = 'Y';
The trigger mutates. I want that the validation also work when done as a bulk insert.
The query causing this problem is
SELECT COUNT(*)
INTO existing_cmcl_cnt
FROM g_piece cmcl
WHERE cmcl.class_piece= :new.class_piece
The problem is that this query is called in a trigger applied on the same table "g_piece". When I proceed with a simple insert (insert into g_piece values(...)), I do not have this problem.
How can I avoid this problem?
Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
在这里，您应该更改查询并插入
SELECT ... FROM some join的结果，根本不使用触发器。您希望在插入表X时，触发器也会插入到同一个表中，这是不可能的（它会递归）。如果无法更改查询，则应重命名表，使用旧名称创建表视图，并在该视图上为每个行创建一个
TRIGGER INSTEAD INSTEAD OF INSERT这会将
SELECT ... FROM some join的结果INSERT INTO 到真实表中。Here you should change the query and insert the result of a
SELECT ... FROM some join, not using a trigger at all. You want that when inserting into tableX, a trigger also inserts into the same table, which is not possible (it would recurse).If you cannot change the query, you should rename your table, create a view to the table with the old name, and create a
TRIGGER INSTEAD OF INSERT ON that view FOR EACH ROWthat would
INSERT INTO into the real tablethe result of aSELECT ... FROM some join.如果您使用的是 Oracle 11G，则可以查看复合触发器以避免突变表错误。看一下：
http://download.oracle.com/docs /cd/B28359_01/appdev.111/b28370/triggers.htm#CIHEFGFD
If you're using Oracle 11G, you can take a look to Compound triggers to avoid the mutating-table error. Take a look:
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/triggers.htm#CIHEFGFD