Help with Triggers

[eiram00]

This is the first trigger that I'm making.
I'm trying to make a trigger to update only the field from the same row that is updated
when another field from the same row is updated. I'm doing something wrong.
Here is my code:

CREATE OR REPLACE TRIGGER MYSCH.APPT_RESCHEDULE
BEFORE UPDATE OF appointment
ON MYSCH.TBL_RESCHEDULE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE

BEGIN
if (new.appointment > ld.due_date) or ((truncnew.appointment) = truncold.due_date))
and to_charold.due_date,'HH24:MI:SS') < '13:00:00'))
and to_charnew.appointment,'HH24:MI:SS') = '08:00:00'

update tbl_reschedule
set :new.due_date = to_date(to_charnew.appointment,'DD-MON-YYYY')||' '|| '12:00:00',
'DDMON-YYYY HH24:MI:SS')
where pk_schedule_id := ld.pk_schedule_id;

else if (new.appointment > ld.due_date) or ((truncnew.appointment) = truncold.due_date))
and to_charold.due_date,'HH24:MI:SS') < '17:00:00'))
and to_charnew.appointment,'HH24:MI:SS') = '13:00:00'

update tbl_reschedule
set :new.due_date = to_date(to_charnew.appointment,'DD-MON-YYYY')||' '|| '17:00:00',
'DD-MON-YYYY HH24:MI:SS')
where pk_schedule_id := k_schedule_id;
end if;

EXCEPTION
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
NULL;
END MYSCH.APPT_RESCHEDULE;

Thank you for your help.

 

[jimirvine]

Hi
Instead of

update tbl_reschedule
    set :new.due_date = to_date(to_char(:new.appointment,'DD-MON-YYYY')||' '|| '12:00:00',
               'DDMON-YYYY HH24:MI:SS')
            where pk_schedule_id  := :old.pk_schedule_id;

:new.due_date = to_date(to_charnew.appointment,'DD-MON-YYYY')||' '|| '12:00:00',
'DDMON-YYYY HH24:MI:SS');
[/code]

 

[eiram00]

jimirvine Thank you for your help.

This code updates only the row that I'm updating
or it updates all the table?

 

[SantaMufasa]

Eiram,

The trigger affects only the row you are updating (your specification, "for each row" allows you to modify the contents of the current row via the ":new." prefix)...Oracle does not allow you to modify the contents of other rows while modifying the current row -- Oracle would throw an error such as "ORA-04091: table XXXX is mutating..."

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[eiram00]

I remade my trigger, but I don't understand why when I create my Trigger I get a prompt asking me to put a value on the old and new keywords. Here is my code:

CREATE OR REPLACE TRIGGER MYSCH.APPT_RESCHEDULE
BEFORE UPDATE OF appointment
ON MYSCH.TBL_RESCHEDULE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE

old_due_date date := 0;

BEGIN
old_due_date date := :new.due_date;

if (new.appointment > old_due_date date) or ((truncnew.appointment) = trunc(old_due_date date)) and
to_char(old_due_date date,'HH24:MI:SS') < '13:00:00')) and to_charnew.appointment,'HH24:MI:SS') = '08:00:00'

:new.due_date := to_date(to_charnew.appointment,
'DD-MON-YYYY')||' '|| '12:00:00', 'DD-MON-YYYYHH24:MI:SS');

else if (new.appointment > old_due_date date) or ((truncnew.appointment) = trunc(old_due_date date)) and
to_char(old_due_date date,'HH24:MI:SS') < '17:00:00')) and to_charnew.appointment,'HH24:MI:SS') = '13:00:00'

:new.due_date := to_date(to_charnew.appointment,'DD-MON-YYYY')||' '|| '17:00:00', 'DD-MON-YYYY HH24:MI:SS');

end if;

EXCEPTION
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
NULL;
END APPT_RESCHEDULE;

I will appreciate any help with this.

 

[SantaMufasa]

Eiram,

There are several syntactical issues with your code:[ul]
[li]You cannot initialise a DATE expression to "0".[/li][li]Following each "IF <true>", you must use "THEN".[/li][li]Each time you referred to the variable "old_due_date" in your procedural section, you erroneously added the word "DATE", which should only appear in the definition of the variable.[/li][li]Each "IF" statement must end with an "END IF;" clause...you have two "IFs"; you had only one "END IF;" clause[/li][/ul]Also, for informational purposes, when you have a "FOR EACH ROW" trigger, "REFERENCING NEW AS NEW OLD AS OLD" is the default.


Here is a syntactically correct version of your code. (I took the liberty of initialising "old_due_date" to SYSDATE, not knowing what you wanted its initial to be.):

CREATE OR REPLACE TRIGGER MYSCH.APPT_RESCHEDULE
    BEFORE UPDATE OF appointment
ON MYSCH.TBL_RESCHEDULE
REFERENCING NEW AS NEW OLD AS OLD -- this line is optional
FOR EACH ROW
DECLARE
   old_due_date date := sysdate; -- date cannot = 0
BEGIN
   old_due_date := :new.due_date;
   if ((:new.appointment > old_due_date) or ((trunc(:new.appointment) = trunc(old_due_date)) and
   to_char(old_due_date,'HH24:MI:SS') < '13:00:00'))  and to_char(:new.appointment,'HH24:MI:SS') = '08:00:00'
then -- this was missing
     :new.due_date := to_date(to_char(:new.appointment,
'DD-MON-YYYY')||' '|| '12:00:00', 'DD-MON-YYYYHH24:MI:SS');

 else if ((:new.appointment > old_due_date) or ((trunc(:new.appointment) = trunc(old_due_date)) and
  to_char(old_due_date,'HH24:MI:SS') < '17:00:00'))  and to_char(:new.appointment,'HH24:MI:SS') = '13:00:00'
then -- this was missing
  :new.due_date := to_date(to_char(:new.appointment,'DD-MON-YYYY')||' '|| '17:00:00', 'DD-MON-YYYY HH24:MI:SS');

       end if; -- This was missing
END if;
   EXCEPTION
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       NULL;
END APPT_RESCHEDULE;
/

Trigger created.

Let us know your thoughts.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]