如何将数据插入多个表？ / oracle sql / [英] How insert data into multiple table? /oracle sql/

问题描述

我有2个表是my_school和my_class



并且my_school表有'info_id'列，而且'my_class'表有'info_id'然后我想得到一个自动生成info_id的查询，然后我找到了解决方案..



这是我在my_school表上工作的TRIGGER ......

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;

它的工作原理和它在插入值时会生成自动ID。但是现在如何在用户插入值时获取此触发器，然后同时使用my_school的info_id获取id？

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?

推荐答案

根据我的知识..你不应该使用触发器这里。创建一个将数据插入到两个表中的SP。

在插入任何表之前，将example_id_seq.nextval插入到varibale中，并在表插入语句中使用相同的变量。

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.

你可以通过sql中的Procedure添加到多个表...





程序

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


procedure

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

这篇关于如何将数据插入多个表？ / oracle sql /的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！