Subqueries in Inserts in Triggers

From: Tony Keller <EUS.EUSAJK_at_MEMOUSA.ERICSSON.SE>
Date: 1997/03/05
Message-ID: <331DAEE7.656F@MEMOUSA.ERICSSON.SE>#1/1

I'm new to Oracle but have been around the RDBMS world for a while so my question is more syntax than semantics, I think. Anyway here's the scoop:

  Oracle7 Server Release 7.1.4.1.10
  SQL*Plus Release 3.1.3.5.1
  I want the creation of a summary table to occur within a trigger, the   error occurs when I use subqueries in my insert statement but only in the
  trigger if I run just the insert statement in SQL*Plus it runs fine.

  And here's the problem:

create or replace trigger tr_the_load
  after update of second_is_loaded on the_load   for each row
  when (new.first_is_loaded = 'Y' and new.second_is_loaded = 'Y') BEGIN   insert into the_summary

    (select sq1.this,
            sq1.that,
            sq1.the_other_thing,
            to_char(sq2.a_date,'YYYYMM'),
            sq2.not_a_date,
            sum(sq2.totals)
     from (select t1.this this,
                  decode(t1.a_code, 'CODE1', '1',
                                    'CODE2', '2',
                                             '0') that,
                  t1.the_other_thing the_other_thing
           from table1 t1
           where not exists (select 1
                             from table2 t2
                             where t1.this = t2.this)) sq1,
         (select a_date,
                 not_a_date,
                 count(*) totals,
          from table3
          where yada_yada_yada
          group by a_date,
                   not_a_date) sq2

    where sq1.this = sq2.not_a_date

    group by sq1.this,
             sq1.that,
             sq1.the_other_thing,
             to_char(sq2.a_date,'YYYYMM'),
             sq2.not_a_date);

END; and the error I get from SQL*Plus is:

create or replace trigger tr_the_load
*
ERROR at line 1:

ORA-06550: line 13, column 11:
PLS-00103: Encountered the symbol "(" when expecting one of the
following:

a PL/SQL variable or double-quoted string an expanded name
an expanded name link
etc.
Resuming parse at line 53, column 28.

TIA Received on Wed Mar 05 1997 - 00:00:00 CST