Re: Default user ID in SQL table - trigger

Sorry I did not get back on the Trigger Mediator post. I have been very
busy.

Issues with triggers are well known.


1. Triggers put an exclusive lock on the trigger program. It cannot be
removed except by removing the trigger from the table. If errors are
detected in the trigger program, they cannot be corrected without removing
the table from triggers.
2. Triggers put an exclusive lock on tables when they are under trigger
control. They cannot be deleted or changed while under trigger control.
3. When enabling a trigger, you must put the name of the individual
program to call for each event, Add, Update, Insert.
4. The only way to disable triggers is to remove triggers from all
tables.
5. Triggers cannot be disabled except by removing the trigger from the
table and then adding the trigger back.


Basically once it on there is nothing you can do to get it off except by
getting everyone out the database. If you have something going on that is
screwing things up that is a real problem.

Someone eluded to putting a program between the OS and the trigger service
program. That is actually what the mediator does. You maintain a database of
tables with the correct service program and procedure to call and turn the
turn the triggers off and on through the database.

You still have to get the mediator on (Program TG_MED) but that is a one
shot deal. Once it is on, you can add the trigger to the table at anytime.

Chuck discussed the various issue below with signaling errors. One of the
advantages of using my Trigger Mediator is that is comes with a standard
error handler. When an error occurs, you issue a ERRH_Throw which logs the
messages and sends an escape message back. The mediator just captures the
error and send it up the stack. When you arrive back on your caller, you can
just call ERRH_GetMessageCount and ERRH_GetMessage to retrieve the messages.


All this is provided as standard functionality.

About the only issue I had with the mediator is that it could not handle
recursive calls but I fixed that several releases back.

I think it solves all of the problems of triggers and the price is right!
Free.

Give it a try and let me know if you have issues.
www.think400.dk/downloads.htm



On Tue, May 19, 2009 at 12:41 PM, CRPence <CRPbottle@xxxxxxxxx> wrote:

rob wrote:

True, but one would have to understand the mediator. If one
assumes that the mediator simply is there to prevent a lock and
you try an escape in your end program only to find out that the
mediator went gung ho with error trapping and does not pass the
escape up you could be in trouble.

But understanding the mediator is not a bad idea.

IMO this is summed up nicely as "If you do not know how to code
and\or design, then ..." If someone has to understand that the
mediator will resignal or that they have to send an escape to the
specific database I/O program seems moot, because in either case
they have to understand both how to code and how things work [i.e.
the design to which to code]. If the mediator is not designed to
resignal, then they will need to know that, or to make it so; code
to whatever is the design.

Not sure that the mediator should be the one blasting the escape
message. Then again, judging by the recent posts, perhaps it's
harder than I think for most people to get sending an escape
message.

As alluded, for OPM and return-code based implementations, that
will have more value. Should the same logic that decides which QDB*
program should receive the exception be coded in every program, even
if effected by /COPY [for which any changes requires recompiles]?
Or should such logic be coded in just one common place, which could
be the mediator? A procedure can better resolve the concern for
ILE, which could be called by each trigger program or by the
mediator. Each trigger would still need to either set a return code
or to signal an exception to notify the mediator. Thus for an
implementation via exceptions, it is little difference whether the
trigger program or the mediator sends the exception, because the
recipient logic would be encapsulated in just one procedure. If the
design for triggers was to send to the mediator then the mediator is
hard-coded in the call to the QMHSNDPM, for which no logic is required:

// trigger program has decided this request is not allowed:
If RequestDisallowed Then Do;
call PreventTriggeredIO(); // sends escape to DB I/O pgm
// via mediator, or directly
// if coded here as CALL QMHSNDPM, and directed to the DB
// I\O program, then logic to decide to which clutters
End;

And, yes, doing what you can to supply information or diagnostic
messages would really help. So instead of "CPF9898-Trigger
failed" something in there about what constraint or whatever
failed would be nice. Makes me wonder how that would behave in:

write e newrec;
if %error(myfile);
Select %status;
When 01023; // Error in trigger before file operation
// Retrieve diag or info message from job
// and do something about it
EndSl;
EndIf;

This is where the stack matters, for ease of message retrieval
and optionally removal. If the diagnostic message goes to either a
trigger mediator or a database I/O program [instead of to the I/O
requester], then the message is not directly retrievable via receive
program message feature [by the application, in response to the "I/O
failed" error]. That is because when control returns to the
application, those programs [the DB I/O and trigger related] are no
longer on the stack.

Regards, Chuck
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.