CODEWITHSUNDEEP
oracle-databasetriggers
Volodymyr Frolov
Volodymyr Frolov

Reputation: 1306

Statement Triggers on a VIEW in Oracle

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

When I create INSTEAD OF trigger without FOR EACH ROW option on a view, Oracle fires that trigger for each row 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;

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_TRG1 and TEST_VIEW_TRG2 as AFTER on a TEST_TABLE (instead of a TEST_VIEW) the output is as expected:

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

Is there any workaround for this issue?

Upvotes: 3

Views: 25906

Answers (1)

Michael Broughton
Michael Broughton

Reputation: 4055

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

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.

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 trigger statements are implicitly activated for each row.

Upvotes: 9

Related Questions