Home » SQL & PL/SQL » SQL & PL/SQL » Trigger Problem
icon4.gif  Trigger Problem [message #192226] Mon, 11 September 2006 08:37 Go to next message
kipp@plancknet.com
Messages: 25
Registered: September 2006
Junior Member
Hi, I am trying to write a trigger but I am having a terrible time, I am not very familar with them so I am sure it is something minor (I Hope)?....

Below is two trigger one to flip the conflict flag to “N” and the other to flip it to “Y”.

The at the bottom is the data, in that example if I change the data on 117 so that the data conflicts with the 115 record, it should have flip the flag on both, where as it is only flipping the flag on the record I am changing?

Data from the table RESOURCE_SCHEDULES:
CONFLICT RESOURCE_SCHED_ID RESOURCE_ID SCD_START SCD_END
-------- ---------------------- ---------------------- ------------------------- -------------------------
N 115 600 2006-09-01 17:00:00.0 2006-09-01 18:00:00.0
Y 117 600 2006-09-01 17:00:00.0 2006-09-01 18:00:00.0
N 114 601 2006-09-02 10:00:00.0 2006-09-02 11:00:00.0
N 116 601 2006-09-05 00:00:00.0 2006-09-01 17:40:00.0

CREATE OR REPLACE
TRIGGER TGR_CONFLICT_NO BEFORE INSERT OR UPDATE ON RESOURCE_SCHEDULES
FOR EACH ROW
WHEN ((NEW.resource_id=OLD.resource_id
AND (to_char(NEW.scd_start,'YYYY-MM-DD HH24:MI:SS')
NOT BETWEEN to_char(OLD.scd_start,'YYYY-MM-DD HH24:MI:SS')
AND to_char(OLD.scd_end,'YYYY-MM-DD HH24:MI:SS'))
OR (to_char(NEW.scd_end,'YYYY-MM-DD HH24:MI:SS')
NOT BETWEEN to_char(OLD.scd_start,'YYYY-MM-DD HH24:MI:SS')
AND to_char(OLD.scd_end,'YYYY-MM-DD HH24:MI:SS'))) )

BEGIN
:NEW.conflict:='N';
END;

/

CREATE OR REPLACE
TRIGGER TGR_CONFLICT_YES BEFORE INSERT OR UPDATE ON RESOURCE_SCHEDULES
FOR EACH ROW
WHEN ((NEW.resource_id=OLD.resource_id
AND (to_char(NEW.scd_start,'YYYY-MM-DD HH24:MI:SS')
BETWEEN to_char(OLD.scd_start,'YYYY-MM-DD HH24:MI:SS')
AND to_char(OLD.scd_end,'YYYY-MM-DD HH24:MI:SS'))
OR (to_char(NEW.scd_end,'YYYY-MM-DD HH24:MI:SS')
BETWEEN to_char(OLD.scd_start,'YYYY-MM-DD HH24:MI:SS')
AND to_char(OLD.scd_end,'YYYY-MM-DD HH24:MI:SS'))))
BEGIN
:NEW.conflict:='Y';
END;

/







Re: Trigger Problem [message #192275 is a reply to message #192226] Mon, 11 September 2006 11:57 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Why are you using TO_CHAR on DATE columns to do comparisons?
Re: Trigger Problem [message #192292 is a reply to message #192226] Mon, 11 September 2006 12:50 Go to previous messageGo to next message
rigatonip
Messages: 50
Registered: December 2005
Member
You didn't tell it to change the other row. The trigger is firing and acting on only the row you are changing. Sounds like you will have to have some additional logic to change the other row.

You could probably do both of these operations in the same trigger as well.
icon4.gif  Re: Trigger Problem [message #192294 is a reply to message #192275] Mon, 11 September 2006 12:56 Go to previous messageGo to next message
kipp@plancknet.com
Messages: 25
Registered: September 2006
Junior Member
That was actually a mistake, but, I did change that to a to_date and I am getting the update on teh first record but not on the matching one that is causing the conflict. Any clues...
Re: Trigger Problem [message #192295 is a reply to message #192292] Mon, 11 September 2006 12:58 Go to previous messageGo to next message
kipp@plancknet.com
Messages: 25
Registered: September 2006
Junior Member
Not to be stupid, but how do you do that, I am new to DBA responsiblities and really new to triggers?
Thanks in Advance.
Re: Trigger Problem [message #192310 is a reply to message #192226] Mon, 11 September 2006 15:08 Go to previous messageGo to next message
rigatonip
Messages: 50
Registered: December 2005
Member
You can call a procedure or function in the trigger that finds the conflicts and deals with them. Something similar to this should do what you need:

create or replace trigger tgr_conflict 
before insert or update
on resource_schedules for each row
 l_conflict boolean;
begin  

 -- Call procedure to mark rows that conflict with the one
 -- we are updating. Return l_conflict so we know if we
 -- have conflicts.
 check_conflicts(:new.resource_sched_id, l_conflict);

 -- if we have conflicts, set the flag for the current record
 if l_conflict
 then
   :new.conflict := 'Y';
 end if;

end;


And a procedure like this will find and deal with the other conflicting rows:

create or replace procedure check_conflicts(
  pi_sched_id in number,
  po_conflict out boolean) 
is
begin

 po_conflict := false;

 -- find conflicting rows and update them
 for r1 in (select rowid
             from resource_schedules a, resource_schedules b
             where a.resource_sched_id = pi_sched_id
               and a.resource_sched_id <> b.resource_sched_id
               and a.resource_id = b.resource_id
               and ( (a.scd_start between b.scd_start and b.scd_end)
                     or (a.scd_end between b.scd_start and b.scd_end) )
 loop

   update resource_schedules
   set conflict = 'Y'
   where rowid = r1.rowid;

   po_conflict := true;

 end loop;

end check_conflicts;


But since the procedure will read data from the same table we are modifying, we'll get a mutating table error. Take a look at http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adg13trg.htm#786 and this http://asktom.oracle.com/~tkyte/Mutate/index.html to see how to handle that.
Re: Trigger Problem [message #193273 is a reply to message #192310] Fri, 15 September 2006 09:35 Go to previous messageGo to next message
kipp@plancknet.com
Messages: 25
Registered: September 2006
Junior Member
What if I dont use the trigger but just have the procedure, and have the application call the stored procedure? To loop through all the records checking for conflicts.
How would update the conflict flag to 'Y' or 'N'?

Thanks
Re: Trigger Problem [message #193331 is a reply to message #192226] Fri, 15 September 2006 13:21 Go to previous message
rigatonip
Messages: 50
Registered: December 2005
Member
You could change that procedure to a function that returns a Y if there's a conflict or an N if there isn't. The application would then do whatever it needs to do with that value. Either inform the user of the conflict or schedule the resource and update the appropriate records to show there's a conflict.
Previous Topic: Need a SQL Expert to solve my SQL dilema
Next Topic: How to check records between AM && PM in a single day
Goto Forum:
  


Current Time: Thu Dec 08 18:00:05 CST 2016

Total time taken to generate the page: 0.18171 seconds