Call now: (800) 766-1884  



 Home


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA



 Articles
 Services
 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

AS

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

INSTEAD OF INSERT

AS

SET NOCOUNT ON

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

INSTEAD OF UPDATE

AS

SET NOCOUNT ON

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

 http://www.rampant-books.com/book_2005_2_sql_server_external_procedures.htm
 

 

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