"Jim Kennedy" <kennedy-downwithspammersfamily_at_attbi.net> wrote in message news:<baidnTOmGsSZ5NbfRVn-vg_at_comcast.com>...
> "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
Thanks for the feedback - I'm just about to post an updated code
segment. However I think I do need the execute immediate.
:newRow.BGTNO does not exist. :newRow.COMPTYPE determines which table
I can retrieve the BGTNO from (this value prefixed with 'COMP' is the
table name. Does that make sense?
Received on Thu Mar 31 2005 - 19:28:58 CST
