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  |
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 #378515 is a reply to message #378512] |
Tue, 30 December 2008 15:36  |
 |
Barbara Boehmer
Messages: 9106
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 |
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 |
|
Barbara Boehmer
Messages: 9106
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 |
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 #379696 is a reply to message #379508] |
Wed, 07 January 2009 12:51 |
|
Barbara Boehmer
Messages: 9106
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 |
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Aug 30 05:51:14 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
