SQL Trigger and External Stored Procedures

[danlong005]

I have a external rpgle trigger(MYTRIGR) right now that is applied to a table(MYTABLE and it has 276 fields in it). I have been reading the Modernization Redbook, and found a great tip that I want to implement in section 8.4.2. This particular table is VERY difficult to get an exclusive lock on. So we want to create an SQL trigger(MYTRIGS) on the table and then turn our existing rpgle trigger(MYTRIGR) into a stored procedure(MYTRIGP) so that we can make updates to the stored procedure without having to have the lock. I have been reading the Stored Proc/Trigger/ and UDF redbook as well.

What I haven't found yet is how to push the before and after(old row and new row) to the stored procedure. Can anyone help me? Here is what I have so far...

This is the SQL Trigger

 CREATE OR REPLACE TRIGGER MYTRIGS
   AFTER INSERT ON MYTABLE
   REFERENCING NEW ROW AS NEW_ROW
               OLD ROW AS OLD_ROW
   FOR EACH ROW MODE DB2ROW
   BEGIN
     CALL MYTRIGP( NEW_ROW, OLD_ROW );
   END

Here is the External Stored Procedure

CREATE OR REPLACE PROCEDURE MYTRIGP
  ( IN NEW_ROW [COLOR="#FF0000"]?????[/COLOR], IN OLD_ROW [COLOR="#FF0000"]?????[/COLOR] )
  LANGUAGE RPGLE
  SPECIFIC MYTRIGP
  EXTERNAL NAME MYLIB/MYTRIGR
  PARAMETER STYLE GENERAL

I don't want to have to send each field to the stored proc, and I don't want any logic processing in the trigger. This way changes can be made as needed without having to get a lock on the table.

[tomholden]

Re: SQL Trigger and External Stored Procedures

the way i get around this issue is to put a program on the trigger that either calls a program that actually does the processing or writes to a data queue & have a program read the data queue to do the processing. no more locks are required to make changes to your processing.

[danlong005]

Re: SQL Trigger and External Stored Procedures

Thanks. I was just reading in the SP/Trigger/UDF redbook that using SQL Triggers would not allow me to change the "new record" on a before trigger in the stored proc. That I would have to do all of that in the actual trigger. So I was thinking that I could use 2 RPGLE programs to do this.

[tomholden]

Re: SQL Trigger and External Stored Procedures

if you are trying to alter the data on a before trigger IIRC you would have to do it in the actual program tied to the file but i'm old & not enough coffee yet so i might be wrong lol

[danlong005]

Re: SQL Trigger and External Stored Procedures

Ok. Well... That is just a limitation that I will have to deal with. The before triggers that I am worried about are on a less widely used table. Thanks again. I will give it a try and hope, but will be prepared for this to be true.

[TedHolt]

Re: SQL Trigger and External Stored Procedures

I don't see what you have to worry about, Daniel. The trigger program is going to receive the parameters by reference. The trigger program needs to call the second program, passing the same parms by reference, and let it modify the new record image. Neither program has to do any I/O to update the file -- the system takes care of that.

[Scott Klement]

Re: SQL Trigger and External Stored Procedures

If you find yourself needing to change the trigger frequently, and the exclusive lock is a big problem, then you might try using a tool like Alan Campin's Trigger Mediator. Personally, I don't use it, as this hasn't been a problem for me -- but others I've talked to have recommended it highly.

Find it here:

http://www.think400.dk/downloads.htm

[danlong005]

Re: SQL Trigger and External Stored Procedures

Thanks Guys

Ted thanks for that I will look into it. These are my first trigger programs, so I am learning as I go.

Scott thanks for the tip. I may look into that. The lock is a problem for us as it requires that we bring down everything(website, IVR, Data Entry). It is a rather extensively used file for us. Ideally we will not be changing this trigger much.