Insert/Update Trigger Before Actions

Hi All, I'm reviewing the syntax/semantics for adding a BEFORE action statement to insert and update triggers. It looks like there is no way to pass in the values of the DML statement into a stored procedure if you use the BEFORE action statement. Has anyone tried this and may know a workaround that I am not aware of? Or is it just not possible? Thanks, Bob

I'm not in a place where I can check the manual properly, but considering that the BEFORE clause is executed only once and that the INSERT and specially the UPDATE can affect several rows of data your request doesn't sound reasonable. If you need to process something that depends on each row data you should use the FOR EACH ROW clause.... On Jan 23, 2018 20:49, "BOB KRUSE" <bob.kruse@marriott.com> wrote: > Hi All, > > I'm reviewing the syntax/semantics for adding a BEFORE action statement to > insert and update triggers. It looks like there is no way to pass in the > values of the DML statement into a stored procedure if you use the BEFORE > action statement. > > Has anyone tried this and may know a workaround that I am not aware of? Or > is > it just not possible? > > Thanks, > > Bob > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

Thanks, Fernando. I should further explain what the intended goal is in this exercise. We are taking an application-centered approach to check the data before it is populated. I'm trying to keep potentially sensitive data from being recorded in the physical/logical logs. I have a check on the incoming data via stored procedure code called with the FOR EACH ROW action statement that will generate a -746 error if the input data violates data policy. At this point, the data is in the physical/logical logs. Setting the table to RAW would take care of half this issue but would also negate a possible transaction recovery if necessary.

You lost me. How would the BEFORE help on that? You were assuming you could check all rows in that clause? Can't be done. However... Why do you need to prevent the sensitive information to reach the logical logs? Compliance/Security? From whom are you trying to secure the information? Have you considered encrypting the data on disk? Encryption at rest I mean.... Regards. On Tue, Jan 23, 2018 at 9:46 PM, BOB KRUSE <bob.kruse@marriott.com> wrote: > Thanks, Fernando. > > I should further explain what the intended goal is in this exercise. We are > taking an application-centered approach to check the data before it is > populated. > > I'm trying to keep potentially sensitive data from being recorded in the > physical/logical logs. I have a check on the incoming data via stored > procedure code called with the FOR EACH ROW action statement that will > generate a -746 error if the input data violates data policy. At this > point, > the data is in the physical/logical logs. > > Setting the table to RAW would take care of half this issue but would also > negate a possible transaction recovery if necessary. > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...

I would like to use a trigger to check the data BEFORE it gets recorded in the logical/physical logs. If the data violates the check I have coded in the stored procedure, an exception is raised. I am not trying to check a lot of rows, just the data in the insert/updated statement. If I can check the data before it is written to the physical/logical logs I make sure there is no residual sensitive data remains on the disk device, even if I do a rollback. I understand encryption is available for disk devices for data at rest.

Hi, Hmmm I was wondering... - have a view doing select * from the real table, users/apps are only permissioned to read/write the view. - have an INSTEAD OF trigger on the view https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/id s_sqs_0637.htm which calls a DBA procedure "CREATE DBA PROCEDURE" https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/id s_sqs_0456.htm - The DBA procedure runs with elevated (DBA) privileges - DBA-privileged UDRs https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/id s_sqs_0460.htm#ids_sqs_0460 - the DBA procedure checks if data passes policy and if so inserts/updates the real table (which is logged) with the data. Users/app ids would need to be granted execute on the DBA privileges procedure. Not tried this but interested to see how far you could get with this! Regards, David. > On 23 January 2018 at 21:46 BOB KRUSE <bob.kruse@marriott.com> wrote: > > > Thanks, Fernando. > > I should further explain what the intended goal is in this exercise. We are > taking an application-centered approach to check the data before it is > populated. > > I'm trying to keep potentially sensitive data from being recorded in the > physical/logical logs. I have a check on the incoming data via stored > procedure code called with the FOR EACH ROW action statement that will > generate a -746 error if the input data violates data policy. At this point, > the data is in the physical/logical logs. > > Setting the table to RAW would take care of half this issue but would also > negate a possible transaction recovery if necessary. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >