Re: Problems with a Trigger

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: Fri, 21 May 2010 14:37:11 -0700 (PDT)
Message-ID: <95829147-7045-4ed4-bc2c-15b7030e5362_at_m4g2000vbl.googlegroups.com>



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 Received on Fri May 21 2010 - 16:37:11 CDT

Original text of this message