Q
Problem solve Get help with specific problems with your technologies, process and projects.

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 bob
Just 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 bob
There it is!!!

For More Information

  • What do you think about this answer? E-mail the editors at editor@searchDatabase.com 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.

Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close