Home » SQL & PL/SQL » SQL & PL/SQL » URGENT: To select newly entered values in Trigger
URGENT: To select newly entered values in Trigger [message #37135] Sun, 20 January 2002 08:58 Go to next message
Parimal Desai
Messages: 2
Registered: January 2002
Junior Member
Hello Everyone,
I have an AFTER update or insert or delete trigger on a table: e.g. ACCOUNT (Acct_id, begin_dt, end_dt)
The trigger in turn calls a procedure that has a Cursor -
select acct_id, begin_dt, end_dt from ACCOUNT;

Problem is - The cursor does not select the NEW values, it selects the old ones. So if there are no rows and if one row is inserted, still the cursor won't select any rows. Similarly for delete and update it would perform the select query keeping the old values in mind.
IS THERE A WAY TO COMMIT VALUES OF THE MAIN TABLE (ACCOUNT)? I tried Autonomous Transaction but it does not help. I would really appreciate a quick response,
Thanks in advance,
Parimal
Re: URGENT: To select newly entered values in Trigger [message #37139 is a reply to message #37135] Mon, 21 January 2002 00:21 Go to previous messageGo to next message
pratap kumar tripathy
Messages: 660
Registered: January 2002
Senior Member
Hi,

i could not find any problem. here is how i have tested.

SQL> desc my_table
Name Null? Type
------------------------------- -------- ----
COL1 NUMBER
COL2 NUMBER

SQL> desc temp
Name Null? Type
------------------------------- -------- ----
N NUMBER

SQL> ed
Wrote file afiedt.buf

1 create or replace trigger tr_my_table
2 after insert
3 on my_table
4 declare
5 n number :=0;
6 begin
7 select count(*) into n from my_table;
8 insert into temp values(n);
9* end;
SQL> insert into my_table values(1,2);

1 row created.

SQL> select * from temp;

N
---------
1

SQL> insert into my_table values(1,2);

1 row created.

SQL> select * from temp;

N
---------
1
2

cheers
pratap
Re: URGENT: To select newly entered values in Trigger [message #37141 is a reply to message #37139] Mon, 21 January 2002 02:51 Go to previous message
Parimal Desai
Messages: 2
Registered: January 2002
Junior Member
Thanks Pratap...Actually I didn't mention that it was a Row Level Trigger. Last night Suresh Vemulapalli wrote to me directly and explained to me that you can't select rows from table on which row level trigger is firing ( this result in mutating table error).
Unless there is any workaround, I guess I will have to write a statement level trigger and overcome this issue.
thanks for your reply,
Parimal
Previous Topic: Is this correct?
Next Topic: Two-phase commit
Goto Forum:
  


Current Time: Fri Jul 23 10:19:08 CDT 2021