CODEWITHSUNDEEP
sqloracle-database
Fazal Jarral
Fazal Jarral

Reputation: 170

SQL Trigger Error ORA-04098

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?

Upvotes: 0

Views: 443

Answers (1)

Connor McDonald
Connor McDonald

Reputation: 11586

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

Upvotes: 1

Related Questions