Skip to Main Content
  • Questions

  • before & after triggers and jre version

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, sushant.

Asked: September 09, 2001 - 1:29 pm UTC

Last updated: May 10, 2006 - 7:26 am UTC

Version: 8.1.5

Viewed 1000+ times

You Asked


what is actual difference between before and after triggers
is there anything substantial which can be acheived by a before
trigger which cannot be done by after triggers or viceversa


how do i know which jre version is running in my database(is there a command to
find out)


normally if i want to delete a row from the primary key table
and a corresponding row should be deleted from the foreign key table
we use the on delete cascade option while creating the foreign key table
but suppose if we dont use the on delete cascade option while creating the
table then how do we acheive it along with a normal delete statement
on a row from the primary key table

and Tom said...

For the before/after trigger:

Well, a before trigger can prevent the work from ever taking place -- an AFTER trigger will have allowed the work to happen. So, if you were using a statement trigger to do additional privilege checking -- it would be infinitely more performant in a BEFORE trigger, thereby preventing the work from ever happening (less wasted cycles processing a statement that will be rolled back which itself is expensive).

A before row level trigger can modify the :new values, an after row trigger cannot.


For the on delete cascade:

If you did not have the on delete cascade, you will have to explicitly delete from the child tables PRIOR to deleting from the parent table.

and lastly on the jre version, this information is available in the system properties:


ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace and resolve java source named "ver"
2 as
3 public class util
4 {
5 public static void prop()
6 {
7 System.getProperties().list(System.out);
8 }
9 public static String ver()
10 {
11 return System.getProperty("java.version", "unknown");
12 }
13 }
14 /

Java created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace procedure jdk_props as language java name 'util.prop()';
2 /

Procedure created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace function jdk_ver return varchar2 as language java name
2 'util.ver() return java.lang.String';
3 /

Function created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> begin
2 dbms_java.grant_permission( user,
3 'java.util.PropertyPermission',
4 '*',
5 'read,write' );
6 commit;
7 end;
8 /

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> set serveroutput on size 1000000
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec dbms_java.set_output(1000000)

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec jdk_props
-- listing properties --
java.specification.name=Java Platform API Specification
java.version=1.2.1
oracle.aurora.mts.INIT=oracle.aurora.mts.rdbms.INIT
user.timezone=EST
java.specification.version=1.2
java.vm.vendor=Oracle Corporation
java.vm.specification.version=1.0
user.home=
java.naming.factory.initial=oracle.aurora.namespace.InitialContex...
os.arch=sparc
java.vendor.url=</code> http://www.oracle.com/java/ <code>
file.encoding.pkg=sun.io
user.region=US
java.home=/export/home/ora817/javavm/
java.class.path=
line.separator=
java.io.tmpdir=/var/tmp/
jdbc.drivers=oracle.jdbc.driver.OracleDriver
os.name=Solaris
java.vendor=Oracle Corporation
oracle.jserver.version=8.1.7
oracle.server.version=8.1.7
java.library.path=/export/home/ora817/lib
java.vm.specification.vendor=Sun Microsystems Inc.
oracle.aurora.namespace.INIT=oracle.aurora.namespace.rdbms.INIT
oracle.aurora.rdbms.SID=ora817dev
file.encoding=ISO8859_1
oracle.aurora.mts.session.INIT=oracle.aurora.mts.session.rdbms.INIT
java.specification.vendor=Sun Microsystems Inc.
oracle.aurora.vm.environment.name=rdbms
user.name=
user.language=en
java.vm.name=JServer VM
java.vm.specification.name=Java Virtual Machine Specification
java.class.version=46.0
oracle.aurora.rdbms.oracle_home=/export/home/ora817/
sun.boot.library.path=/export/home/ora817/lib:/export/home/...
sqlj.runtime=sqlj.framework.ide.aurora.rdbms.Oracl...
java.naming.factory.url.pkgs=oracle.aurora.ejb.jndi:com.sun.jndi.u...
java.protocol.handler.pkgs=oracle.aurora.rdbms.url
os.version=5.7
java.vm.version=1.2.1
java.compiler=
path.separator=:
file.separator=/
user.dir=

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select jdk_ver from dual;

JDK_VER
--------------
1.2.1



Rating

  (27 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Very Informative post.

Yogeeraj, September 20, 2002 - 7:13 am UTC

Hi,

Is the JRE version in the database related to the version of JRE we use in our JDeveloper?

will there be any issues if i use latest version of JDeveloper for application development with our Oracle 8.1.7.4 database?

Thank you for a reply.

Best Regards
Yogeeraj

Tom Kyte
September 20, 2002 - 8:37 am UTC

No, the jre in the database is the jre that was in widespread use when the database was released.

the jre in jdev is the jre that was in widespread use when jdev was released.



If you are planning on putting the java IN the database (eg: java stored procedures), then you might find there are features available in java as seen by jdeveloper that are not available in the jre in the database.

When should one use Before and After trigger

Sami, January 18, 2004 - 1:30 am UTC

Dear Tom,
Hope you are back to home.

I understand that AFTER trigger has some limitations.
Otherthan that if my operation is doable in both BEFORE and AFTER trigger which one I should prefer?

<Asktom>
o Well, a before trigger can prevent the work from ever taking place -- an AFTER trigger will have allowed the work to happen. So, if you were using a statement trigger to do additional privilege checking -- it would be infinitely more performant in a BEFORE trigger, thereby preventing the work from ever happening (less wasted cycles processing a statement that will be rolled back which itself is expensive).

o A before row level trigger can modify the :new values, an after row trigger cannot.
</Asktom>

<Document>
"Oracle9 i Application Developer’s Guide - Fundamentals"

Note: AFTER row triggers are slightly more efficient than BEFORE row triggers. With BEFORE row triggers, affected data blocks must be read (logical read, not physical read) once for the trigger and then again for the triggering statement. Alternatively, with AFTER row triggers, the data blocks must be read only once for both the triggering statement and the trigger.
</Document>

Asktom is more favor of BEFORE trigger and DOCUMENT is more favor of AFTER trigger? After reading both,I am bit confused about BEFORE and AFTER trigger.

Could you please enlighten me when should one go for BEFORE trigger and AFTER trigger.

Thanks Tom.

Tom Kyte
January 18, 2004 - 1:11 pm UTC 

well, a before trigger has limitations too -- let's compare a before to an after ROW trigger:

a) before for each row -- can modify the values HOWEVER, a before for each row trigger cannot see what value will actually be placed into the table (hence, never use a before for each row trigger to verify values!!! it may not be seeing the final value)

b) after for each row -- cannot modify the values HOWEVER, an after for each row trigger sees the value of the column(s) that will actually be inserted into the table.


Additionally -- you'll find an AFTER trigger to cause less redo to be generated.

ops$tkyte@ORA9IR2> create table t ( x )
  2  as
  3  select rownum from all_objects;
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace trigger t_trigger before update on t for each row
  2  begin
  3          if ( :new.x is null )
  4          then
  5                  null;
  6          end if;
  7  end;
  8  /
 
Trigger created.
 
ops$tkyte@ORA9IR2> set autotrace traceonly statistics;
ops$tkyte@ORA9IR2> update t set x = x;
 
30614 rows updated.
 
 
Statistics
----------------------------------------------------------
         35  recursive calls
      62505  db block gets
         73  consistent gets
         47  physical reads
   13512980  redo size
        793  bytes sent via SQL*Net to client
        787  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      30614  rows processed
 
ops$tkyte@ORA9IR2> drop trigger t_trigger;
 
Trigger dropped.
 
ops$tkyte@ORA9IR2> create or replace trigger t_trigger AFTER update on t for each row
  2  begin
  3          if ( :new.x is null )
  4          then
  5                  null;
  6          end if;
  7  end;
  8  /
 
Trigger created.
 
ops$tkyte@ORA9IR2> update t set x = x;
 
30614 rows updated.
 
 
Statistics
----------------------------------------------------------
         48  recursive calls
      31346  db block gets
         68  consistent gets
          0  physical reads
    7494192  redo size
        793  bytes sent via SQL*Net to client
        787  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      30614  rows processed
 
ops$tkyte@ORA9IR2> set autotrace off


<b>many times -- i find that the statement:

Otherthan that if my  operation is doable in both BEFORE and AFTER trigger which 
one I should prefer?

is actually made "by mistake"</b>  You cannot validate (reliably) values in a BEFORE trigger -- since they are not stable (there could be other before triggers and triggers of the same type fire in whatever order WE feel like firing them so -- you could have your validate happen AND THEN another trigger comes and changes the value to something that would have failed your validation).


Use an after trigger when you do not need to modify the values.  Use a before trigger to modify values.  Use an after trigger to validate values....

I Salute You

Sami, January 18, 2004 - 2:05 pm UTC

As usual THE BEST ANSWER & CLARIFICATION with EXAMPLES.


A reader, January 18, 2004 - 2:38 pm UTC


Modify values after Validate values

A reader, January 18, 2004 - 7:54 pm UTC

Dear Tom,

How about if I want to modify :new value based on some validation?. Basically I want perform both validation and modification like below trigger.

Kindly need your suggestion.

CREATE OR REPLACE TRIGGER MY_ZIP_CODE_TR01
BEFORE INSERT OR UPDATE ON MY_ZIP_CODE
FOR EACH ROW
DECLARE
INVALID_ZIP_CODE EXCEPTION;
BEGIN

IF MY_PKG.MY_PROC(:NEW.FROM_ZIP_CODE) = 1
AND MY_PKG.MY_PROC(:NEW.TO_ZIP_CODE) = 1 THEN
:NEW.FROM_ZIP_CODE := LPAD(:NEW.FROM_ZIP_CODE,10);
:NEW.TO_ZIP_CODE := LPAD(:NEW.TO_ZIP_CODE,10);
ELSE
RAISE INVALID_ZIP_CODE;
END IF;

EXCEPTION WHEN INVALID_ZIP_CODE THEN
RAISE_APPLICATION_ERROR(-20002, 'ZIP CODE FORMAT NOT VALID');
END;
/

Tom Kyte
January 19, 2004 - 9:00 am UTC

as long as you do not have other triggers (in the before insert or update for each row mode), you will be OK with this.


Why would you declare an exception, to throw it, only to catch it yourself and then use raise_application_error? seems to be an obscure roundabout way to just code:

if ( f(x) = 1 and f(y) = 1 )
then
x:=...
y:=...
else
raise_application_error( .... );
end if;



Page 312-314 of Mastering PL/SQL from Connor McDonald

Winston, October 18, 2004 - 1:52 am UTC 

When reading page 312-314 about the topic 'Performance of Before and After DML Row Triggers', I realized the data doesn't support the conclusion in terms of the elpased time.   So I ran the test case on my machine. I found another error in trigger body on lin 9. It missed one ' in front of another single quote in doesn't. 

Then I looked up asktom and gained a complete understanding.

Here is the details of my bencharmark on 9.2.0.4.0 ;

create table aircraft(flying_hrs number);

insert into aircraft select dbms_random.random from all_objects;

commit;

create or replace trigger check_flying_hrs
after update of flying_hrs on aircraft
for each row
begin
    if :new.flying_hrs <:old.flying_hrs then
       raise_application_error(-20001, 'the number of flying hours doesn''t look right.');
    end if;
end;
/


create table aircraft2(flying_hrs number);
insert into aircraft2 select * from aircraft;
commit;


create or replace trigger check_flying_hrs2
before update of flying_hrs on aircraft2
for each row
begin
    if :new.flying_hrs <:old.flying_hrs then
       raise_application_error(-20001, 'the number of flying hours doesn''t look right.');
    end if;
end;
/



SQL> select trigger_name, table_name, trigger_type 
from user_triggers
where table_name like 'AIR%';  2    3  

TRIGGER_NAME                   TABLE_NAME                     TRIGGER_TYPE
------------------------------ ------------------------------ ----------------
CHECK_FLYING_HRS               AIRCRAFT                       AFTER EACH ROW
CHECK_FLYING_HRS2              AIRCRAFT2                      BEFORE EACH ROW


exec runStats_pkg.rs_start;

update aircraft set flying_hrs=flying_hrs+3.5;


exec runStats_pkg.rs_middle;

update aircraft2 set flying_hrs=flying_hrs+3.5;


exec runStats_pkg.rs_stop(1000);

Run1 ran in 1676 hsecs
Run2 ran in 1797 hsecs
run 1 ran in 93.27% of the time

Name                                   Run1         Run2      Diff
STAT...hot buffers moved to he         2,833         1,258    -1,575
LATCH.simulator hash latch             1,754         3,789     2,035
STAT...redo entries                   25,523        50,541    25,018
LATCH.redo allocation                 25,590        50,793    25,203
STAT...session logical reads          27,199        52,911    25,712
STAT...db block gets                  26,902        52,780    25,878
STAT...db block changes               51,705       102,151    50,446
LATCH.cache buffers chains           131,681       285,619   153,938
STAT...session pga memory max        196,608             0  -196,608
STAT...redo size                   6,408,952    11,337,664 4,928,712

Run1 latches total versus runs -- difference and pct
Run1         Run2         Diff     Pct
162,625       346,878       184,253  46.88%

PL/SQL procedure successfully completed.

Tom, thanks for recommending the book. That book is full of facts, test cases. 

Do you know why the Before trigger took half time than the After trigger even if it used twice logic reads and generated twice redo? I wonder if it's a typo or it's normal.

Tom Kyte
October 18, 2004 - 8:16 am UTC

where do you have "1/2 the time" above?

what I see is the after trigger

o ran faster by the wall clock
o generated lots less redo
o used lots less latches

I don't have the mastering plsql book at my fingertips right now to comment (its at home, I'm not) so I'm not sure if you are refering to the numbers in the book or above..

The before/after trigger demo

Connor McDonald, October 18, 2004 - 9:48 am UTC

Hi Winston,

I wasn't responsible for that chapter but having fielded numerous questions about precisely this particular example, I can assure you that every time I've run the demo on my own database, the results concur pretty much with your posting. That is, the AFTER trigger does less work and runs faster.

(Whilst I'm guessing here...) I'm assuming that the book example got the results back to front. I'll ask the editor to get in touch with my co-authors to try to confirm this.

Cheers
Connor

Thanks to Tom and Connor!

Winston, October 18, 2004 - 2:45 pm UTC

to Tom: I was referring to the numbers in the book.

to Connor McDonald: Thanks for your confirmation! I wonder where I could find information who is reponsible for what chapters. I remember in 'beginning Oracle programming' that kind of information was presented right in the beginning.

Again thanks for writing such a great book! It proved many things in a scientific way with very good test cases.

Cheers!
Winston

Who wrote what...

Connor McDonald, October 18, 2004 - 10:58 pm UTC

For Winston:

Anything in the book that you like... I wrote. Anything you do not like, must have been someone else :-)

Seriously though..(if my memory serves) the chapters are:

Ch 01 Me
Ch 02 Me
Ch 03 Me
Ch 04 Me
Ch 05 Me
Ch 06 Chris Beck ?
Ch 07 Chaim Katz
Ch 08 David Knox
Ch 09 Joel Kallman
Ch 10 Chris Beck



Redo generation from Before and after trigger

Anil, January 24, 2005 - 12:50 pm UTC

Tom

Surprised to see the difference in redo generation of before and after triggers. We are developing a J2EE +Oracle Application where we decided to keep before triggers at the database to fill the audit fields(created_by, created_date etc.). But this results prompt me to move these code to middle tier with Insert SQL itself. What is your take on that or Am I unnnecessarily worried about this extra redo. My worry is after seeing almost double the size of redo generation.

Rgds
Anil

Tom Kyte
January 24, 2005 - 3:01 pm UTC

nope, don't do that.

for you see, some day java won't be "cool" and if you have all of the logic to access your data only in java, well, you'll be right where everyone is/was with mainframe transactions when the web came along - totally stuck.


use a stored procedure! have java call nice database transactions written in plsql (not just an insert/table type of store procedure, but a stored procedure that does an entire transaction -- modifies all of the needed tables for a well formed transaction)

Then, when new technology "42" comes out in 2007 -- you are already well in position to take advantage of it.

Before Trigger

Anil, January 25, 2005 - 4:50 am UTC

Hi Tom

I totally Agree with your views of using stored procedures. In our application we are using stored procedures where ever there is a data intensive operation. But Normal operations are from J2ee code. We cannot change that to store procedure. Is there any other way to achive this at the database level other than trigger.



Rgds
Anil

Tom Kyte
January 25, 2005 - 9:23 am UTC

a trigger is the correct approach for supply complex default values.

you can use DEFAULT for the insert and limit the trigger to be an UPDATE trigger to set the two fields.


That a trigger generates redo is something to be aware of, that you want to use BEFORE triggers only when you need the feature set of a BEFORE trigger is something to be aware of.

It is not a reason to rule them out.


So,

create table t
( ...,
created_by .. default USER,
created_on .. default sysdate,
modified_by ...
modified_on ..
)


create trigger t_trigger before update on t
for each row
begin
:new.modified_by := user;
:new.modified_on := sysdate;
end;
/

(even I have triggers like that in my system :)



Before trigger

Anil, January 25, 2005 - 12:46 pm UTC

Hi Tom

I am slightly relaxed to hear that you have used before trigger in your system. Our application will be a high OLTP mission critical application. Where performance and scalability is of prime importance. That is why I am keen on these things.

Rgds
Anil

Tom Kyte
January 25, 2005 - 1:35 pm UTC

Where performance and scalability is of prime importance.
....


(then stored procedures should be the exclusive way to transact against the data.......)

Very useful information

Umesh, April 26, 2005 - 5:32 am UTC

Hi Tom ,
The information related to JRE is quite useful.

I am using oracle 8.1.7.4
I need to parse a xml document. For this I use XDK-the plsql parser for xml. I use 2 built-in-packages named xmlparser and xmldom for the purpose.The XML document contains @500 tags to be parsed and this parsing takes @ 2 seconds time.This is quite slow for my requirement.As the procedure would parse such 60k documents in a day.

1. Could you suggest how to improve performance time?
One way I suppose is to increase the java_pool_size but how much ?

2.Also Could we replace the JRE installed with 8i to the current versions of JRE?



Tom Kyte
April 26, 2005 - 7:39 am UTC

9i and 10g are light years ahead and most of the XML stuff is kernelized in C now.

You cannot upgrade the JRE in the database.



do you suggest a 9i or 10g XDK on 8i?

Umesh, April 27, 2005 - 2:56 am UTC

Thanks a lot Tom for a quick reply!

Do you suggest to use a XDK for 9iR2 or 10g ( which are in c ) with oracle 8.1.7.4 ?
Are there any known issues doing this? If i choose to upgrade current XDK to 9i/10g XDK, do I need other things to upgrade?






Tom Kyte
April 27, 2005 - 7:55 am UTC

you cannot, they are features of the database, part of the kernel

Oh! then how to tune memory ?

umesh, April 28, 2005 - 2:47 am UTC

Thanks a lot Tom for this very useful information!

I don't have option to upgrade database to 9i or 10G....
so, I don't have only option to tune memory related parameters to improve the performance, Correct?

...I read somewhere on net that the XML doc when parsed through plsql DOM parser might make a footprint equal to 10x of its size in the memory buffer..

so, which part of SGA/PGA it occupies?
what parameters should be tuned for this?




Tom Kyte
April 28, 2005 - 7:52 am UTC

it is not tuning memory, it is the fact that the parser is parsing a big document here.

Performance mismatch with seemingly same specs

Umesh, April 29, 2005 - 8:38 am UTC

Tom,

Thanks for your responses till now. Appreciate it very much. About the XML parsing stuff; we have 2 development environments (2 different database instances in 2 geographically distinct countries with nothing in common) having the same version of Oracle, JRE and XDK as well as similar hardware; yet we realised that one environment took 1.7 seconds to parse an XML data while the other took just 0.7 seconds to parse the same XML data using the same PL/SQL procedure. We even tried to keep the java_pool_size the same on both database servers and yet got the performance mismatch. Any ideas as to what would we be missing? Any parameters to tune, etc please? Parsing XML in Oracle 8.1.7.4 like what we are doing is very uncommon and hence there are not many references wherein we can check this out; as a result this has stumped us since some time. Would appreciate if you could throw some light on this. Thanks!


Regards,
Your pal from India

Tom Kyte
April 29, 2005 - 9:19 am UTC

sql_trace (level 12 10046 so you can see the waits in the trace file) it and dbms_profiler it.

"similar" or "the same" hardware.

Trigger priority

A reader, September 26, 2005 - 8:23 am UTC

Tom,

We are facing a problem here.
We have say two tables a (parent) , b (child).
And we have written "after insert" trigger on both the tables. The trigger on parent tables does 5 inserts in different tables & the trigger on child does 1 insert.
Now we are using these triggers as event and inserts these new rows in EVENT table.

In the code we insert in a & b and then issue a commit.
Then another application is polling the EVENT table & then it picks up these new rows & inserts the rows in other target system.
So ideally it should have first inserted a record for a (parent) & then b (child).
But the application 1st SEES the row in b & then a (sometimes..I dont know why) & then tries to insert the child row first & hence fails.

I hopw you understood the scene.
How can we solve this ???

Thanks as always.

Tom Kyte
September 26, 2005 - 9:36 am UTC

you need to have something to order this data, the application - unless it retrieves data with an ORDER BY can have no way of knowing "what" rows it will get first.

so, as you insert into event, are you adding a sortable column (like a column populated via a sequence)

otherwise, you have no reason to expect any sort of ordering

Thanx

A reader, September 27, 2005 - 1:49 am UTC

Thanks tom,

Yes we do have something called as event_id which is a sequnce.
We were planning to use that but before that I just thought if its possible through Oracle then we could try.

thanks again.

Tom Kyte
September 27, 2005 - 11:10 am UTC

it is possible via oracle -- it is "order by" ?



ORA - 29547

A reader, September 27, 2005 - 9:24 am UTC

Hi Tom,

I tried to compile the below procedure but got some errors

kaurraj@SYS97> ed
Wrote file afiedt.buf

1 create or replace and resolve java source
2 named "ver"
3 as
4 public class util
5 {
6 public static void prop()
7 {
8 System.getProperties().list(System.out);
9 }
10 public static String ver()
11 {
12 return System.getProperty("java.version", "unknown");
13 }
14 }
15* /
kaurraj@SYS97> /
named "ver"
*
ERROR at line 2:
ORA-29547: Java system class not available: oracle/aurora/rdbms/Compiler

My Oracle version details are:

SQL*Plus: Release 8.0.6.0.0 - Production on Tue Sep 27 14:11:24 2005

(c) Copyright 1999 Oracle Corporation. All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production
With the Partitioning option
JServer Release 8.1.7.2.0 - Production

Please help me out.
Thanks



Tom Kyte
September 27, 2005 - 11:39 am UTC

you don't have java installed.

Also, you should use the release of plus that goes with your database - you'll find many commands that just don't work right otherwise.

XDK upgrade?

umesh, November 14, 2005 - 7:05 am UTC

Hi Tom,
On my question "do you suggest a 9i or 10g XDK on 8i? dated April 27, 2005 " you had said that One can't do this. As its a part software.

But, I found one doument on metalink, doc id 171658.1 section: how yo Install the latest 9.2 XDK for plsql on any 816 and 817 databse.
I followed the steps to install and got it installed successfully and its working fine.
Now i want to know if there are any known bugs for 9i XDK for plsql (9_2_0_6_0 version) ? if yes, then are there any workaround/patches for it?




Tom Kyte
November 14, 2005 - 9:18 am UTC

thanks for the update - guess I was thinking "xdb", not the plsql toolkit - sorry about that....


You would utilize support for that last bit - yes, I am sure there is at least one known bug out there.


(i actually would not suggest either on 8i - if you are doing new development, you are very much at risk with 8i - being out of support and all, I'd rather upgrade than risk destabalizing everything by doing this)

dbms_java.server_startup

Nishant Choudhary, January 20, 2006 - 5:13 pm UTC

Hi Tom,

I have a query related to dbms_java package .. there are two procedure listed 'server_startup', 'server_shutdown'.
and my db have trigger defind for them.
CREATE OR REPLACE TRIGGER "SYS"."AURORA$SERVER$STARTUP" AFTER STARTUP ON DATABASE dbms_java.server_startup;

please advise there role/function/use in database.

I got below error related to this yester.

Error in executing triggers on database startup
*** 2006-01-20 01:16:01.772
ksedmp: internal or fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-29549: class SYS.oracle/aurora/namespace/rdbms/SessionContext has changed, Java session state cleared
ORA-06512: at "SYS.DBMS_JAVA", line 0
ORA-06512: at line 2

Thanks in Advance,


Tom Kyte
January 20, 2006 - 5:39 pm UTC

it is undocumented "java startup code". I'm getting a big zero on any hits on dbms_java.server_startup ora-29549. I suggest you open a tar with support on this one, I've not seen it personally.

Tiggers among Schemas

A reader, March 08, 2006 - 1:13 am UTC

Hi Tom,

Are triggers written in one schema accessible to other schema in the database. For example if a trigger tr_client is developed in one schema "TEST" is it accessible to the other schema "TEST2".

Can you let me know the Query that fetches the triggers accessible in a schema whose owner is in a different schema.

Thanks for the same.



Tom Kyte
March 09, 2006 - 12:10 pm UTC

triggers are associated with "things (insert into a table for example)" or "events (before alter for example)"


they are available to other schemas in that if other schemas do something that fires them (inserts into a table, alters something) then yes, they are "available"

Tiggers among Schemas

A reader, March 09, 2006 - 11:08 pm UTC

Thanks for the response. So it means triggers belong at the database level.So it is not like table creation where table created in one schema may not visible to a different schema.

Am I right on this?



Tom Kyte
March 10, 2006 - 12:11 pm UTC

triggers are associated with "things" (tables) and "events" (before alter).

You might not be able to SEE a trigger on a table (you don't have the privilege) but if you do something to that table that causes the trigger to fire, it'll fire.


Just like you can call code (say A.PKG_1.PROCEDURE) that calls a piece of code (B.PKG.FUNCTION) even though you cannot call B.PKG.FUNCTION yourself.

How to generate unique values

Baiju_P, March 10, 2006 - 7:10 am UTC

Sir,

What is the method of generating unique values for a table column in a multi user environment. (i.e say empno is a column for which we are generating unique values using sequence). Now, I wanted to know the method by which we can generate the empno column using sequence so as to get unique values.

Can this be done using triggers ? If so AFTER or BEFORE trigger.

If I am using a trigger for the emp table, it is showing "mutating table" error .

Please help.


Tom Kyte
March 10, 2006 - 12:28 pm UTC

if you are getting a mutating table constraint - it would indicate you need to go back to the application developers guide and take another look at triggers!

begin
select seq.nextval into :new.empno from dual;
end;
/

is what you would code, NOT

begin
update emp set empno = seq.nextval where empno = :new.empno;
end;
/

which is (sigh) what I'll guess you coded. You do not need to update the table (which would of course give you the mutating table constraint), you just muck with the :new record.

But, I would strongly suggest you do not use a trigger at all! Strongly, really strongly. So strongly I'll keep saying it over and over. You do not need a trigger for something so simple. Just:


insert into emp(empno,ename,....) values (seq.nextval, .......);



ora-04084

mal, May 08, 2006 - 6:41 pm UTC

create or replace Trigger VEHICLES_UPD_TRIG
after UPDATE ON VEHICLES
FOR EACH ROW
BEGIN
:NEW.last_update_date := SYSDATE;
if ( :new.system_id != :old.system_id OR
:new.vehicle_status_id != :old.vehicle_status_id ) then
:new.last_update_status_date := SYSDATE;
end if;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,'Detect ora error('||SQLERRM);
END;
/
create or replace Trigger "ADPROD".vehicle_prices_UPD_TRIG
*
ERROR at line 1:
ORA-04084: cannot change NEW values for this trigger type

Can you please suggest me on this?

Tom Kyte
May 08, 2006 - 8:21 pm UTC

sure, use a trigger type that CAN change the :new values..


BEFORE update on vehicles for each row


[tkyte@dellpe ~]$ oerr ora 4084
04084, 00000, "cannot change NEW values for this trigger type"
// *Cause: New trigger variables can only be changed in before row
// insert or update triggers.
// *Action: Change the trigger type or remove the variable reference.


And I suggest you lose the when others entirely.

The only thing it will do is:

a) hide the actual error code from the application
b) hide the actual line number of the trigger the error happened on
c) make it harder to figure out what happened


and if any of:

if ( :new.system_id != :old.system_id OR
:new.vehicle_status_id != :old.vehicle_status_id ) then

system_id or vehicle_status_id are NULLABLE - that logic does not work...

triggers

mal, May 09, 2006 - 2:38 pm UTC

Create or replace Trigger "ADPROD".VEHICLES_UPD_TRIG
After UPDATE ON VEHICLES
FOR EACH ROW
DECLARE
BEGIN
if (:new.vehicle_id = :old.vehicle_id and
:new.system_id = :old.system_id and
:new.vehicle_status_id = :old.vehicle_status_id) then
null;
else
update vechicles set last_update_date = :sysdate where vehicle_id =:new.vehicle_id ;
end if;
EXCEPTION
when others then
RAISE_APPLICATION_ERROR(-20001,'Detect ora error('||SQLERRM);
END;


Does it make any sense? I am very bad programmer

Tom Kyte
May 09, 2006 - 4:43 pm UTC

makes no sense.

you use a BEFORE UPDATE trigger. Not after.

You would never update the table in the trigger like that (it won't work after all, it'll fail at run time)

create or replace Trigger VEHICLES_UPD_TRIG
before UPDATE ON VEHICLES FOR EACH ROW
BEGIN
:NEW.last_update_date := SYSDATE;
if ( :new.system_id != :old.system_id OR
(:new.system_id is null and :old.system_id is not null ) OR
(:new.system_id is not null and :old.system_id is null ) OR
(:new.vehicle_status_id is null and :old.vehicle_status_id is not null)
OR
(:new.vehicle_status_id is not null and :old.vehicle_status_id is null)
OR
:new.vehicle_status_id != :old.vehicle_status_id )
then
:new.last_update_status_date := SYSDATE;
end if;
END;
/

of course, if you have some "non-possible values" for these columns - eg - like -1, that could be shortened easily:

create or replace Trigger VEHICLES_UPD_TRIG
before UPDATE ON VEHICLES FOR EACH ROW
BEGIN
:NEW.last_update_date := SYSDATE;
if ( nvl(:new.system_id,-1) != nvl(:old.system_id,-1) OR
nvl(:new.vehicle_status_id,-1) != nvl(:old.vehicle_status_id,-1) )
then
:new.last_update_status_date := SYSDATE;
end if;
END;
/

trigger

mal, May 09, 2006 - 4:57 pm UTC

How to write after update for same funtionality?
It will be a good help

Tom Kyte
May 10, 2006 - 7:26 am UTC

I've already said it - YOU WOULDN'T use an after update, doesn't make sense.

It is not the right tool for the job. You want to modify the :new values? You *have* to use a before update.

right tool, right job.

wrong tool, don't even consider it.


I fail to see how it would be "good help"

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library