SQL Trigger Error ORA-04098

Question

i have two tables named as childs and parent.

create table parent(
    wallet_id NUMBER generated always as identity,
    amount int,
    customer_id int,
    primary key(wallet_id),
    Foreign key (customer_id) REFERENCES childs(customer_id) 
);

create table childs(
    customer_id number generated always as identity,
    name varchar2 (255)
);

Now what i wanted to achieve was to get name from childs , and assign a wallet_id to childs in parent table as parent table has a foreign key to customer_id. For this purpose i created a trigger.

create or replace 
TRIGGER TRIGGER1 
AFTER INSERT ON CHILDS 
Declare 
id int;
BEGIN
select MAX(customer_id) into id FROM childs;
insert into parent (customer_id ) values ( id );

END;

Now trigger is created but when i insert value in childs,

insert into childs(names) values ('jarral');

Now following error appears:

SQL Error: ORA-04098: trigger 'SYSTEM.TRG' is invalid and failed re-validation 04098. 00000 - "trigger '%s.%s' is invalid and failed re-validation" *Cause: A trigger was attempted to be retrieved for execution and was found to be invalid. This also means that compilation/authorization failed for the trigger. *Action: Options are to resolve the compilation/authorization errors, disable the trigger, or drop the trigger.

How can i solve that?

What are the compilation errors? ORA-04098 just tells us that it's invalid. By the way, why is childs the parent of parent? And your definition is missing a primary/unique key for the FK on parent to refer to, and the system schema is reserved for Oracle internal objects that you are not supposed to go near. Aside from that, the trigger creates without error. Your insert statement refers to a column called names that doesn't exist, but otherwise runs fine. — William Robertson

Connor McDonald Connor McDonald · Accepted Answer · 2018-07-06T14:29:30

I'm not entirely sure I follow your question, but is this what you are after ?

SQL> create table t1 ( id number generated as identity , x int);

Table created.

SQL> create table t2 ( id number generated as identity , t1_id int);

Table created.

SQL>
SQL> create or replace
  2  trigger trg
  3  after insert on t1
  4  for each row
  5  begin
  6    insert into t2 (t1_id) values ( :new.id);
  7  end;
  8  /

Trigger created.

SQL> insert into t1 (x) values ( 0);

1 row created.

SQL> select * from t1;

        ID          X
---------- ----------
         1          0

SQL> select * from t2;

        ID      T1_ID
---------- ----------
         1          1

SQL Trigger Error ORA-04098

1 Answers