Prevent Update in DB2 SQL External Trigger Program

25 pts.
Tags:
AS/400
IBM DB2
SQL
Triggers
I have a 3rd party application that maintains a customer master file (update, insert, delete) and I cannot alter their programs. I would like to put a trigger on the database file to fire an external program on insert and determine if the insert should be performed. If not, I would like to send a message to the user and abort the insert. I have written a trigger program to determine if the update should happen and send a message to the user but I do not know how to force the calling program to abort without performing the insert. Does anyone know if this is possible and if so, how? Thanks.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Here’s a site to look at
I’m not sure it will abort the calling program but
1. The insert/update/delete doesn’t occur
2. An escape message is sent to the calling program.
Hope this helps

http://www.mcpressonline.com/programming/general/the-trigger-is-the-safety-part-2.html

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Gilly400
    Hi, I understand that you cannot alter the third party software - but are you allowed to view the source? If not, you could get problems trying to do what you're doing here. When your trigger program stops/aborts the insert and returns an escape message your third party software will most likely crash. If you don't know what else the program does you may have some database files updated and others not. Have you spoken to the supplier of the software to see whether what you want to do is possible? Maybe there's even an exit routine to do the checks that you need? Regards, Martin Gilbert.
    23,730 pointsBadges:
    report
  • Djswtd
    Thanks Martin for your response. Although I don't have the source, I do know what the program is trying to do. I'm ok with the program crashing as long as the insert is not performed. My problem is that although my trigger program is sending an escape message back to QDBPUT in the call stack the calling program still completes and performs the insert. I'm expecting the *Escape message sent back to the call stack using the QMHSNDPM API to cause the calling program to throw an error and not perform the insert but that is not happening. Dennis
    25 pointsBadges:
    report
  • philpl1jb
    The sequence you're describing seems wrong 1. RPG program sends change (update/write/delete) 2. DBMS exercises the "before" trigger (update/write/delete) with allow change??? 3. trigger program runs 4. if trigger program sends escape message - DBMS doesn't do update RPG program receives escape message else DBMS performs update RPG program receives 0000 status Issues 1. Trigger must be before and may need Allow Change 2. Trigger must be for add/delete/change - whichever is used Back to this site http://www.mcpressonline.com/programming/general/the-trigger-is-the-safety-part-2.html "our trigger program to demonstrate how a trigger can deny a database action " ... "Once your trigger program detects a violation, all you have to do is send an escape message to the trigger program’s caller."..."Database management interprets this message as an error condition, and the record is not updated." author used RPG1299 - which has a Severity of 99
    50,415 pointsBadges:
    report
  • Djswtd
    After tinkering with the D specs for the program call prototype I got the calling program to fail without updating. Thanks to everyone who took the time to respond to my query. Dennis
    25 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following