Update trigger, for each row, constraint problem

From: <vick9935_at_my-deja.com>
Date: Fri, 02 Jul 1999 20:52:19 GMT
Message-ID: <7lj8pv$me7$1@nnrp1.deja.com>

 Here is an excerpt of my update trigger...
...

create trigger TU_CP_PROJ_SCHED_ACTIVITY_DATE   AFTER UPDATE OF
        ACTUAL_DATE
  on CP_PROJ_SCHED_ACTIVITY
  for each row
/* Name:TU_CP_PROJ_SCHED_ACTIVITY_DATE */ /* Date: Fri Jul 02 10:16:25 1999 */
begin

if updating('actual_date') then

   if :new.actual_date IS NULL then

	delete from cp_proj_activity_audit
	where proj_no = :old.proj_no
        and sched_activity_code = :old.sched_activity_code
	;
   else
	IF :new.sched_activity_code = '030' OR
           :new.sched_activity_code = '150' THEN
		insert into cp_proj_activity_audit
          (proj_no,sched_activity_code,actual_date,date_actual_entered)
  values(:new.proj_no,:new.sched_activity_code,:new.actual_date,sysdate)
		;
	END IF;

   end if;
end if;
end;
...

The problem is that I may be statusing activity code "030", but the trigger inserts rows for both "030" and "150" (because the trigger should insert a row if EITHER 030 or 150 is statused). Why is it inserting both rows when old.sched_activity_code is either "030" OR "150"? If a row already exists for the activity that isn't modified in the current update, I get a unique constraint problem (because of the duplicate row).

I've tried a few iterations to no avail, so any help will be greatly appreciated.

--
Brian in Seattle
Pride, commitment, teamwork - words we use to get you to work for free.

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Fri Jul 02 1999 - 15:52:19 CDT