VIEW Report Triggers in Oracle

Question

Is it possible in Oracle DB to create a Statement trigger (but not a Row trigger) on VIEW?

When I create a parameter INSTEAD OFwith no option FOR EACH ROWin the view, Oracle fires this trigger for each row in any way.

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;

It 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 TEST_TABLE(instead TEST_VIEW), the output will be as expected:

ROW TRIGGER: one
ROW TRIGGER: two
ROW TRIGGER: three
STATEMENT TRIGGER.

Is there any workaround?

Answer 1

INSTEAD OF View triggers are always row-based, as indicated in the Docs: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7004.htm#i2235611

, . Oracle Database , , , WHEN.

INSTEAD OF, , . Oracle Database , , .

INSTEAD .