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]

Ok, I found the correct way:

CREATE TRIGGER CMLIB.CHKIN_INSERT_UPDATE_CHKINHEARINGS 
AFTER INSERT ON CMLIB.CMPCHKINS 
REFERENCING NEW N 
FOR EACH ROW  MODE DB2ROW  
WHEN ( n.STATUS = 'C' )
 BEGIN ATOMIC
   UPDATE CMLIB.CMPCKINHEARINGS SET CHKINTIME = n.CHKINTIME WHERE OFFNUM = n.OFFNUM AND MAGENC = n.MAGENC;
 END;

However, now I need to do one thing if STATUS = C and another if STATUS = M. Can I do this:

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

 BEGIN ATOMIC
WHEN ( n.STATUS = 'C' )
   UPDATE CMLIB.CMPCKINHEARINGS SET CHKINTIME = n.CHKINTIME WHERE OFFNUM = n.OFFNUM AND MAGENC = n.MAGENC;
WHEN (n.STATUS = 'M' )
[i]next update statement[/i]
 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]

I wonder whether DML statements can be wrapped within CASE constructs like:

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

 BEGIN ATOMIC
CASE
WHEN ( n.STATUS = 'C' )
THEN
   UPDATE CMLIB.CMPCKINHEARINGS SET CHKINTIME = n.CHKINTIME WHERE OFFNUM = n.OFFNUM AND MAGENC = n.MAGENC;
WHEN (n.STATUS = 'M' )
next update statement
ELSE NULL END
 END;

This is a longshot...

Ties Blom

 

[lespaul]

Well, this works for the IF construct. I haven't had a chance to see if the IN construct works for finding the courtroom. I figured it was worth a shot since it IS a comma separated list that this MAY work.

CREATE TRIGGER CMLIB.CHKIN_INSERT_UPDATE_CHKINHEARINGS 
AFTER INSERT ON CMLIB.CMPCHKINS 
REFERENCING NEW N 
FOR EACH ROW  MODE DB2ROW  
BEGIN ATOMIC
  IF n.STATUS = 'C'
  THEN
    UPDATE CMLIB.CMPCKINHEARINGS SET CHKINTIME = n.CHKINTIME WHERE OFFNUM = n.OFFNUM AND MAGENC = n.MAGENC AND CRTROM IN (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;

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]

An 'IN' clause should no work in this case, cause the value for update is no longer 'singular' (more then value may be returned)

Ties Blom

 

[lespaul]

no, actually it IS a single value, the field Courtrooms is:
360, 740, 620

(not MY bad design, but what I have to work with!!)

But I think I recall some courtrooms that are text, which would require them to be quoted...I'll have to look into that.

 

[blom0344]

But, then why the 'IN' operator?

Ties Blom

 

[lespaul]

(From Thread183-1347306)
Ok, here's a little history:

The courtroom/judges needed to be able to see which officers are in the courthouse so that cases weren't dismissed because the officer failed to appear for the hearing. Except the officer WAS in the courthouse, just in a different courtroom. So now we have a table, CMPCHKINS. This table ties into several other (horribly designed) tables and the query that is required to refresh this data is HORRENDOUS!

So, we have decided to nightly take all the static court information and import that into CMPCKINHEARINGS, which has a structure that mimics the query the program is currently using. When an officer checks in a record is added to CMPCHKINS:
SEQ (autoincrement)
OFFNUM
MAGENC
CHKINDATE
CHKINTIME
STATUS (indicates a checkin or a message C or M)
COURTROOMS
ADDCRTROOM

[tt]
SEQ OFFNUM MAGENC CHKINDATE CHKINTIME STATUS COURTROOMS ADDCRTROOM
67989 5001 APD 20060319 1343 C 360,560,600 740
67990 5051 AAC 20060319 1400 C 540,740
[/tt]

So when record 67989 is entered, I need to update CMPCKINHEARINGS with the time the officer checked in, but ONLY for the courtrooms in COURTROOMS and ADDCRTROOM. The CMPCKINHEARINGS table contains:

CHKINTIME (blank)
HERTIM
HERTYP
CASPRE
CASNUM
JUDNAM
OFFCR
OFFNUM
MAGENC
CRTROM (a single record for EACH courtroom)

So if the CMPCKINHEARINGS has two hearing records for officer 1144, one in the morning in courtroom 360 and one in the afternoon in courtroom 740, when he checks in for 360 I DON'T want to check him in for the afternoon setting in 740.

I thought about checking the time to see if it's before the court hearing time, but then I noticed that some officers check in at 2:04 for a 2:00 hearing. If I do some funky time check I might miss something.

 

[blom0344]

I am not even trying to understand how and why. But if you store information on 3 courtrooms in 1 field (like 360,560,600) than I reckon it is stored as the string
'360,560,600'.
If you are going to evaluate whether either one or more of these courtrooms fit the bill you would use:

WHERE CRTROM LIKE '%360%' OR CRTROM LIKE '%560%' OR CRTROM LIKE '%600%'

But I may miss something special here...


Ties Blom

 

[lespaul]

I'll let you know if it works! I did figure out that the text courtrooms are being excluded, so there's a chance!

Leslie

 

[lespaul]

Well, this is wierd. It works for single courtroom check ins, but not multiple courtrooms.

So if the CMPCHKINS record is:
[tt]SEQ OFFNUM MAGENC CHKINDATE CHKINTIME STATUS COURTROOMS ADDCRTROOM
67989 5001 APD 20060319 1343 C 360 [/tt]

then the UPDATE query works and updates courtroom 360 for that particular officer. But if it's a multiple:

[tt]
SEQ OFFNUM MAGENC CHKINDATE CHKINTIME STATUS COURTROOMS ADDCRTROOM
67989 5001 APD 20060319 1343 C 360,560,600 [/tt]

then it doesn't update any of the information. I kinda figured it would either work or not, not that it would work on some but not others.

Oh well, back to the drawing board.

Tell me, can I call a stored procedure from a trigger? Or can you think of some way to "split" the multiple courtrooms so that I can run the update for each courtroom entered?

Thanks!

leslie

 

[lespaul]

I found a thread in the SQL Server forum that dealt w/ a comma separated list and it suggested assigning the value of the comma separated list to a variable and trying that, so that's what I'm trying now:

/* Creating SQL trigger CMLIB.CHKIN_INSERT_UPDATE_CHKINHEARINGS */
CREATE TRIGGER landrews.CHKIN_INSERT_UPDATE_CHKINHEARINGS 
AFTER INSERT ON landrews.CMPCHKINS 
REFERENCING NEW N 
FOR EACH ROW  MODE DB2ROW  
 BEGIN ATOMIC
  DECLARE courtrooms VARCHAR(100);
  SET courtrooms = n.Courtrooms;
   IF n.STATUS = 'C'
 THEN
      UPDATE CMLIB.CMPCKINHEARINGS SET CHKINTIME = n.CHKINTIME WHERE OFFNUM = n.OFFNUM AND MAGENC = n.MAGENC AND CRTROM IN (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;

I should find out shortly if that works!

Leslie

 

[blom0344]

Leslie,

How can I explain?
You cannot read the contents of a field the way you think!
That is why the update is performed for '360'

The statement :

where courtroom in ('360,560,600')

is NOT equivalent to:

where courtroom = '360' or courtroom = '560' or courtroom = '600' )

It is equivalent to:

where courtroom = ('360,560,600')

'360,560,600' is ONE value!


Ties Blom

 

[lespaul]

but I'm not adding the ' ' around the value. If the value of n.Courtrooms is 360, 560, 600 why are the ' being included in the SQL statement?

I can build a string in delphi that is:

str := '360, 560, 600'

and then use it in a query:

qry := 'SELECT * FROM TableName WHERE Courtroom IN (' + str + ')';

and when it's all put together the SQL statement will read:

qry = 'SELECT * FROM TableName WHERE Courtroom IN (360, 560, 600)

That's all I'm trying to do here too....

 

[lespaul]

ps: Courtroom in the target table is a number, not a text field, so there don't need to be ', it should be:

IN(360, 560, 660)

not
IN('360', '560', '660')

Thanks,
Leslie

 

[lespaul]

On another topic, how do I debug triggers/stored procedures?

I'm using the iSeries Navigator and the Run SQLScripts process to create the triggers & stored procedures, but I can't figure out how to see what's actually being done (like viewing the complete SQL statement in my stored procedure after the parameters are passed in).

Thanks,
leslie

 

[MarcLodge]

Leslie,
Let me try to get this straight in my head.
Each court room is a three digit number eg 360, 460, 560.
The column Courtroom is numeric.
When this numeric column contains one room it will hold the value 360.
When this numeric coumn contains two rooms it will hold the value 360,560 or more likely 360560.

Is this correct or am I barking up the wrong tree......?

Marc

 

[lespaul]

Ok, there are two tables
CMPCHKINS (contains comma separated list of courtrooms)

CMPCKINHEARINGS (contains a single record for each officer for each courtroom)

There is a program (Officer CheckIn) that writes a record to CMPCHKINS. This record contains a comma separated list of the courtrooms that the officer is here for.

I need to create a trigger for CMPCHKINS that updates CMPCKINHEARINGS when a record is inserted in CMPCHKINS.

So an officer checks in and a record goes to CMPCHKINS:
[tt]
SEQ OFFNUM MAGENC CHKINDATE CHKINTIME STATUS COURTROOMS(text field) ADDCRTROOM
67989 5001 APD 20060319 1343 C 360,560,600 [/tt]

so above this officer is here for cases that are in three different courtrooms: 360, 560, 600
This is a string field.

Now, I need to update a specific record in CMPCKINHEARINGS. This table has the following information and ALL of it is completed EXCEPT the CHKINDATE and CHKINTIME fields (here is ONE record that would be related to the CMPCHKINS table entry above):[tt]
CHKINTIME null
HERTIM 1400
HERTYP CT
CASPRE CR
CASNUM 123406
JUDNAM Jane Doe
OFFCR Bob Smith
OFFNUM 5001
MAGENC APD
CHKINDATE Null
CRTROM 360 (numeric field)[/tt]

(this same officer will have 2 other records with different case numbers one in CRTROM = 560 and one in CRTROM = 660)

So, I need to update all three records in CMPCKINHEARINGS. If I was writing an SQL statement I would write:

UPDATE CMPCKINHEARINGS SET CHKINTIME = 1430 WHERE OFFNUM = 5001 AND CRTROM IN (360, 560, 660)

And I would expect all three records to be updated.

Thanks for your continued assistance! I didn't think it would be quite so difficult!

Leslie

 

[MarcLodge]

Leslie,
The problem, as I'm sure you are aware is that the two fields are not comparable. One is a numeric field, the other a text field containing multiple values of the numeric field that will need converting to numeric in order to make the match.

You've said that the rows are written to CMPCHKINS via a program, Officer Checkin. Is it possible to do this programmatically, rather than use an SQL and a trigger? Could you change the program so that immediately after the rows have been inserted or updated on CMPCHKINS, the program breaks down the multiple courtroom text column, moves each value that it finds there to a numeric column and proceeds to perform the insert or update?

I feel that the table design might lend itself to this type of a solution rather than a trigger.

Marc

 

[blom0344]

Deep down this is a matter of database design. If you want to establish data for 3 courtrooms than you either:

1. Store courtrooms in multiple fields (but then you can not store more than a fixed amount of courtrooms)

2. Or store a separate record for each courtroom.

The last design is the best one. It allows for proper design and the use of SQL

Marc is right about solving this programmatically if you store multiple courtrooms in one field.

Ties Blom