CODEWITHSUNDEEP
oracle-databaseplsqltriggers
Alex
Alex

Reputation: 1232

oracle trigger throws ora-04098

Seeking some basic help, probably a typo in code.

I have the following:

create table scott.original(mystr varchar2(10))
insert into scott.original values('hi')
select * from scott.original

CREATE OR REPLACE TRIGGER scott.original_trigger
AFTER INSERT ON scott.original
FOR EACH ROW
BEGIN
    select 'deleting or updating' from dual
END;
/

insert into scott.original values('bye')

There insert of 'bye' throws error ora-04098, I can't see an issue in oracle, granted first time I'm trying to create a trigger in oracle.

Upvotes: 1

Views: 422

Answers (1)

Justin Cave
Justin Cave

Reputation: 231861

Any SELECT statement in a PL/SQL block needs to select the data INTO some variable. 

CREATE OR REPLACE TRIGGER scott.original_trigger
AFTER INSERT ON scott.original
FOR EACH ROW
DECLARE
    l_variable VARCHAR2(100);
BEGIN
    select 'deleting or updating' 
      into l_variable
      from dual
END;
/

will be valid syntax. Of course, this trigger isn't actually doing anything but I assume that's something you're planning on addressing later.

Upvotes: 4

Related Questions