CODEWITHSUNDEEP
sqloracle-database
lcazarre
lcazarre

Reputation: 743

Oracle Compound Trigger

I realise this is a very basic question, but I have been stuck for three hours already and I could not find any hint from any help forum.

I started using PL SQL today and I immediately ran into the table mutation problem. I now want to write a compound trigger to get round the problem.

But I am not able to run the most basic compound trigger, not even when I copy the following code from Oracle's documentation pages.

http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/triggers.htm#LNPLS2005

I get the following error:

Error: ORA-04071: missing BEFORE, AFTER or INSTEAD OF keyword
SQLState:  42000
ErrorCode: 4071

when I run this:

CREATE OR REPLACE TRIGGER AverageUpdate FOR UPDATE OF Rating ON UserGame
COMPOUND TRIGGER;

BEFORE STATEMENT IS
BEGIN
NULL;
END BEFORE STATEMENT; 

BEFORE EACH ROW IS
BEGIN
NULL;
END BEFORE EACH ROW;

AFTER EACH ROW IS
BEGIN
NULL;
END AFTER EACH ROW;

AFTER STATEMENT IS
BEGIN
NULL;
END AFTER STATEMENT;

END AverageUpdate;
/

Could anyone help me understand why?

Note: I am using Squirrel. I changed the default statement separator from ; to /

Upvotes: 1

Views: 879

Answers (1)

Justin Cave
Justin Cave

Reputation: 231661

You're getting the error because you are running a very old version of Oracle (9.2.0.6 has been deprecated for quite a number of years) and compound triggers did not exist at that time. In 9.2, you'd have to declare multiple triggers that worked together to work around the mutating trigger error.

Upvotes: 1

Related Questions