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

 

Security purposes

 

Example: Preventing users from deleting historic data in table Products. Historic data is data from products that were discontinued.

 

CREATE TRIGGER ProductsDeleteMT ON Products

FOR DELETE

AS

/* * PREVENT DELETES IF PRODUCT IS DISCONTINUED */

IF ((SELECT COUNT(*) FROM inserted WHERE inserted.Discontinued=1)>0)

    BEGIN

        RAISERROR 44447 'The record can''t be deteted because this is historic data.'

        ROLLBACK TRANSACTION

    END

 

The code will check for deleted rows with the discontinued field set. If the user was trying to delete historic data an error flag will be set and an error message, the transaction will be rolled back to prevent the data from being deleted.

 

Example: Automating data encryption with a trigger.
 

For these examples the UDF for TEA encryption, namely UDFdecTEA is used, but using an XP would be the most appropriate in a production environment.

 

A test table will be necessary:

 

CREATE TABLE tblCrypt(secret nvarchar(50))

 

Let us fill the table with some data:

INSERT tblCrypt(secret) VALUES('Secret string 1')

INSERT tblCrypt(secret) VALUES('Secret string 2')

INSERT tblCrypt(secret) VALUES('Secret string 3')

 

An INSTEAD OF trigger can be used to force the inserted data to be encrypted before being inserted:

 

CREATE TRIGGER "tblCrypt_ITrig" ON tblCrypt

INSTEAD OF INSERT

AS

SET NOCOUNT ON

INSERT tblCrypt(secret)

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

 

An update trigger will also be necessary so that the updates will store data already encrypted:

 

CREATE TRIGGER "tblCrypt_UTrig" ON tblCrypt

INSTEAD OF UPDATE

AS

SET NOCOUNT ON

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

 

To view the data unencrypted, the UDF must be called with the password:

 

SELECT dbo.UDFdecTEA(secret, 'abc') FROM tblCrypt

 

It is better to use a view so that it can be reused and benefit from the execution plan:

 

CREATE VIEW dbo.VIEW_tblCrypt

AS

SELECT dbo.UDFdecTEA(secret, 'abc') as secret

FROM  dbo.tblCrypt

 

Using this view to see the data without encryption:

 

SELECT secret from VIEW_tblCrypt

 


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