Call now: (800) 766-1884  


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA

 SQL Server Scripts
 Scripts Menu




  SQL Server Tips by Gama and Naughter


Using Views for input and output


There are two problems with using triggers to insert and update data:

Only one INSTEAD OF trigger is allowed for each DELETE, UPDATE or INSERT operations. Merging the code might be hard and prone to errors.


If the encryption is to be applied to an existing data structure it is possible that the existing tables already have triggers and some might have several AFTER triggers. There might too many changes to be made and the possibility of error grows very fast with the number of tables and triggers per table.


A better solution that leaves the existing data structure almost intact is to use one view for inserting data and another one for viewing the data. The view that shows the decrypted data is VIEW_tblCrypt, the view that will allow inserting data will be VIEW_tblCryptInsert:


CREATE VIEW dbo.VIEW_ tblCryptInsert


SELECT secret

FROM  dbo.tblCrypt


The view simply shows the encrypted data as it is stored in the table; a trigger on this view will cause it to encrypt inserted data:


CREATE TRIGGER "VIEW_tblCrypt_ITrig" ON VIEW_ tblCryptInsert




INSERT VIEW_ tblCryptInsert (secret)

SELECT dbo.UDFencTEA(secret, 'abc') FROM INSERTED


An update trigger on that view will make it possible to encrypt updated data:


CREATE TRIGGER "VIEW_tblCrypt_UTrig" ON VIEW_ tblCryptInsert




UPDATE tblCrypt SET secret= dbo.UDFencTEA(INSERTED.secret, 'abc') FROM INSERTED


Let us test the insert trigger:


INSERT VIEW_ tblCryptInsert (secret) VALUES('Test 1')


And the update trigger:


UPDATE VIEW_ tblCryptInsert SET secret='Test 2' WHERE secret='Test 1'


The above book excerpt is from:

Super SQL Server Systems
Turbocharge Database Performance with C++ External Procedures

ISBN: 0-9761573-2-2
Joseph Gama, P. J. Naughter


Burleson Consulting Remote DB Administration







Burleson is the America's Team

Note: The pages on this site were created as a support and training reference for use by our staff of DBA consultants.  If you find it confusing, please exit this page.

Errata?  SQL Server technology is changing and we strive to update our SQL Server support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:and include the URL for the page.

Burleson Consulting
SQL Server database support


Copyright 1996 -  2013 by Vaaltech Web Services. All rights reserved.

Hit Counter