Trigger Assistance 1

[lespaul]

I am creating a trigger that when a new row is inserted into table CMPCHKINS another table CMPCKINHEARINGS is updated with the time the officer checked in. In reviewing the Redbook on Triggers and Stored Procedures, it appears that all I need to do in the SQL statements of the trigger is:

/* Creating SQL trigger CMLIB.INSERT_TO_CHECKINHEARINGS */
CREATE TRIGGER CMLIB.INSERT_TO_CHECKINHEARINGS 
AFTER INSERT ON CMLIB.CMPCHKINS FOR EACH ROW  
MODE DB2ROW  
BEGIN ATOMIC
    UPDATE CMPCKINHEARINGS SET ChkInTIme = ChkInTIme WHERE CasPre=CasPre AND CasNum=CasNum AND CRTROM = CRTROM 
END;

Is that right?



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]

I know that's the best solution, in fact I emailed the developer who did the system and berated him for NOT having the originating program create separate records. Additionally, bad database design is responsible for the trigger in the first place.

So there's no way that within the trigger I can take the string:

360, 560, 600

parse it into pieces, loop through them and run the query with:
WHERE CRTROM = {current parsed piece- single courtroom}

Thanks again,
Leslie

 

[blom0344]

Well, if you have a max of 3 courtrooms in the field courtrooms, it may be possible to create a view on the table CMPCHKINS that stores data the proper way (just one courtroom per record).
I am not saying this is ideal , but technically it is possible.



Ties Blom

 

[lespaul]

no, there could be more than three courtrooms. I guess I'm going to change the originating program.

Thanks for your help.

Leslie