CODEWITHSUNDEEP
sqloracle-databasejointriggersinsert
All Solution
All Solution

Reputation: 3

Oracle trigger insert other table

I have 2 tables which are my_school and my_class

And "my_school" table has 'info_id' column and also "my_class" table has 'info_id' then I want to get a query that automatically generate "info_id" then I found solution..

Here are my working TRIGGER on "my_school" table...

CREATE OR REPLACE TRIGGER info_id

 before insert on my_direction 

 for each row

 begin 

 if :NEW.WAY_ID is null then 

 :NEW.WAY_ID := example_id_seq.nextval; 

 end if;

 end;

It works and it's generating auto id when inserting value. But now how to get this trigger do it on "my_class" table when users insert value on my_school's table then take id with "my_class" table's "info_id" column same time?

Upvotes: 0

Views: 142

Answers (1)

San
San

Reputation: 4538

You can create trigger on my_school table to update info_id similar to that you have explained and while inserting records, use returning into clause.

Declare a variable to store returned value, for example 

v_info_id number(9);

And use it in returning into clause

insert into my_school(column.......list) 
values (values........list)
RETURNING info_id INTO v_info_id;

Use v_info_id in your program to insert value of info_id into another tables.

Upvotes: 1

Related Questions