Re: Please check this syntax

DA Morgan <damorgan_at_x.washington.edu> wrote in message news:<1112572690.33138_at_yasure>...
> 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.

OK - There will always be exactly one row.

> > -- 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.

Good.

> > EXECUTE IMMEDIATE v_sql INTO v_bgtNo USING :newRow.COMPKEY;
>
> Will fail if no rows or more than one row.

OK. There will always be exactly one row although the BGTNO may be null.

> > IF v_bgtNo IS NULL THEN
>
> No going to ever happen. Try SQL in SQL*Plus before ever putting
> it into NDS.

You lost me here. Why will this never happen? If the EXECUTE IMMDEIATE statement returns null (INTO v_bgtNo) what value will v_bgtNo get?

> > Like I said before the trigger is supposed to:
> > 1. Determine which table the BGTNO is stored in
>
> How does it do that?

This is the dynamic part of the code - it determines which table to query by appending the COMPTYPE onto the end of 'COMP' to get the table name COMPxxxx

> > 2. Raise an application error is the BGTNO is null
>
> Will never happen.

Like I said above, I don't understand this.

> > Performance is not an issue due the conditions under which this
> > trigger will fire.
>
> Hard to believe but I'll let that pass. ;-)

Thank you ;-)  

> > 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 aware of this and I'm not assuming Oracle should work the same as SQL Server but I thought it might help you to know where I'm coming from.

> > 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.

Doing it now. Received on Mon Apr 04 2005 - 01:45:03 CDT