Re: Problems with a Trigger

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Sat, 22 May 2010 07:37:07 -0700 (PDT)
Message-ID: <ab830d4f-316f-4e35-9bcc-70fcbd044faa_at_y21g2000vba.googlegroups.com>



On May 21, 5:37 pm, "Vladimir M. Zakharychev" <vladimir.zakharyc..._at_gmail.com> wrote:
> On May 21, 10:13 pm, MJSobol <murray_so..._at_dbcsmartsoftware.com>
> wrote:
>
> > Environment: Oracle 10g2
> > Windows 32-bit, Standard Edition
>
> > I have created a Trigger which compiles OK.
> > Here is the Trigger:
>
> [skip]
>
>
>
> > I am confused, also frustrated with working with DYNAMIC SQL, it can
> > be such a PITA!!!!
>
> Then why are you using it??? Dynamic SQL approach you chose is not
> appropriate here and even if it was, it's not used properly (hint:
> read about bind variables and USING clause of EXECUTE IMMEDIATE - as
> it is, your trigger will only help in creating completely unscalable
> application.)
>
> I didn't completely understand what that code is assumed to do as it's
> hardly readable and you didn't present the data model (at least in the
> scope of the trigger,) but it seems to me that (1) the data model is
> utterly wrong and (2) you are trying to work around that wrong model
> with trigger code that is no better.
>
> Now let's 'trace' the trigger. You issue
>
> update s1_contract_misc_column_item
> set description = 'Decatur'
> where misc_column_item_nbr = 10;
>
> In the trigger:
>
> :new.misc_column_item_nbr = 10
> :new.misc_column_nbr = 4
> :new.description = 'Decatur'
> c_misc_column_description = 'misc_column_description4'
>
> This triggers the following statements to be executed in order of
> appearance:
>
> UPDATE s1_contract SET misc_column_description4 = 'Decatur' WHERE 10 =
> s1_contract.misc_column_item_nbr1
> UPDATE s1_contract SET misc_column_description4 = 'Decatur' WHERE 10 =
> s1_contract.misc_column_item_nbr2
> UPDATE s1_contract SET misc_column_description4 = 'Decatur' WHERE 10 =
> s1_contract.misc_column_item_nbr3
> UPDATE s1_contract SET misc_column_description4 = 'Decatur' WHERE 10 =
> s1_contract.misc_column_item_nbr4
> UPDATE s1_contract SET misc_column_description4 = 'Decatur' WHERE 10 =
> s1_contract.misc_column_item_nbr5
> UPDATE s1_contract SET misc_column_description4 = 'Decatur' WHERE 10 =
> s1_contract.misc_column_item_nbr6
> UPDATE s1_contract SET misc_column_description4 = 'Decatur' WHERE 10 =
> s1_contract.misc_column_item_nbr7
> UPDATE s1_contract SET misc_column_description4 = 'Decatur' WHERE 10 =
> s1_contract.misc_column_item_nbr8
>
> Assuming that query output after "I do not understand this error since
> there is data that SHOULD be updated:" is from "SELECT * FROM
> s1_contract", I don't see at least two columns that the trigger would
> attempt to reference in update statements above (that are only
> misc_column_item_nbr's from 1 to 6 while there are 8 update
> statements,) so it will fail anyway as it reaches 7th update.
>
> Second point: UPDATE itself does NOT generate NO_DATA_FOUND exception
> if it didn't find any rows to update, but triggers firing on it can
> throw exceptions as they please, which is the case. The exception
> comes from your BEFORE UPDATE ON S1_CONTRACT trigger BU_S1_CONTRACT so
> that's what you need to check.
>
> Though I'd recommend you to rethink and reimplement your data model
> first. Normalize it to BCNF or 3NF and things will become much easier.
> And don't be afraid of joins - they are not really that evil.
>
> Hth,
>    Vladimir M. Zakharychev
>    N-Networks, makers of Dynamic PSP(tm)
>    http://www.dynamicpsp.com

One thing I noticed about a couple of the dynamic SQL update statement is that the use of dynamic SQL is not needed to begin with. A series of static UPDATE statements could be coded. All of the base table target row columns are available for use as bind variables to the trigger SQL.

Non-normalized data as Vladimir noted almost always ends up causing DML data value update issues. Data shold generally be fully normailized for data integrity and SQL statement performance reasons.

HTH -- Mark D Powell -- Received on Sat May 22 2010 - 09:37:07 CDT

Original text of this message