Home » SQL & PL/SQL » SQL & PL/SQL » What is wrong with this trigger code? (merged)
What is wrong with this trigger code? (merged) [message #378501] Tue, 30 December 2008 15:09 Go to next message
efachim
Messages: 42
Registered: July 2008
Member
Hi folks,

Just wondering whether I might be missing something here about Oracle 9i, that one of you might notice, either from the syntax perspective, or regarding a bug? The code below compiles in the database I have, but the trigger does not fire, and no data is being inserted into the tables. The trigger is owned by sys, and the insert statements are in dynamic sql because I don't want the trigger not to compile if ever the tables are deleted from the b1dev schema.

Incidentally, this same trigger works well on Oracle 10.2.1.0 and Oracle 11.1.0. Any feedback would be much appreciated.

CREATE OR REPLACE TRIGGER cc_LogOff_Trig
    BEFORE LogOff ON DATABASE
    DECLARE
       LogOff_sid   PLS_INTEGER;
       LogOff_Time  DATE := SYSDATE;
       Table_1      VARCHAR2(30) := 'CC_SESSION_EVENT_HISTORY';
       Table_2      VARCHAR2(30) := 'CC_SESSTAT_HISTORY';
       Table_Count  NUMBER;
       v_sql1       VARCHAR2(4000);
       v_sql2       VARCHAR2(4000);
    BEGIN
      SELECT sId
      INTO   LogOff_sId
      FROM   sys.v$MysTat
      WHERE  ROWNUM < 2;
      
      SELECT COUNT(* )
      INTO   Table_Count
      FROM   dba_Objects
      WHERE  Object_Name = Table_1
             AND Object_Type = 'TABLE';
      
      IF Table_Count = 1 THEN
        v_sql1 := 'INSERT INTO bdev.cc_session_event_history(sid,   serial#,   username,   osuser,   session_process_addr,   os_client_process_id,   logon_time,   type,   event,    total_waits,   total_timeouts,   time_waited_csecs,   
  average_wait_csecs,   max_wait_csecs,    logoff_timestamp) SELECT se.sid, s.serial#, s.username, s.osuser, s.paddr, s.process, s.logon_time, s.type, se.event, se.total_waits, se.total_timeouts, se.time_waited, se.average_wait, 
  se.max_wait, '''
                  ||LogOff_Time
                  ||''' FROM sys.v$session_event se, sys.v$session s WHERE se.sid = s.sid AND s.username = '''
                  ||LogIn_User
                  ||''' AND s.sid = '
                  ||LogOff_sId;
        
        EXECUTE IMMEDIATE v_sql1;
      END IF;
      
      SELECT COUNT(* )
      INTO   Table_Count
      FROM   dba_Objects
      WHERE  Object_Name = Table_2
             AND Object_Type = 'TABLE';
      
      IF Table_Count = 1 THEN
        v_sql2 := 'INSERT INTO bdev.cc_sesstat_history(username,     osuser,   sid,   serial#,   session_process_addr,   os_client_process_id,   logon_time,   statistic#,   name,   VALUE,   logoff_timestamp)   SELECT s.username,    
  s.osuser,      ss.sid,     s.serial#,     s.paddr,     s.process,     s.logon_time,     ss.statistic#,     sn.name,     ss.VALUE,     '''
                  ||LogOff_Time
                  ||'''   FROM sys.v$sesstat ss,     sys.v$statname sn,     sys.v$session s  
  WHERE ss.statistic# = sn.statistic#    AND ss.sid = s.sid    AND sn.name IN(''CPU used when call started'',   ''CPU used by this session'',   ''recursive cpu usage'',   ''parse time cpu'')    
  AND s.username = '''
                  ||Login_User
                  ||''' AND s.sid = '
                  ||LogOff_sId;
        
        EXECUTE IMMEDIATE v_sql2;
      END IF;
      
      COMMIT;
    END;


desc cc_session_event_history;
Name                           Null     Type                                                                                                                                                                                          
------------------------------ -------- ------------------------- 
SID                                     NUMBER                                                                                                                                                                                        
SERIAL#                                 NUMBER                                                                                                                                                                                        
USERNAME                                VARCHAR2(30)                                                                                                                                                                                  
OSUSER                                  VARCHAR2(30)                                                                                                                                                                                  
SESSION_PROCESS_ADDR                    RAW(0)                                                                                                                                                                                        
OS_CLIENT_PROCESS_ID                    VARCHAR2(24)                                                                                                                                                                                  
LOGON_TIME                              DATE                                                                                                                                                                                          
TYPE                                    VARCHAR2(10)                                                                                                                                                                                  
EVENT                                   VARCHAR2(64)                                                                                                                                                                                  
EVENT#                                  NUMBER                                                                                                                                                                                        
TOTAL_WAITS                             NUMBER                                                                                                                                                                                        
TOTAL_TIMEOUTS                          NUMBER                                                                                                                                                                                        
TIME_WAITED_CSECS                       NUMBER                                                                                                                                                                                        
AVERAGE_WAIT_CSECS                      NUMBER                                                                                                                                                                                        
MAX_WAIT_CSECS                          NUMBER                                                                                                                                                                                        
TIME_WAITED_MICRO                       NUMBER                                                                                                                                                                                        
LOGOFF_TIMESTAMP                        DATE                                                                                                                                                                                          

17 rows selected

desc cc_sesstat_history;
Name                           Null     Type                                                                                                                                                                                          
------------------------------ -------- ----------------
USERNAME                                VARCHAR2(30)                                                                                                                                                                                  
OSUSER                                  VARCHAR2(30)                                                                                                                                                                                  
SID                                     NUMBER                                                                                                                                                                                        
SERIAL#                                 NUMBER                                                                                                                                                                                        
SESSION_PROCESS_ADDR                    RAW(0)                                                                                                                                                                                        
OS_CLIENT_PROCESS_ID                    VARCHAR2(24)                                                                                                                                                                                  
LOGON_TIME                              DATE                                                                                                                                                                                          
STATISTIC#                              NUMBER                                                                                                                                                                                        
NAME                                    VARCHAR2(64)                                                                                                                                                                                  
VALUE                                   NUMBER                                                                                                                                                                                        
LOGOFF_TIMESTAMP                        DATE                                                                                                                                                                                          

11 rows selected

Re: What is wrong with this trigger code? [message #378507 is a reply to message #378501] Tue, 30 December 2008 15:20 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
The first thing that jumps out at me is that you are issuing a commit within a trigger with pragma autonomous_transaction.
Re: What is wrong with this trigger code? [message #378508 is a reply to message #378501] Tue, 30 December 2008 15:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
What is wrong with this trigger code?

The lines exceed 80 characters.

Regards
Michel
Re: What is wrong with this trigger code? [message #378512 is a reply to message #378507] Tue, 30 December 2008 15:31 Go to previous messageGo to next message
efachim
Messages: 42
Registered: July 2008
Member
I added the commit, because the inserts would not commit, when I tested the trigger on Oracle 11g. Are you saying I should remove the commit?
Re: What is wrong with this trigger code? [message #378515 is a reply to message #378512] Tue, 30 December 2008 15:36 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
efachim wrote on Tue, 30 December 2008 13:31
I added the commit, because the inserts would not commit, when I tested the trigger on Oracle 11g. Are you saying I should remove the commit?


No, I am saying that in earlier versions, if you are going to use commit in a trigger, you need to add "PRAGMA AUTONOMOUS_TRANSACTION;" or it will raise an error, which you may never see, because it logs out.
Re: What is wrong with this trigger code? (merged) [message #379482 is a reply to message #378501] Tue, 06 January 2009 13:41 Go to previous messageGo to next message
efachim
Messages: 42
Registered: July 2008
Member
Hi folks,

I have now taken out the 'commit' and added the 'when others then null;' exception clause, otherwise known as the stupid clause. But I am thinking there has to be a better way to do this, without having to insert into another table, which is why, I am having to use dynamic sql for my inserts above in the first place.

Would anyone have any other suggestions?

CREATE OR REPLACE TRIGGER cc_LogOff_Trig
    BEFORE LogOff ON DATABASE
    DECLARE
       LogOff_sid   PLS_INTEGER;
       LogOff_Time  DATE := SYSDATE;
       Table_1      VARCHAR2(30) := 'CC_SESSION_EVENT_HISTORY';
       Table_2      VARCHAR2(30) := 'CC_SESSTAT_HISTORY';
       Table_Count  NUMBER;
       v_sql1       VARCHAR2(4000);
       v_sql2       VARCHAR2(4000);
    BEGIN
      SELECT sId
      INTO   LogOff_sId
      FROM   sys.v$MysTat
      WHERE  ROWNUM < 2;
      
      SELECT COUNT(* )
      INTO   Table_Count
      FROM   dba_Objects
      WHERE  Object_Name = Table_1
             AND Object_Type = 'TABLE';
      
      IF Table_Count = 1 THEN
        v_sql1 := 'INSERT INTO bdev.cc_session_event_history(sid,     serial#,   username,   osuser,   
session_process_addr,   os_client_process_id, logon_time,   type,   event,    total_waits,   total_timeouts,   
time_waited_csecs,  average_wait_csecs,   max_wait_csecs,    logoff_timestamp) SELECT se.sid, s.serial#, 
s.username, s.osuser, s.paddr, s.process, s.logon_time, s.type, se.event, se.total_waits, se.total_timeouts, 
se.time_waited, se.average_wait, se.max_wait, '''
                  ||LogOff_Time
                  ||''' FROM sys.v$session_event se, sys.v$session s WHERE se.sid = s.sid AND s.username = '''
                  ||LogIn_User
                  ||''' AND s.sid = '
                  ||LogOff_sId;
        
        EXECUTE IMMEDIATE v_sql1;
      END IF;
      
      SELECT COUNT(* )
      INTO   Table_Count
      FROM   dba_Objects
      WHERE  Object_Name = Table_2
             AND Object_Type = 'TABLE';
      
      IF Table_Count = 1 THEN
        v_sql2 := 'INSERT INTO bdev.cc_sesstat_history(username,     osuser,   sid,   serial#,   
session_process_addr,   os_client_process_id, logon_time,   statistic#,   name,   VALUE,   logoff_timestamp)   
SELECT s.username, s.osuser, ss.sid, s.serial#, s.paddr, s.process,     s.logon_time,     ss.statistic#, 
sn.name,     ss.VALUE,     '''
                  ||LogOff_Time
                  ||'''   FROM sys.v$sesstat ss,     sys.v$statname sn,     sys.v$session s  
  WHERE ss.statistic# = sn.statistic#    AND ss.sid = s.sid    AND sn.name IN(''CPU used when call started'', 
''CPU used by this session'',   ''recursive cpu usage'',   ''parse time cpu'')    
  AND s.username = '''
                  ||Login_User
                  ||''' AND s.sid = '
                  ||LogOff_sId;
        
        EXECUTE IMMEDIATE v_sql2;
      END IF;
EXCEPTION
  WHEN OTHERS THEN
    NULL;      
END;

[Updated on: Tue, 06 January 2009 13:50]

Report message to a moderator

Re: What is wrong with this trigger code? (merged) [message #379496 is a reply to message #379482] Tue, 06 January 2009 16:32 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
I believe the problem is related to handling of the commit. I will use a simplified example with an after insert trigger to demonstrate.

Ordinarily, if you attempt to do an insert within a trigger and commit it, then it raises an error and no rows are inserted:

SCOTT@orcl_11g> CREATE TABLE test_tab1 (test_col NUMBER)
  2  /

Table created.

SCOTT@orcl_11g> CREATE TABLE test_tab2 (idate DATE)
  2  /

Table created.

SCOTT@orcl_11g> CREATE OR REPLACE TRIGGER test_trig
  2    AFTER INSERT ON test_tab1
  3  BEGIN
  4    INSERT INTO test_tab2 VALUES (SYSDATE);
  5    COMMIT;
  6  END;
  7  /

Trigger created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> INSERT INTO test_tab1 VALUES (1)
  2  /
INSERT INTO test_tab1 VALUES (1)
            *
ERROR at line 1:
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "SCOTT.TEST_TRIG", line 3
ORA-04088: error during execution of trigger 'SCOTT.TEST_TRIG'


SCOTT@orcl_11g> SELECT * FROM test_tab2
  2  /

no rows selected


If you add the exception when others then null clause, this works around the problem, but results in any other errors not being raised:

SCOTT@orcl_11g> TRUNCATE TABLE test_tab2
  2  /

Table truncated.

SCOTT@orcl_11g> CREATE OR REPLACE TRIGGER test_trig
  2    AFTER INSERT ON test_tab1
  3  BEGIN
  4    INSERT INTO test_tab2 VALUES (SYSDATE);
  5    COMMIT;
  6  EXCEPTION
  7    WHEN OTHERS THEN NULL;
  8  END;
  9  /

Trigger created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> INSERT INTO test_tab1 VALUES (1)
  2  /

1 row created.

SCOTT@orcl_11g> SELECT * FROM test_tab2
  2  /

IDATE
---------
06-JAN-09


The usual method for doing this properly is to use pragma autonomous_transcation:

SCOTT@orcl_11g> TRUNCATE TABLE test_tab2
  2  /

Table truncated.

SCOTT@orcl_11g> CREATE OR REPLACE TRIGGER test_trig
  2    AFTER INSERT ON test_tab1
  3  DECLARE
  4    PRAGMA AUTONOMOUS_TRANSACTION;
  5  BEGIN
  6    INSERT INTO test_tab2 VALUES (SYSDATE);
  7    COMMIT;
  8  END;
  9  /

Trigger created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> INSERT INTO test_tab1 VALUES (1)
  2  /

1 row created.

SCOTT@orcl_11g> SELECT * FROM test_tab2
  2  /

IDATE
---------
06-JAN-09


In the past when you used dynamic sql, it caused an implicit commit.
So using dynamic sql even without a commit, theoretically should have raised the same error as issuing an insert followed by a commit.
That may be what is happening in your older version.
However, in 11g (don't know about 10g), it does not seem to do that:

SCOTT@orcl_11g> TRUNCATE TABLE test_tab2
  2  /

Table truncated.

SCOTT@orcl_11g> CREATE OR REPLACE TRIGGER test_trig
  2    AFTER INSERT ON test_tab1
  3  BEGIN
  4    EXECUTE IMMEDIATE 'INSERT INTO test_tab2 VALUES (SYSDATE)';
  5  END;
  6  /

Trigger created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> INSERT INTO test_tab1 VALUES (1)
  2  /

1 row created.

SCOTT@orcl_11g> SELECT * FROM test_tab2
  2  /

IDATE
---------
06-JAN-09

SCOTT@orcl_11g> ROLLBACK
  2  /

Rollback complete.

SCOTT@orcl_11g> SELECT * FROM test_tab2
  2  /

no rows selected


And attempting to add pragma autonomouos_transaction with dynamic sql causes another problem:

SCOTT@orcl_11g> TRUNCATE TABLE test_tab2
  2  /

Table truncated.

SCOTT@orcl_11g> CREATE OR REPLACE TRIGGER test_trig
  2    AFTER INSERT ON test_tab1
  3  DECLARE
  4    PRAGMA AUTONOMOUS_TRANSACTION;
  5  BEGIN
  6    EXECUTE IMMEDIATE 'INSERT INTO test_tab2 VALUES (SYSDATE)';
  7  END;
  8  /

Trigger created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> INSERT INTO test_tab1 VALUES (1)
  2  /
INSERT INTO test_tab1 VALUES (1)
            *
ERROR at line 1:
ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at "SCOTT.TEST_TRIG", line 5
ORA-04088: error during execution of trigger 'SCOTT.TEST_TRIG'


SCOTT@orcl_11g> SELECT * FROM test_tab2
  2  /

no rows selected


You might consider adding your dynamic insert to the exceptions clause, along with whatever else you want to do if there is an exception:

SCOTT@orcl_11g> TRUNCATE TABLE test_tab2
  2  /

Table truncated.

SCOTT@orcl_11g> CREATE OR REPLACE TRIGGER test_trig
  2    AFTER INSERT ON test_tab1
  3  BEGIN
  4    EXECUTE IMMEDIATE 'INSERT INTO test_tab2 VALUES (SYSDATE)';
  5  EXCEPTION
  6    WHEN OTHERS THEN
  7  	 EXECUTE IMMEDIATE 'INSERT INTO test_tab2 VALUES (SYSDATE)';
  8  END;
  9  /

Trigger created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> INSERT INTO test_tab1 VALUES (1)
  2  /

1 row created.

SCOTT@orcl_11g> SELECT * FROM test_tab2
  2  /

IDATE
---------
06-JAN-09


You should try doing some testing on your version by temporarily changing the trigger to an insert trigger and removing the exception clause and testing as I have done.
That allows you to see what is happening, that would otherwise be obscured by the exception clause and the logout.
That way you can tell if it is objecting to an implicit commit by the dynamic sql or something else in the code.
If it is something else in the code, then you need to know about it, so that you can fix it.





Re: What is wrong with this trigger code? (merged) [message #379508 is a reply to message #379496] Tue, 06 January 2009 19:42 Go to previous messageGo to next message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
Barbara Boehmer wrote on Tue, 06 January 2009 16:32

In the past when you used dynamic sql, it caused an implicit commit.
So using dynamic sql even without a commit, theoretically should have raised the same error as issuing an insert followed by a commit.
That may be what is happening in your older version.
However, in 11g (don't know about 10g), it does not seem to do that:

SCOTT@orcl_11g> TRUNCATE TABLE test_tab2
  2  /

Table truncated.

SCOTT@orcl_11g> CREATE OR REPLACE TRIGGER test_trig
  2    AFTER INSERT ON test_tab1
  3  BEGIN
  4    EXECUTE IMMEDIATE 'INSERT INTO test_tab2 VALUES (SYSDATE)';
  5  END;
  6  /

Trigger created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> INSERT INTO test_tab1 VALUES (1)
  2  /

1 row created.

SCOTT@orcl_11g> SELECT * FROM test_tab2
  2  /

IDATE
---------
06-JAN-09

SCOTT@orcl_11g> ROLLBACK
  2  /

Rollback complete.

SCOTT@orcl_11g> SELECT * FROM test_tab2
  2  /

no rows selected





Tried it in Oracle Database 10g 10.2.0.1.0, but unfortunately...

ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "MY_SHEMA.TEST_TRIG", line 3
ORA-04088: error during execution of trigger 'MY_SHEMA.TEST_TRIG'


Now, thats interesting to know..

Regards,
Wilbert

[Updated on: Tue, 06 January 2009 19:44]

Report message to a moderator

Re: What is wrong with this trigger code? (merged) [message #379536 is a reply to message #379496] Tue, 06 January 2009 23:58 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Excellent job Barbara mam have done!!

But this raises one question about

SCOTT@orcl_11g> CREATE OR REPLACE TRIGGER test_trig
  2    AFTER INSERT ON test_tab1
  3  BEGIN
  4    INSERT INTO test_tab2 VALUES (SYSDATE);
  5    COMMIT;
  6  EXCEPTION
  7    WHEN OTHERS THEN NULL;
  8  END;
  9  /

Trigger created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.


here, when exception occurs, it moves to exception block preventing execution of insert statement.
And then also it shows record is inserted in the table.
How is it possible?

Or it only prevents execution of COMMIT statement, and that insert statement for TAB2 remains uncommited?

regards,
Delna
Re: What is wrong with this trigger code? (merged) [message #379538 is a reply to message #378501] Wed, 07 January 2009 00:02 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Sorry for asking that stupid question. Idea

I just checked what I asked.

Exception prevents only execution of COMMIT. Inserted Record in the table remains unsaved.

regards,
Delna
Re: What is wrong with this trigger code? (merged) [message #379696 is a reply to message #379508] Wed, 07 January 2009 12:51 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
I was testing some examples in the 10g documentation and along the way I discovered that although using pragma autonomous_transaction with dynamic sql in a trigger in the code below produces an error:

SCOTT@orcl_11g> CREATE TABLE test_tab1 (test_col NUMBER)
  2  /

Table created.

SCOTT@orcl_11g> CREATE TABLE test_tab2 (idate DATE)
  2  /

Table created.

SCOTT@orcl_11g> CREATE OR REPLACE TRIGGER test_trig
  2    AFTER INSERT ON test_tab1
  3  DECLARE
  4    PRAGMA AUTONOMOUS_TRANSACTION;
  5  BEGIN
  6    EXECUTE IMMEDIATE 'INSERT INTO test_tab2 VALUES (SYSDATE)';
  7  END;
  8  /

Trigger created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> INSERT INTO test_tab1 VALUES (1)
  2  /
INSERT INTO test_tab1 VALUES (1)
            *
ERROR at line 1:
ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at "SCOTT.TEST_TRIG", line 5
ORA-04088: error during execution of trigger 'SCOTT.TEST_TRIG'


SCOTT@orcl_11g> SELECT * FROM test_tab2
  2  /

no rows selected

SCOTT@orcl_11g> TRUNCATE TABLE test_tab2
  2  /

Table truncated.


if you add commit to the same code, it does not produce an error:

SCOTT@orcl_11g> CREATE OR REPLACE TRIGGER test_trig
  2    AFTER INSERT ON test_tab1
  3  DECLARE
  4    PRAGMA AUTONOMOUS_TRANSACTION;
  5  BEGIN
  6    EXECUTE IMMEDIATE 'INSERT INTO test_tab2 VALUES (SYSDATE)';
  7    COMMIT;
  8  END;
  9  /

Trigger created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> INSERT INTO test_tab1 VALUES (1)
  2  /

1 row created.

SCOTT@orcl_11g> SELECT * FROM test_tab2
  2  /

IDATE
---------
07-JAN-09

SCOTT@orcl_11g> 


Similarly using ddl in dynamic sql does not produce an error without the commit:

SCOTT@orcl_11g> TRUNCATE TABLE test_tab2
  2  /

Table truncated.

SCOTT@orcl_11g> CREATE OR REPLACE TRIGGER test_trig
  2    AFTER INSERT ON test_tab1
  3  DECLARE
  4    PRAGMA AUTONOMOUS_TRANSACTION;
  5  BEGIN
  6    EXECUTE IMMEDIATE 'create table test_tab3 (test_col date)';
  7  END;
  8  /

Trigger created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> INSERT INTO test_tab1 VALUES (1)
  2  /

1 row created.


I now find myself trying to remember if I was just thinking that dynamic sql issues an implicit commit, because the only time I used it was for ddl, because you can't issue ddl from pl/sql without dynamic sql, and ddl definitely issues an implicit commit.

It appears that if you use pragma autonomous_transaction, it expects a commit, and throws an error without it:

SCOTT@orcl_11g> CREATE OR REPLACE TRIGGER test_trig
  2    AFTER INSERT ON test_tab1
  3  DECLARE
  4    PRAGMA AUTONOMOUS_TRANSACTION;
  5  BEGIN
  6    INSERT INTO test_tab2 VALUES (SYSDATE);
  7  END;
  8  /

Trigger created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> INSERT INTO test_tab1 VALUES (1)
  2  /
INSERT INTO test_tab1 VALUES (1)
            *
ERROR at line 1:
ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at "SCOTT.TEST_TRIG", line 5
ORA-04088: error during execution of trigger 'SCOTT.TEST_TRIG'


SCOTT@orcl_11g> SELECT * FROM test_tab2
  2  /

no rows selected


I would be interested if anybody out there has multiple versions and can post any copy and pastes of identical code handled differently in different versions.
Please note that I am requesting actual complete copies and pastes, so there is no doubt about what you ran.






[Updated on: Wed, 07 January 2009 12:53]

Report message to a moderator

Re: What is wrong with this trigger code? (merged) [message #379749 is a reply to message #378501] Wed, 07 January 2009 19:14 Go to previous messageGo to next message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
Hmmm.. Would like to try it in 11g and earlier versions but I can only do testing in 10g for now.. Confirmed!

Barbara's efforts in this thread needs to be recognized!
./fa/2115/0/ Thumbs up!

Regards,
Wilbert
Re: What is wrong with this trigger code? (merged) [message #379936 is a reply to message #379749] Thu, 08 January 2009 09:35 Go to previous message
efachim
Messages: 42
Registered: July 2008
Member
Lol Wilbert,

I am actually working on this, and on various Oracle versions...

rgds, E
Previous Topic: SQL failing when the language is NL
Next Topic: Creating Trigger with Dynamic sql when ever table structure changes
Goto Forum:
  


Current Time: Fri Dec 09 21:13:11 CST 2016

Total time taken to generate the page: 0.14092 seconds