在Oracle中触发以自动增加字段并插入关联表

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 withtable1:1,val1,val2table2: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_triggerbefore insert on table1for each rowbeginselect 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 createdSQL> SQL> create table Tb_table_2( 2 user_id number references tb_table_1(user_id), 3 name1 varchar2(17) 4 );Table createdSQL> create sequence user_seq 2 start with 1 3 increment by 1 4 nomaxvalue;Sequence createdSQL> 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 createdSQL> 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 completedSQL> select * from tb_table_1 2 ; USER_ID FIELD_1---------- ---------- 1 123SQL> select * from tb_table_2; USER_ID NAME1---------- ----------------- 1 jzsdbna 1 ozbibgs 1 btxrxcm 1 hxwwpzc 1 sdjbwziSQL> 从Oracle 11g开始，您可以直接将序列值分配给变量:In Oracle 11g onward you can directly assign sequence value to a variable::new.users_id := user_seq.nextval; 这篇关于在Oracle中触发以自动增加字段并插入关联表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！