compile error when creating an "instead of" trigger for a view

Question

I've created an "instead of" trigger for a simple view that only does select * on a table and a trigger that does nothing (I wanted to minimize the problem):

create or replace view tmp(id, nazwa, nip, adres, zalega, punkty) as
select * from klient

create or replace trigger tmp_trg
instead of insert
on tmp
for each row
begin

end;

The view is created. Then when I want do declare this trigger sql developer returns error:

Error(8,1): PLS-00103: Encountered the symbol "END" when expecting one of the following: begin case declare exit for goto if loop mod null pragma raise return select update while with 'an identifier' 'a double-quoted delimited-identifier' 'a bind variable' << close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe

Note: if you just want to stop people inserting into your view, just revoke the INSERT privileges from the view. — Jeffrey Kemp
– Jeffrey Kemp, Commented Nov 29, 2010 at 6:10

erbsock · Accepted Answer · 2010-11-29 00:00:34Z

5

just add "null;" between the begin and the end.

answered Nov 29, 2010 at 0:00

erbsock

1,2178 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ZdenekSmetana Over a year ago

It didnt help. When i had the real stuff in the trigger (before minimizing the problem) the error code was the same. So having trigger that does nothing is not the problem.

Sathyajith Bhat · Accepted Answer · 2010-11-29 01:11:23Z

1

a trigger that does nothing (I wanted to minimize the problem):

I'm not sure I understand why you need a trigger if it does nothing ?

To answer, there isn't a valid PL/SQL statement in your trigger code block, add a NULL to make it a valid block and have no action.

create or replace trigger tmp_trg
instead of insert
on tmp
for each row
begin
NULL;
end;

answered Nov 29, 2010 at 1:11

Sathyajith Bhat

22k22 gold badges102 silver badges141 bronze badges

1 Comment

ZdenekSmetana Over a year ago

When I created the real trigger which was doing stuff, it caused the same problem. So I've tried to declare a trigger with nothing inside to check if it will casuse the same problem and it did. It helps in searching the cause of the problem because we dont have to check if the syntax inside the trigger caused the problem. Minimizing problems works this way if you dont know. I thought it is be obvious.

Collectives™ on Stack Overflow

compile error when creating an "instead of" trigger for a view

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related