Inserting whole rows into tables without denoting column names
In Oracle 8i, I want to insert a whole row using "INSERT INTO TABLE VALUES(x,x,x)". But, a "Before Insert" trigger creates a new (sequential) primary key. My question is, what value should I use for the primary key on the insert or do I really need to use a "INSERT INTO TABLE (COl1,COL2,COL3) VALUES (x,x,x);" instead? I'm trying to avoid the latter.
You'll either need to do one of two things. Either denote the column names or "spoof" the system by employing a workaround. Since the first option is unacceptable, let's see how you can get around it!
First, I'll create a table and a sequence.
ORA9I SQL> create table test1 (id number, name varchar2(20)); Table created. ORA9I SQL> create sequence test1_seq; Sequence created.Then, I'll create a trigger for this table.
ORA9I SQL> create trigger test1_trig 2 before insert on test1 for each row 3 declare 4 new_id number; 5 begin 6 select test1_seq.nextval into new_id from dual; 7 :new.id := new_id; 8 end; 9 / Trigger created.Now, I'll create a view with only the NAME column of that table. This leaves out the ID column of the table.
ORA9I SQL> create view test1_view as select name from test1; View created.Instead of inserting directly into the table, insert into the view instead!!!
ORA9I SQL> insert into test1_view values ('bob'); 1 row created.Checking the table, we can see that a sequence value was used here.
ORA9I SQL> select * from test1; ID NAME ---------- -------------------- 52 bobJust to double check, let's insert one more row to verify the sequence number is incrementing.
ORA9I SQL> insert into test1_view values ('bob'); 1 row created. ORA9I SQL> select * from test1; ID NAME ---------- -------------------- 52 bob 53 bobThere it is!!!
For More Information
- What do you think about this answer? E-mail the editors at [email protected] with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.