Re: Please check this syntax

Comments in-line.

Josh White wrote:

> Here my code again:
>
> --------------------------------------------------
> CREATE OR REPLACE TRIGGER iah_iu_Comp
> BEFORE INSERT OR UPDATE
> ON COMP
> REFERENCING NEW AS newRow OLD AS oldRow
> FOR EACH ROW
>
> DECLARE
> Err_Num NUMBER;
> Err_Desc VARCHAR2(500);
> v_compType VARCHAR2(4);
> v_sql VARCHAR2(500);
> v_bgtNo VARCHAR2(24);
>
> BEGIN
> -- Get the type of asset
> SELECT CT.COMPCODE INTO v_compType
> FROM COMPTYPE CT
> WHERE CT.COMPTYPE = :newRow.COMPTYPE;

Will throw an exception if there is no row in COMPTYPE that matches or if more than one row matches.

> -- Building Assets must have a budget number
> IF v_compType = 'BLDG' THEN
> -- Build SQL to get budget number
> v_sql := 'SELECT C.BGTNO FROM COMP' || v_compType || ' C WHERE
> C.COMPKEY = :newRowCompKey';

Won't fail no matter what happens.

> EXECUTE IMMEDIATE v_sql INTO v_bgtNo USING :newRow.COMPKEY;

Will fail if no rows or more than one row.

> IF v_bgtNo IS NULL THEN

No going to ever happen. Try SQL in SQL*Plus before ever putting it into NDS.

> Like I said before the trigger is supposed to:
> 1. Determine which table the BGTNO is stored in

How does it do that?

> 2. Raise an application error is the BGTNO is null

Will never happen.

> Performance is not an issue due the conditions under which this
> trigger will fire.

Hard to believe but I'll let that pass. ;-)

> You said this code will never generate an error - can you please
> explain why? Similar code in T-SQL would work.

Because Oracle is not a Microsoft product.

The answer is not intended to be rude but rather to point out that making assumptions in Oracle based on anything SQL Server does paves the road to heck.

> I am on the other side of the country to my company's Oracle CDs until
> the end of next week so unfortunately I'm writing this blind in the
> meantime.
>
> Thanks again.

Download Oracle from otn.oracle.com.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)