Re: What is the Purpose of Subquery in Insert?

From: Ed Prochak <ed.prochak_at_magicinterface.com>
Date: 18 Feb 2005 11:48:35 -0800
Message-ID: <1108756115.674093.62250@z14g2000cwz.googlegroups.com>

Maybe the point is to be obscure?

Actually, it is an in-line view.

 If the PK is just the employee_id, then this is definitely obscure. It's obvious the department_id is never assigned a value (unless there is some behind the scenes trigger).

Hmm, is this some way of avoiding a trigger on the employee table?

  1 create table emp (
  2 emp_id number PRIMARY KEY,
  3 emp_nm varchar(20),
  4 dept_id number ,
  5* other varchar(10) )
SQL> / Table created.

SQL> insert into emp values (1, 'adam', 50,'first');

1 row created.

SQL> insert into emp values (2, 'brent', 30,'second');

1 row created.

SQL> insert into emp values (3, 'cary',10, NULL);

1 row created.

SQL> create or replace trigger emp_in before insert on emp   2 for each row
  3 begin
  4 :new.dept_id := 50;
  5 end;
  6 /

Trigger created.

SQL> select emp_id , emp_nm from emp
  2 where dept_id=50;

    EMP_ID EMP_NM

---------- --------------------
         1 adam

SQL> select * from emp;

    EMP_ID EMP_NM                  DEPT_ID OTHER


---------- -------------------- ---------- ----------

         1 adam                         50 first
         2 brent                        30 second
         3 cary                         10

SQL> insert into ( select  emp_id , emp_nm from emp
  2               where dept_id=50 )

  3 VALUES ( 9999, 'peter' );

1 row created.

SQL> select * from emp;

    EMP_ID EMP_NM                  DEPT_ID OTHER


---------- -------------------- ---------- ----------

         1 adam                         50 first
         2 brent                        30 second
         3 cary                         10
      9999 peter                        50

SQL> insert into ( select  emp_id , emp_nm from emp
  2               where dept_id=50 )

  3 VALUES ( 9999, 'peter' );

1 row created.

SQL> select * from emp;

    EMP_ID EMP_NM                  DEPT_ID OTHER


---------- -------------------- ---------- ----------

         1 adam                         50 first
         2 brent                        30 second
         3 cary                         10
      9999 peter                        50

SQL>insert into ( select  emp_id , emp_nm from emp
  2               where dept_id=50 )

  3 VALUES ( 9999, 'peter' );

1 row created.

SQL> select * from emp;

    EMP_ID EMP_NM                  DEPT_ID OTHER


---------- -------------------- ---------- ----------

         1 adam                         50 first
         2 brent                        30 second
         3 cary                         10
      9999 peter                        50

SQL>insert into ( select  emp_id , emp_nm from emp
  2               where dept_id=50 )

  3 VALUES ( 9999, 'peter' );

1 row created.

SQL> select * from emp;

    EMP_ID EMP_NM                  DEPT_ID OTHER


---------- -------------------- ---------- ----------

         1 adam                         50 first
         2 brent                        30 second
         3 cary                         10
      9999 peter                        50

SQL> Nope, the trigger still fires. Might be a job-security-thru-obscurity ploy? I see no reason to use that versus the simple: insert into employee VALUES(9999,'peter');

HTH,
  ed Received on Fri Feb 18 2005 - 13:48:35 CST