Re: Please check this syntax

Josh White wrote:

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

Not a single line of this trigger makes sense.

As Jim Kennedy has pointed out you have a mutating trigger and your use of NDS superfluous.

Far mroe troubling is that your trigger does nothing. v_bgtNo is a variable. You give it a value. So what?

If this is a for a job you need to quickly take some serious training before they find you out. If this is school work you need to do some serious catch-up. Quite seriously my first year students, after 7 weeks of PL/SQL, would never write something like this: Ever!

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