RE: Default user ID in SQL table - trigger

Alan

Just as an FYI, these rules (NOT issues) are there for a reason.

One fine summer day a mediator trigger program was changed, as intended, and everyone went happily along, for 4 weeks until the EOM. Users continued to use the old trigger for the first day, and several NEPs used the old trigger for varying periods of the month (3 for the whole month) until they were ended. Spent a week (including a full weekend) fixing the data and two days removing all trace of the mediator.

You may never get bit, and in most cases you probably won't, but if you bypass the rules you may not shoot yourself in the foot but someone else probably will.

Duane Christen


--


Duane Christen
Senior Software Engineer
(319) 790-7162
Duane.Christen@xxxxxxxxxx

Visit PAETEC.COM


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Alan Campin
Sent: Tuesday, May 19, 2009 2:13 PM
To: Midrange Systems Technical Discussion
Subject: 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.

--
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.