Re: Oracle Trigger Problem

On 23 Jul 2003 12:34:24 -0700, eric.edder_at_oberg.com (Eddie Monster) wrote:

>I am new to Triggers...
>
>I have two oracle triggers the first trigger (which works fine) simply
>copies records from table ttdilc501795 to my table (ttdobg501795).
>The second trigger is an update trigger that is supposed to update
>only two fields (t$cstk and t$cstr) in my table when they are modified
>in the original original. The problem is that the trigger updates the
>fields for all records in my table. How do I fix this?
>
>-- Create Trigger to update data copied from ttdilc501795 to
>ttdobg501795 after
>-- the inventory cycle count is processed, but before the system
>deletes the
>-- records in ttdilc501795 during processing.
>
>CREATE OR REPLACE TRIGGER CYCLECOUNT
>BEFORE UPDATE ON ttdilc501795
>FOR EACH ROW
>WHEN (new.t$cstk <> old.t$cstk
>OR new.t$cstr <> old.t$cstr)
>DECLARE
> ws_cstk number(16,4);
> ws_cstr number(16,4);
>BEGIN
> ws_cstk := :new.t$cstk;
> ws_cstr := :new.t$cstr;
>UPDATE ttdobg501795
>SET t$cstk = ws_cstk,
> t$cstr = ws_cstr
>WHERE :new.t$orno = :old.t$orno
>AND :new.t$sern = :old.t$sern
>AND :new.t$item = :old.t$item;
>
>END;
>
>/
>
>
>Any suggestions would be greatly appreciated!

You don't seem to refer to any column of ttdobg501795 (what a horrible table name) in the where clause of the update statement. Probably implicitly you where clause reads 'where 1 = 1'

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Wed Jul 23 2003 - 15:02:10 CDT