-
Getting the triggering statement inside a trigger
Is there a way to get the SQL statement which fired the trigger, inside the trigger block ?
Say, I issue the following statement,
-----------------------------------------------
SQL> update scott.emp set sal = 0 ; -- Statement which fires trigger
SQL> commit;
Which fires the the following trigger,
----------------------------------------------
create or replace trigger scott.test_trigger
before update on scott.emp
begin
/*Code to store the SQL statement which fired the trigger */
insert into EMP_UPDATE_SQLS values ( **TRIGGERING_SQL_STMT** );
end;
Result expected:
---------------------
A row in EMP_UPDATE_SQLS , with the sql statement that fired the trigger, ( a row with the value 'update scott.emp set sal = 0' ).
Is it possible ???
-
Re: Getting the triggering statement inside a trigger
Originally posted by ramesh_1600
Is there a way to get the SQL statement which fired the trigger, inside the trigger block ?
Say, I issue the following statement,
-----------------------------------------------
SQL> update scott.emp set sal = 0 ; -- Statement which fires trigger
SQL> commit;
Which fires the the following trigger,
----------------------------------------------
create or replace trigger scott.test_trigger
before update on scott.emp
begin
/*Code to store the SQL statement which fired the trigger */
insert into EMP_UPDATE_SQLS values ( **TRIGGERING_SQL_STMT** );
end;
Result expected:
---------------------
A row in EMP_UPDATE_SQLS , with the sql statement that fired the trigger, ( a row with the value 'update scott.emp set sal = 0' ).
Is it possible ???
Yes.
If you compare the fields between the :OLD and :NEW meta rows. the columns that are different are the ones that changed, assuming an update statement.
-
friends,
he is asking for the DML statement, not the identifiers of fetching the OLD and the NEW value.
ramesh correct me if i am wrong.
Regards,
Surajit K Mitra
-
Looks like you are trying to acheive the Oracle provided LOGMINER utility.
I would recommend looking into LOGMINER, to get all the statements that were issued against a particular table.
-
Code:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
SQL> CREATE OR REPLACE FUNCTION cur_sql_txt
2 RETURN CLOB
3 AS
4 v_cnt BINARY_INTEGER;
5 v_sql ORA_NAME_LIST_T;
6 v_rtn CLOB;
7 BEGIN
8 v_cnt := ora_sql_txt (v_sql);
9 FOR l_bit IN 1..v_cnt LOOP
10 v_rtn := v_rtn || RTRIM (v_sql (l_bit), CHR (0));
11 END LOOP;
12 RETURN RTRIM (v_rtn, CHR (10)) || ';';
13 END;
14 /
Function created.
SQL> CREATE OR REPLACE TRIGGER trigger_name
2 BEFORE UPDATE ON emp
3 FOR EACH ROW
4 BEGIN
5 DBMS_OUTPUT.PUT_LINE (cur_sql_txt);
6 END;
7 /
Trigger created.
SQL> SET SERVEROUTPUT ON;
SQL> UPDATE emp
2 SET empno = empno,
3 ename = ename
4 WHERE ROWNUM = 1;
UPDATE emp
SET empno = empno,
ename = ename
WHERE ROWNUM =
:"SYS_B_0";
1 row updated.
SQL>
-
Thanks for all your posts.
Settled with solution which is similar to the one that Padders had posted. I was trying to use the v$sqlarea, to get the current sql statement ( most probably the statement which fires the trigger ), but Padders' solution was smarter.
Thanks all.
regards,
Ramesh.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|