How can I work around a "derived field in view" error when attempting an INSERT/UPDATE?

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?
jb20147Asked:
Who is Participating?
 
_agx_Connect With a Mentor Commented:
Update MyTable
SET Column1="newdata2encrypt", Column2=null, Column3='something something'
Where RowID = 1234


Maybe you've done this already. But if you're going to update multiple columns, the INSTEAD OF trigger must include them too. Otherwise, they won't be updated.

      UPDATE orig
      SET        orig.Column1	  = i.Column1,
                    orig.Column2          = i.Column2,
                     etc....
      FROM     OrigTable orig INNER JOIN INSERTED i ON i.rowid  = orig.rowid 

Open in new window



UPDATE OrigTable
SET Column1 = i.Column1
FROM INSERTED i  INNER JOIN OrigTable d on d.rowid = i.rowid


If you prefer the ms sql flavor join, then use the alias in the UPDATE clause.

      UPDATE orig
      SET        orig.Column1        = i.Column1
      FROM     OrigTable orig INNER JOIN INSERTED i ON i.rowid  = orig.rowid

0
 
gdemariaCommented:

If we are talking best-practices, it doesn't seem to make sense to create a view that decrypts the values.  SQL injection attacks can pull that data just it was coming from the table itself.  Not sure what benefit you're getting.  

The preferred method would be to use a stored procedure to decrypt and crypt the data ONLY when you need that particular record's value.  If it's a password that you need to verify, you don't really ever need to decrypt it, you encrypt the user's entry and compare the encrypted values.


If you have to work with what you have, for whatever reason, you could create a trigger that replaces the default insert/update action.. within that trigger you perform your own insert/update using the encrypted values.

0
 
jb20147Author Commented:
Unfortunately, the data needs to be encrypted in SQL, but it needs to be displayed in numerous reports and pages throughout well more than 300 pages.

How would I pass the values from this Coldfusion query to a SQL Trigger?

MyTable = View which decrypts data in OrigTable

<cfquery datasource="mydatasource" name="mydata">
Update MyTable
SET Column1="newdata2encrypt", Column2=null, Column3='something something'
Where RowID = 1234
</cfquery>
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
_agx_Commented:
How would I pass the values from this Coldfusion query to a SQL Trigger?

You don't.  You do the query as usual and ms sql passes the values to your trigger.  Inside your trigger is where you do the changes.  It's all done with sql

http://msdn.microsoft.com/en-us/library/aa258254%28v=sql.80%29.aspx

CREATE TRIGGER MyTrigger ON MyTable FOR INSERT, UPDATE
AS
BEGIN
     // .... code to do changes here
END
GO
0
 
jb20147Author Commented:
A View can only be referenced by an Instead of Trigger, and using a Trigger on the OrigTable the the View references results in the same derived value within the View error.  Since I didn't include previously, this is the View I am using:

 ALTER view [dbo].[MyTable]
as Select *, convert(nvarchar, decryptbykeyautocert(cert_id('ColumnEncrypt1'), NULL, Column1)) AS 'Decrypt1',
convert(nvarchar,decryptbykeyautocert(cert_id('ColumnEncrypt1'),NULL,Column2)) AS 'Decrypt2'
       from OrigTable

0
 
gdemariaCommented:

> A View can only be referenced by an Instead of Trigger

Yes, that's the one you want.

http://msdn.microsoft.com/en-us/library/def01zh2(v=vs.80).aspx
0
 
_agx_Commented:
A View can only be referenced by an Instead of Trigger

Right, but it's still just a trigger with the same overall syntax.  If you look at the link it covers all types:
 FOR | AFTER | INSTEAD OF

http://msdn.microsoft.com/en-us/library/aa258254%28v=sql.80%29.aspx
0
 
jb20147Author Commented:
I did get this working for the INSTEAD OF INSERT, however, the INSTEAD OF UPDATE only works when the update affects 1 row.  SQL throws the following error if an update affects more than one row:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

INSTEAD OF UPDATE
AS
BEGIN
      SET NOCOUNT ON;

      UPDATE OrigTable
      SET Column1 = i.Column1
      FROM INSERTED i
      inner join OrigTable d on d.rowid = i.rowid

END
0
 
gdemariaCommented:
UPDATE OrigTable
   SET Column1 = i.Column1
FROM INSERTED i
where i.rowid = origTable.rowId


0
 
jb20147Author Commented:
Thanks for those possible solutions but they all return the same error.
0
 
gdemariaCommented:
hmmm, how about this one..


UPDATE OrigTable
   SET Column1 = sub.Column1
     , column2 = sub.column2
from (select i
  from inserted) sub
where sub.rowId = origTable.rowId
0
 
_agx_Commented:
Maybe you're doing something different than the sample code? Can you post the exact code you're using?

--- sample table
CREATE TABLE MyTable (
	id int identity,
	Column1 varchar(100),
	OtherColumn varchar(100)
)
GO

--- sample view
CREATE VIEW MyView
AS
SELECT	ID, OtherColumn, Column1 
FROM	MyTable
GO


--- sample trigger
CREATE TRIGGER MyTrigger ON MyView INSTEAD OF UPDATE
AS
BEGIN
      SET NOCOUNT ON;

	  -- append a string to show it was modified by the trigger
      UPDATE orig
	  SET    orig.OtherColumn = i.OtherColumn,
			 orig.Column1	  = i.Column1 +' modified by trigger'
      FROM   MyTable orig INNER JOIN INSERTED i ON i.ID = orig.ID

      SET NOCOUNT OFF;
END
GO

--- insert sample rows
INSERT INTO MyTable (OtherColumn, Column1) 
SELECT 'a1', 'a2' UNION ALL
SELECT 'b1', 'b2' UNION ALL
SELECT 'c1', 'c2' 
GO

--- show values before
SELECT	* FROM MyView
GO

--- test update
UPDATE  MyView
SET     OtherColumn = 'changed value', 
		Column1 = 'changed value'
WHERE   ID = 2
GO

--- show values AFTER
SELECT	* FROM MyView
GO

Open in new window


0
 
_agx_Commented:
Btw: The sample code above was tested w/MS SQL 2005
0
 
gdemariaCommented:

no recognition - so sad  - I only gave you the approach first and gave you the code sample first

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.