How can I work around a "derived field in view" error when attempting an INSERT/UPDATE?
Posted on 2011-02-17
We are in the process of encrypting 2 columns in a SQL 2005 database table. We have encrypted the data and created a VIEW of the table which decrypts COLUMN1 and COLUMN2 allowing any existing SELECT statements in our Coldfusion 9 pages to function normally. However, when attempting to UPDATE or INSERT rows into the table we receive the following error because we are decrypting COLUMN1 and COLUMN2 within the view: “Update or insert of view or function 'MyTable' failed because it contains a derived or constant field.”
The only solution I have been able to come up with is to create a second VIEW for the INSERT and UPDATE statements with a trigger that encrypts COLUMN1 AND COLUMN2 if the value is not null for those columns, but this will require the modification of almost 300 files that contain these statements. I wanted to avoid updating an existing code. Does anyone have a solution that can be accomplished within SQL?