Current time as part of criteria?

[lespaul]

I have the following trigger:

CREATE TRIGGER CMLIB.CHKIN_INSERT_UPDATE_CHKINHEARINGS 
AFTER INSERT ON CMLIB.CMPCHKINS 
REFERENCING NEW N 
FOR EACH ROW  
MODE DB2ROW  
BEGIN 
  IF n.STATUS = 'C' THEN
     UPDATE CMLIB.CMPCKINHEARINGS SET CHKINTIME = n.CHKINTIME WHERE OFFNUM = n.OFFNUM AND MAGENC = n.MAGENC AND CRTROM = (n.Courtrooms);
  ElseIF n.STATUS = 'M' THEN
    UPDATE CMLIB.CMPCKINHEARINGS SET MSGDATE = n.MSGDATE, MSGTIME = n.MSGTIME, MSGSOURCE = n.MSGSOURCE, MESSAGE = n.MESSAGE WHERE OFFNUM = n.OFFNUM AND MAGENC = n.MAGENC;
END IF;
END;

(for those of you familar with Thread178-1346593, I ended up modifing the originating program so there's a single record for each courtroom entry and the trigger fires for each courtroom).

My subsequent issue has now turned into this. If the officer is checking in during morning hours, I only want to update records with the trigger above that meet the criteria WHERE HERTIM < 1200 and inversely, if it's in the afternoon, I want to update records WHERE HERTIM >= 1200

Is there a way I can determine in the trigger if it's AM or PM, maybe assign that to a variable to determine what the final WHERE clause should be?

Thanks!

Leslie

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[lespaul]

Here's what I finally ended up with and it works perfectly, hopefully it will come in handy for someone else:

CREATE TRIGGER LANDREWS.CHKIN_INSERT_UPDATE_CHKINHEARINGS 
AFTER INSERT ON LANDREWS.CMPCHKINS 
REFERENCING NEW N 
FOR EACH ROW  MODE DB2ROW  
BEGIN

   IF n.STATUS = 'C' THEN
     if Time(Now()) < Time('12:00 pm') then
      UPDATE CMLIB.CMPCKINHEARINGS SET CHKINTIME = n.CHKINTIME WHERE OFFNUM = n.OFFNUM AND MAGENC = n.MAGENC AND CRTROM = (n.Courtrooms) AND HERTIM < 1200;
     else
       UPDATE CMLIB.CMPCKINHEARINGS SET CHKINTIME = n.CHKINTIME WHERE OFFNUM = n.OFFNUM AND MAGENC = n.MAGENC AND CRTROM = (n.Courtrooms) AND HERTIM >= 1200;
     end if;
   ElseIF n.STATUS = 'M' THEN
      UPDATE CMLIB.CMPCKINHEARINGS SET MSGDATE = n.MSGDATE, MSGTIME = n.MSGTIME, MSGSOURCE = n.MSGSOURCE, MESSAGE = n.MESSAGE WHERE OFFNUM = n.OFFNUM AND MAGENC = n.MAGENC;
   END IF;

 END;

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[blom0344]

alternative is the MIDNIGHT_SECONDS function. I somehow feel that those REALLY vendor-specific functions deserve to be used

Ties Blom

 

[sathyarams]

Is there any reason CURRENT TIME has not been considered ?

if CURRENT TIME < Time('12:00') then
...
else
...
end if

Cheers

Sathyaram

For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

&

http://www.idug.org/user/UserLogin.asp