SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF not exists(SELECT sid FROM sys.database_principals WHERE lower(suser_sname(sid) )= 'Login' AND type = 'S')
 CREATE LOGIN Login WITH PASSWORD = 'jjkkk'
GO

IF not exists(SELECT sid FROM sys.database_principals WHERE lower(name) = 'user')
 CREATE USER VSUser FOR LOGIN Login
GO

EXEC sp_addrolemember N'db_owner', N'User'
GO



IF not exists(SELECT name FROM sys.symmetric_keys WHERE LOWER(name) = '##ms_databasemasterkey##')
 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'abcdef'



IF not exists(SELECT name FROM sys.asymmetric_keys WHERE lower(name) = 'asymmetric')
 CREATE ASYMMETRIC KEY asymmetric AUTHORIZATION user
  WITH ALGORITHM = RSA_1024
  ENCRYPTION BY PASSWORD ='FLXHRMSQ'



IF not exists(SELECT name FROM sys.symmetric_keys WHERE name = 'symmetric')
 CREATE SYMMETRIC KEY HRMS WITH ALGORITHM = DES
  ENCRYPTION BY ASYMMETRIC KEY asymmetric



IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[employee]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
 EXECUTE sp_rename N'employee', 'employee_base', 'OBJECT'

GO



EXEC AS USER='User'

OPEN MASTER KEY DECRYPTION BY PASSWORD='abcdef'

OPEN SYMMETRIC KEY symmetric

DECRYPTION BY ASYMMETRIC KEY asymmetric WITH PASSWORD='FLXHRMSQ'



IF not exists(SELECT name FROM syscolumns where id = object_id(N'employee_base') and lower(name) = 'salary_enc')
 ALTER TABLE employee_base ADD salary_enc varbinary (max) NULL
GO



UPDATE employee_base SET salary_enc = EncryptByKey(Key_GUID('symmetric'), cast(salary as varbinary(max)))
GO



IF exists (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[employee]'))
 DROP VIEW [dbo].[employee]

GO



CREATE VIEW [dbo].[employee] AS
SELECT fname,lname,id, salary FROM dbo.employee_base

GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_I_employee]') and xtype = 'TR')
   DROP TRIGGER [dbo].[T_I_employee]
GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_U_employee]') and xtype = 'TR')
   DROP TRIGGER [dbo].[T_U_employee]
GO



CREATE TRIGGER [T_I_employee]
   ON  [dbo].[employee]
   INSTEAD OF INSERT
AS 
BEGIN
 SET NOCOUNT ON;

 INSERT INTO employee_base
 SELECT fname,lname,id, encryptbykey(key_guid('symmetric'), cast(isnull(salary,0) as varbinary(max))) AS salary_enc
 FROM inserted
END

GO



CREATE TRIGGER [T_U_employee]
   ON  [dbo].[employee]
   INSTEAD OF UPDATE
AS 
BEGIN
 SET NOCOUNT ON;

 DECLARE @lcsql VARCHAR(max), @lddate DATETIME

 SET @lcsql = ''

 SELECT * INTO #inserted_tmp FROM inserted
 CREATE INDEX inserted_tmp ON #inserted_tmp (id)

 IF UPDATE(fname)
  SET @lcsql = @lcsql + ' employee_base.fname = #inserted_tmp.fname,'

 IF UPDATE(lname)
  SET @lcsql = @lcsql + ' employee_base.lname = #inserted_tmp.lname,'

IF UPDATE(id)
  SET @lcsql = @lcsql + ' employee_base.id = #inserted_tmp.id,'

IF UPDATE(salary)
  SET @lcsql = @lcsql + ' employee_base.salary_enc = EncryptByKey(Key_GUID(''symmetric''), cast(#inserted_tmp.salary as varbinary(max))),
        employee_base.salary = #inserted_tmp.salary,'

SET @lcsql = 'Update employee_base SET ' + left(@lcsql, len(@lcsql) -1) + ' from employee_base, #inserted_tmp where employee_base.id   =#inserted_tmp.id'

 EXEC (@lcsql)

END

GO