Re: Triggers? Is there another way with Java?

In article <87vgkvjq1v.fsf_at_ivett.gutschke.com>, Stephan says...
>
>Hi there,
>
>I am about to program a software (in Java) which views some
>data that is stored in an Oracle database. My question is now:
>
>Is there a way that my Java program is notified, whenever any of the
>data-fields that I am viewing change for some reason in the database?
>I want then be able to update in the Java program the view of the
>fields.
>
>I know that I could use some PL/SQL trigger stuff :),
>but I thought that there might be an easier way.
>I heard something about a "embedded java VM" in oracle,
>could I maybe do something with that thing :).
>
>
>Thank you very much
>
> Stephan

You can code triggers in Java. See the CALL option on the CREATE TRIGGER command. See
http://technet.oracle.com/doc/oracle8i_816/java.816/a81358/04_call2.htm#12230

To say that it would be "easier" is stretching it. I think you will find that when deal with SQL data and SQL datatypes in a trigger, PLSQL is not only faster and more efficient but easier and more natural as well.

As a point of comparision, you can see the example trigger in java via the above referenced link. The equivalent in PLSQL is:

CREATE OR REPLACE TRIGGER sal_trig
AFTER UPDATE OF sal ON emp
FOR EACH ROW
WHEN (new.sal > 1.2 * old.sal)
begin

   insert into sal_audit values ( :new.empno, :old.sal, :new.sal ); end;

that is in place of about 25 lines of Java/SQL code. Not only that, but unless you make the java code more sophisticated, you would incurr a soft parse of it's insert statement for each row inserted into the base table (hence the real java code in a production system will be somewhat larger then the simple exampel is)

The second trigger example is likewise much easier in PLSQL, it would be:

CREATE OR REPLACE TRIGGER emps_trig
INSTEAD OF INSERT ON emps
FOR EACH ROW
declare

   l_tname varchar2(30) default 'mktg';
begin

   if (:new.dname = 'Sales') then l_tname := 'Sales';    end if;
   execute immediate 'insert into ' || l_tname || ' values ( :x, :y )'    using :new.empno, :new.ename;
end;

A language is a language, we should all be able to use a couple of them in the right places where appropriate....

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp