在Oracle中触发以自动增加字段并插入关联表 [英] Trigger in Oracle to auto-increment a field and insert into association table

问题描述

我在两个表之间存在一对多的关系:

I have a one-to-many relationship between two tables:

table1:

NUMBER users_id (primary key)
field2
field3
...

table2:

NUMBER users_id (foreign key)
VARCHAR2 name
...
...

，当我INSERT进入table1时，我想自动递增(顺序?)users_id并将许多记录插入table2中，所有记录都具有相同的users_id，所以我最终得到

and when I INSERT into table1, I want to auto increment (sequence?) users_id and insert a number of records into table2 all with the same users_id so I end up with

table1:

1,val1,val2

table2:

1,barry,...
1,bob,...
1,james,...

我认为我需要一个带有序列的触发器，以在table1中自动递增users_id并在table2中创建行.

I think I need a trigger with a sequence to auto-increment users_id in table1 and create the rows in table2.

这可能无关紧要，但是我是通过PHP脚本执行的.

It might not be relevant but I'm doing this from a PHP script.

更新

到目前为止，我已经设置了一个序列和一个触发器，以便可以INSERT进入table1并具有users_id字段自动递增:

So far I have a sequence and a trigger set up so I can INSERT into table1 and have the users_id field auto-increment:

create sequence user_seq 
start with 1 
increment by 1 
nomaxvalue;

create trigger user_trigger
before insert on table1
for each row
begin
select user_seq.nextval into :new.users_id from dual;
end;

所以现在我只需要自动插入第二张表即可.

so now I just need to automatically insert into the second table.

非常感谢.

推荐答案

在将新记录插入到table1中之后，可以使用insert语句的returning into子句返回users_id值.您也可以使用user_seq.currval来获取序列的当前值.这是一个示例(在此示例中，已经实现了一个简单的存储过程来演示insert into子句的用法.您可以根据需要实现类似的存储过程):

You can use returning into clause of the insert statement to return users_id value after a new record has been inserted into table1. Also you can use user_seq.currval to get the current value of the sequence. Here is an example (In this example a simple stored procedure has been implemented to demonstrate the usage of insert into clause. You can implement a similar stored procedure according to your requirements):

SQL> create table Tb_table_1(
  2    user_id number primary key,
  3    field_1 number
  4  );

Table created

SQL> 
SQL> create table Tb_table_2(
  2    user_id number references tb_table_1(user_id),
  3    name1 varchar2(17)
  4  );

Table created

SQL> create sequence user_seq
  2  start with 1
  3  increment by 1
  4  nomaxvalue;

Sequence created

SQL> 
SQL> create trigger user_trigger
  2  before insert on tb_table_1
  3  for each row
  4  begin
  5    select user_seq.nextval into :new.user_id from dual;
  6  end;
  7  /

Trigger created

  SQL> create or replace procedure Insert_Record
  2  is
  3    l_cur_id number;
  4  begin
  5    insert into Tb_table_1(Field_1)
  6      values(123)
  7    returning user_id into l_cur_id; -- store user_id of the new inserted record
  8    for i in 1..5                    -- in a local variable for later use  
  9    loop
 10      insert into tb_table_2(user_id, name1)  -- insert a bunch of sample data into table2 using previously stored user_id.
 11        values(l_cur_id, dbms_random.string('l', 7));
 12    end loop
 13    commit;
 14  end;
 15  /

Procedure created

SQL> select * from tb_table_1;

   USER_ID    FIELD_1
---------- ----------

SQL> select * from tb_table_2;

   USER_ID NAME1
---------- -----------------



SQL> exec insert_record;

PL/SQL procedure successfully completed

SQL> select * from tb_table_1
  2  ;

   USER_ID    FIELD_1
---------- ----------
         1        123

SQL> select * from tb_table_2;

   USER_ID NAME1
---------- -----------------
         1 jzsdbna
         1 ozbibgs
         1 btxrxcm
         1 hxwwpzc
         1 sdjbwzi

SQL> 

从Oracle 11g开始，您可以直接将序列值分配给变量:

In Oracle 11g onward you can directly assign sequence value to a variable:

:new.users_id := user_seq.nextval;

这篇关于在Oracle中触发以自动增加字段并插入关联表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！