Home » SQL & PL/SQL » SQL & PL/SQL » how to pass a current transaction values in statement level triggers?
how to pass a current transaction values in statement level triggers? [message #251604] Sun, 15 July 2007 07:35 Go to next message
fazal_haq2001
Messages: 35
Registered: July 2006
Location: India
Member
Hi,
how can i pass current values inside statement level triggers...
in can pass the values using NEW & OLD in row level triggers..

in statement level triggers it is not allowed..So how can i process with the current transaction values of that particular statement..

Regards,
Faz..
Re: how to pass a current transaction values in statement level triggers? [message #251605 is a reply to message #251604] Sun, 15 July 2007 08:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Package variables.
2/ dbms_application_info.
3/ context.

Regards
Michel
Re: how to pass a current transaction values in statement level triggers? [message #251609 is a reply to message #251605] Sun, 15 July 2007 08:35 Go to previous messageGo to next message
fazal_haq2001
Messages: 35
Registered: July 2006
Location: India
Member
thanks for ur valuable reply..can u give one example that how to use that in trigger...

Regards,
Faz...
Re: how to pass a current transaction values in statement level triggers? [message #251610 is a reply to message #251609] Sun, 15 July 2007 08:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First read, then I will help you if you can't.
Post what you try.

Btw, with what you posted noone can accurately help you.
You have to first give why you want to "pass" parameters to the trigger. What is the need? What do you try to achieve?

Regards
Michel
Re: how to pass a current transaction values in statement level triggers? [message #251613 is a reply to message #251610] Sun, 15 July 2007 09:01 Go to previous messageGo to next message
fazal_haq2001
Messages: 35
Registered: July 2006
Location: India
Member
Ok, i m very sorry...

I ve one transaction table..The data get inserted in bulk for every transaction..
I want to trace the particular transaction when that bulk of records fall into some category(for ex. inserted more than required no. of rows)..
So i created one log table..I m trying to insert that particular data to that table through after insert statement level trigger.for that i want to get the particular transaction key values such as (student_id etc)..


Regards,
Faz..
Re: how to pass a current transaction values in statement level triggers? [message #251614 is a reply to message #251613] Sun, 15 July 2007 09:21 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create table t (id integer);

Table created.

SQL> create table log(id varchar2(64), mystamp timestamp default systimestamp);

Table created.

SQL> create or replace trigger mytrg after insert on t
  2  declare
  3    info varchar2(64);
  4  begin
  5    dbms_application_info.read_client_info (info);
  6    insert into log (id) values (info);
  7  end;
  8  /

Trigger created.

SQL> exec dbms_application_info.set_client_info('first bulk');

PL/SQL procedure successfully completed.

SQL> insert into t select object_id from all_objects where rownum<=10;

10 rows created.

SQL> exec dbms_application_info.set_client_info('second bulk');

PL/SQL procedure successfully completed.

SQL> insert into t select object_id from all_objects where rownum<=10;

10 rows created.

SQL> col id format a20
SQL> col mystamp format a30
SQL> select * from log;
ID                   MYSTAMP
-------------------- ------------------------------
first bulk           15/07/2007 16:18:56.640
second bulk          15/07/2007 16:18:56.671

2 rows selected.

This ONE way maybe not the most efficient for your case.
Work on it and on the other ones.

Regards
Michel
Previous Topic: How to Retrieve transaction time in MilliSeconds
Next Topic: Build Dynamic Table Name to use in Cursor Definition
Goto Forum:
  


Current Time: Sun Dec 04 14:23:36 CST 2016

Total time taken to generate the page: 0.11407 seconds