Re: Please check this syntax

"Josh White" <whitegoose_at_inorbit.com> wrote in message news:aafea0a8.0503301942.5ac37083_at_posting.google.com...
> Hi,
>
> I'm new to PL/SQL and am currently writing some triggers for work -
> even though I won't actually have anything on my machine that can
> check the syntax until late next week - it's a long story.
>
> So someone please check the syntax of this simple trigger for me?
>
> -----------------------------------------------
> CREATE OR REPLACE TRIGGER hia_iu_Comp
> AFTER INSERT OR UPDATE
> ON COMP
> REFERENCING NEW AS new_Row OLD AS oldRow
> FOR EACH ROW
>
> DECLARE
> v_compType VARCHAR2(4);
> v_sql VARCHAR2(500);
> v_bgtNo VARCHAR2(24);
>
> BEGIN
> -- Get the type of asset
> SELECT v_compType = CT.COMPCODE
> FROM COMP C, COMPTYPE CT
> WHERE C.COMPTYPE = CT.COMPTYPE
> AND C.COMPKEY = :newRow.COMPKEY
>
> -- Build SQL to get budget number
> v_sql := 'SELECT C.BGTNO FROM COMP' || v_compType || ' C WHERE
> C.COMPKEY = :newRowCompKey'
>
> EXECUTE IMMEDIATE v_sql INTO v_bgtNo USING :newRow.COMPKEY;
> END
> -----------------------------------------------
>
> Thanks,
> Josh.

You don't need execute immediate. Just do v_bgtNo:=:new.bgtno; Also you don't need the select and can't do it you will get a mutating table error.
Jim Received on Wed Mar 30 2005 - 22:14:36 CST