Re: calling plsql with invoker rights from trigger

Billy Verreynne wrote:

> misiacz1_at_free.polbox.pl (Michal Faber) wrote
>

[...]
>
> A trigger is there for providing data validation and integrity.
> Period. Anything else must be separated from the trigger itself - e.g.
> the most dorkish dead dumb brain thing I have seen with triggers, is
> people wanting to have it send e-mails and the like. That flies in the
> face of fundemental RDBMS design principles and make me reach for my
> lead pipe.
>
> That type of thing has no place in a RDBMS table trigger. If a
> transaction should cause an e-mail event for example, then have the
> trigger generating that event OUTSIDE the current execution scope of
> the trigger - which in Oracle talk means using something like
> DBMS_JOB, DBMS_ALERT, or DBMS_AQ.
>
> --
> Billy

OK, this is off topic from the original post, but related to your statement about triggers. I'm curious as to best practice...

We recently implemented an LDAP/sendmail system to fix a problem with spoofing e-mails (our Java application used to send them all out spoofed as if they were from the actual application user, instead of from our site. Needless to say, anti-spam measures on the net make this a bad tactic...)

So, we used as a starting point the example trigger supplied by Oracle (9.2) for updating an LDAP server (OID, as it happens) when a table is updated. ($ORACLE_HOME/ldap/demo/plsql/trigger.sql).

Are you saying that this would be much better as a DBMS_ALERT, rather than a trigger? The LDAP update should be concurrent with the table update, although if it fails it isn't fatal.

--Mark Bole Received on Fri Feb 20 2004 - 20:11:35 CST