Re: Seeing new transaction data while a trigger is firing

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 02 Jul 1998 19:06:07 GMT
Message-ID: <359dd96e.21256314@192.86.155.100>

A copy of this was sent to mtayag_at_my-dejanews.com (if that email address didn't require changing) On Thu, 02 Jul 1998 17:20:30 GMT, you wrote:

>Consider the following table
>
>TEMP_TABLE
>
>COL_1 COL_2
>====== ======
>X 1
>X 2
>X 3
>
>Now consider the following transaction:
><START TRANSACTION>
> insert into TEMP_TABLE (COL_1, COL_2) VALUES (Y, 2);
> insert into TEMP_TABLE (COL_1, COL_2) VALUES (Y, 4);
> insert into TEMP_TABLE (COL_1, COL_2) VALUES (Y, 6);
> insert into TEMP_TABLE_2 (COL_1) VALUES ('DONE');
> commit;
><END TRANSACTION>
>
>An application (independent of the trigger) is performing the transaction into
>TEMP_TABLE.
>I have a trigger on TEMP_TABLE_2 that is defined as the following:
>
>CREATE OR REPLACE TRIGGER trgTEMP_TABLE_2
>after insert on TEMP_TABLE_2 for each row
>DECLARE
> SumOfCOL_2 number;
>BEGIN
> select sum(COL_2) into SumOfCOL_2 from TEMP_TABLE where COL_1 = 'Y';
>END;
>
>The problem is that the trigger does not recognize any of the values being
>inserted as part of the transaction, even though it is an "after insert"
>trigger.
>
>Note: I am using Oracle 7.3.
>
>Anyone have any ideas as to how I can get the trigger to see these values?
>If you have an idea, please respond by e-mail to mtayag_at_obviousmrp.com
>Thanks in advance for your help.
>
>Michael Tayag
>
>

please post an example of the above -- it doesn't work the way you describe. Here is an example (yours) played out in Oracle7.3.3 on solaris that shows that yes, the trigger does see the values:

$ sqlplus tkyte/tkyte

SQL*Plus: Release 3.3.3.0.0 - Production on Thu Jul 2 14:44:07 1998 Copyright (c) Oracle Corporation 1979, 1996. All rights reserved.

Connected to:
Oracle7 Server Release 7.3.3.0.0 - Production Release With the distributed, replication, parallel query and Spatial Data options PL/SQL Release 2.3.3.0.0 - Production

SQL> @test
Table dropped.
Table dropped.

SQL> create table temp_table ( col_1 char(1), col_2 int ); Table created.

SQL> create table temp_table_2 ( col_1 varchar2(10) ); Table created.

SQL> create or replace trigger trgTEMP_TABLE_2   2 after insert on temp_table_2 for each row   3 declare
  4 sumOfCol_2 number;
  5 begin
  6 select sum(col_2) into sumOfCol_2 from temp_table where col_1 = 'Y';
  7 dbms_output.put_line( 'sum of col2 = ' || to_char(sumOfCol_2) );   8 end;
  9 /
Trigger created.

SQL> set serveroutput on

SQL> insert into temp_table ( col_1, col_2 ) values ( 'Y', 2 ); 1 row created.

SQL> insert into temp_table ( col_1, col_2 ) values ( 'Y', 4 ); 1 row created.

SQL> insert into temp_table ( col_1, col_2 ) values ( 'Y', 6 ); 1 row created.

SQL> insert into temp_table_2 ( col_1 ) values ( 'DONE' ); sum of col2 = 12

1 row created.

SQL> commit;
Commit complete.

So, after the insert into temp_table_2, we definitely see that trigger printing out "col2 = 12". Given that the table was empty before any of the inserts -- it must be that the trigger read the values just inserted....  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Jul 02 1998 - 14:06:07 CDT