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.

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.

ORA9I SQL> create view test1_view as select name from
test1;

View created.

ORA9I SQL> insert into test1_view values ('bob');

1 row created.

ORA9I SQL> select * from test1;

        ID NAME
---------- --------------------
        52 bob

ORA9I SQL> insert into test1_view values ('bob');

1 row created.

ORA9I SQL> select * from test1;

        ID NAME
---------- --------------------
        52 bob
        53 bob

For More Information

• What do you think about this answer? E-mail the editors at [email protected]chDatabase.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.