Re: Rejecting an add to a database file...

On 30-Jul-2014 16:13 -0500, Murray wrote:

I would like to add a trigger to a file.. so that when an add event
is fired.. the trigger looks at the data being added, and if zero
does not proceed with the add... Is this possible?, and if so now? I
have a vague recollection that this is possible by raising an escape
message?

Note: The quoted message was originally on the web400-l, and my below reply was directed-to and thus supposed to go there as well, but my ability to post is suspect on several other of the midrange lists. So for lack of my reply appearing there [via NNTP; in a timely manner], I am posting my reply here [also].

Possible. Any escape message sent back to the database I/O program, for a /native trigger/ [i.e. added with Add Physical File Trigger (ADDPFTRG)], or set the sqlstate\sqlcode [I believe SIGNAL statement is supported] in an SQL TRIGGER [i.e. added with CREATE TRIGGER ... BEFORE INSERT ...].

If the INSERT activity can be restricted to use of a VIEW, then there is also the WITH CHECK OPTION support, for which an SQL INSERT will diagnose a -161 sqlcode [msg SQL0161 "INSERT or UPDATE not allowed because a resulting row does not satisfy view definition ..."] when the WITH CHECK OPTION prevents the insert. I did not test to see [and thus log the symptom here,] what is issued for the /native I\O/ exception. For example, if the integer column INTCOL of the physical file MYFILE should never have the value of zero, and a VIEW named NOZEROES is the target of the INSERT activity, then the following could define the VIEW:

CREATE VIEW NOZEROES AS SELECT * FROM MYFILE WHERE INTCOL<>0

Note: This <ed: web400-l> hardly seems an appropriate forum for such a discussion however, so probably best to open a new topic on the midrange-l if any further discussion is of interest.?