Re: Calling an SQL function from within an Oracle trigger


swethasivaram@xxxxxxxxx wrote:
Hello all

I have the following requirement:

Before insert or update on a table tableA, I need to check if the value
for fieldAtableA which is being inserted or updated in tableA is within
the range specified by two values from another table tableB.

I have a function which performs this check and returns me true if the
value falls within the range or false if it doesnt.

I want to have a trigger before insert or update on tableA which calls
this function. How do I call this function from the trigger and how can
I use the return value to either allow the insert/update or disallow
it?

Any suggestions will be greatly appreciated.

Thanks
Swetha

I am not going to bother to code an example but all you needs is a
before insert row trigger. Perform a select your_func into variable
from sys.dual then test the result for true or false. If true do
nothing so the insert proceeds and if false raise_application_error to
stop the transaction.

See the pl/sql manual for raise_application_error.

Your best source for information on how to code triggers is probably
the Oracle9i Application Developer's Guide - Fundamentals Release 2
(9.2) Part Number A96590-01, Ch 15 Using Triggers, or equilivent for
your version

HTH -- Mark D Powell --

.

Relevant Pages

  • Can We Explain Missing Rows?
    ... My database has TableA and TableB. ... Table A contains a DELETE trigger that removes rows from TableB (TableA has ...
    (microsoft.public.sqlserver.programming)
  • Re: Can We Explain Missing Rows?
    ... >> Your trigger can only handle a delete statement affecting one row. ... I just discovered that TableA is missing a bunch of rows ... >>> CREATE Procedure DeleteAccessController ...
    (microsoft.public.sqlserver.programming)
  • Re: Calling an SQL function from within an Oracle trigger
    ... Before insert or update on a table tableA, I need to check if the value ... I want to have a trigger before insert or update on tableA which calls ... I use the return value to either allow the insert/update or disallow ...
    (comp.databases.oracle.misc)
  • How do I create a trigger in a stored procedure?
    ... I want a stored procedure to: ... Delete existing trigger on TableA ... WHERE (LanguageCode 'EN-US') ...
    (microsoft.public.sqlserver.programming)
  • Re: Easy Trigger "?" question
    ... Stored Procedures ... Write a Insert Trigger to fire when there is an insert in tablea and load ... insert into tablec select * from inserted ...
    (microsoft.public.sqlserver.programming)