How insert data into multiple table? /oracle sql/

Question

0.00/5 (No votes)

See more:

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...

SQL

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 when users insert value then take id with my_school's "info_id" same time?

Posted 27-Mar-14 22:47pm

Orgil Ganzorig

Updated 29-Mar-14 10:28am

Maciej Los

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

sudeepvajrala4114 · Answer 1 · 2014-04-10T23:04:00

Solution 1

As per my knowledge..You should not use trigger here. Create a SP that inserts data in to your two tables.
Before inserting to any table store example_id_seq.nextval in to a varibale and use the same variable in to both the table insert statements.

Posted 10-Apr-14 23:04pm

sudeepvajrala4114

Kabi Pr. Sahoo · Answer 2 · 2014-04-10T23:50:00

You can add to multiple tables through Procedure in sql...

procedure

SQL

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		
-- Create date: 
-- Description:	
-- =============================================
ALTER PROCEDURE [dbo].[P_ItemMaster] 
	-- Add the parameters for the stored procedure here
@ItemId int=0,
@ItemName varchar(100)=null,
@SubCategory_Id int=0,
@DenominationId int=0,
@Created_By varchar(50)=null,
@Created_On varchar(100)=null,
@Modified_By varchar(50)=null,
@Modified_On varchar(100)=null,
@COND VARCHAR(1)=null,
@ItemCode VARCHAR(100)=NULL
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here

IF(@COND='I' OR @COND='i')
begin
insert into T_Itemmaster(ItemName,ItemCode,SubCategory_Id,DenominationId,Created_By,Created_On) values(@ItemName,@ItemCode,@SubCategory_Id,@DenominationId,@Created_By,@Created_On)
insert into T_item_masterDeatils(.......) values(......)

end

END