Oracle 中 VIEW 上的语句触发器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4959255/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Statement Triggers on a VIEW in Oracle
提问by Volodymyr Frolov
Is it possible in Oracle DB to create Statement trigger (but not Row trigger) on a VIEW?
是否可以在 Oracle DB 中创建 Statement 触发器（但不是 Row 触发器）VIEW？
When I create INSTEAD OFtrigger without FOR EACH ROWoption on a view, Oracle fires that trigger for each row any way.
当我在视图上创建INSTEAD OF没有FOR EACH ROW选项的触发器时，Oracle 会以任何方式为每一行触发该触发器。
For example, the following code:
例如，以下代码：
CREATE TABLE TEST_TABLE (
MY_DATA VARCHAR(30)
);
INSERT INTO TEST_TABLE(MY_DATA) VALUES('one');
INSERT INTO TEST_TABLE(MY_DATA) VALUES('two');
INSERT INTO TEST_TABLE(MY_DATA) VALUES('three');
CREATE OR REPLACE VIEW TEST_VIEW AS
SELECT * FROM TEST_TABLE;
CREATE OR REPLACE TRIGGER TEST_VIEW_TRG1
INSTEAD OF DELETE ON TEST_VIEW
DECLARE
BEGIN
Dbms_Output.Put_Line('STATEMENT TRIGGER.');
END;
/
CREATE OR REPLACE TRIGGER TEST_VIEW_TRG2
INSTEAD OF DELETE ON TEST_VIEW FOR EACH ROW
DECLARE
BEGIN
Dbms_Output.Put_Line('ROW TRIGGER: '||:OLD.MY_DATA);
END;
/
DELETE FROM TEST_VIEW;
Produces the following output:
产生以下输出：
ROW TRIGGER: one
STATEMENT TRIGGER.
ROW TRIGGER: two
STATEMENT TRIGGER.
ROW TRIGGER: three
STATEMENT TRIGGER.
When I create triggers TEST_VIEW_TRG1and TEST_VIEW_TRG2as AFTERon a TEST_TABLE(instead of a TEST_VIEW) the output is as expected:
当我创建触发器TEST_VIEW_TRG1，并TEST_VIEW_TRG2为AFTER上TEST_TABLE（而不是一个TEST_VIEW）如预期的输出：
ROW TRIGGER: one
ROW TRIGGER: two
ROW TRIGGER: three
STATEMENT TRIGGER.
Is there any workaround for this issue?
这个问题有什么解决方法吗？
回答by Michael Broughton
INSTEAD OF Triggers on views are always row-based, as stated in the Docs: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7004.htm#i2235611
INSTEAD OF 触发器始终基于行，如文档中所述：http: //download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7004.htm#i2235611
FOR EACH ROW Specify FOR EACH ROW to designate the trigger as a row trigger. Oracle Database fires a row trigger once for each row that is affected by the triggering statement and meets the optional trigger constraint defined in the WHEN condition.
FOR EACH ROW 指定 FOR EACH ROW 将触发器指定为行触发器。Oracle 数据库为受触发语句影响并满足 WHEN 条件中定义的可选触发器约束的每一行触发一次行触发器。
Except for INSTEAD OF triggers, if you omit this clause, then the trigger is a statement trigger. Oracle Database fires a statement trigger only once when the triggering statement is issued if the optional trigger constraint is met.
除 INSTEAD OF 触发器外，如果省略此子句，则触发器为语句触发器。如果满足可选触发器约束，则在发出触发语句时，Oracle 数据库仅触发一次语句触发器。
INSTEAD OF trigger statements are implicitly activated for each row.
INSTEAD OF 触发器语句为每一行隐式激活。
