A trigger program in my experience cannot return a value.
What you are looking for is a UDF ( user defined function )
in SQL. Then, instead of doing a read or chain, you do a fetch on a cursor that looks somewhat like this :
select a, b, c, yourudf(encryptedpassword), from file
A trigger doesn’t return data to any program that causes the trigger to fire. See this V5R4 short description of <a href=”http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/dbp/rbaforzahftrb1.htm”>How trigger programs work</a> and note that the two parms are both “Input”. Also this V5R4 discussion of <a href=”http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/dbp/rbaforzahftrc.htm”>Trigger buffer sections</a> links to field descriptions where the ‘New record’ buffer subfield is defined as “A copy of the record that is being inserted or updated in a physical file as a result of the change operation. The new record only applies to the insert or update operations.”
The direct implication is that it doesn’t exist for read operations… even if the buffer was ever returned.
To accomplish what you want (retrieve decrypted data from a file) you will need a SQL view with a UDF to that column to decrypt it. I had the same problem and the solution was create a UDF to call a subprocedure from a service program to decrypt the data, create a view and use that UDF to retrieve the data decrypted from the table.
To control access to decrypted data, the same subprocedure can make some validations over an authorization list or something like that.
To accomplish it, I’d probably just use the builtin ENCRYPT_xxx/DECRYPT_xxx scalar functions of SQL rather than code a new UDF(). Available in current releases. Might as well have IBM on the hook for it.
Tom, you are right but to add some code like control access over the view and the encryption key, the scalar function will not be enough.