
Use FlowingExampleWithTriggers
Go

-- step  1. delete procedures
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[spMatchInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[spMatchInsert]
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[spAddToCacheTable]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[spAddToCacheTable]
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[spGetSeparator]') and OBJECTPROPERTY(id, N'IsScalarFunction') = 1)
DROP FUNCTION [dbo].[spGetSeparator]
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[spGetVersion]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].spGetVersion
GO

-- step  2. reload assemblies

ALTER DATABASE FlowingExampleWithTriggers SET TRUSTWORTHY ON;

IF EXISTS (
    SELECT [name] 
    FROM sys.assemblies 
    WHERE [name] = N'SqlServerDataladderIntegration')
 
    BEGIN
        DROP ASSEMBLY SqlServerDataladderIntegration
        CREATE ASSEMBLY SqlServerDataladderIntegration
        FROM 'D:\Delete\SqlServerDataladderIntegration.dll'		
        WITH PERMISSION_SET = UNSAFE ;
    END
ELSE
    BEGIN
        CREATE ASSEMBLY SqlServerDataladderIntegration
        FROM 'D:\Delete\SqlServerDataladderIntegration.dll'		
        WITH PERMISSION_SET = UNSAFE ;
    END
Go

-- step  3. create procedures
CREATE PROCEDURE [dbo].spMatchInsert
      @parameter1 nvarchar(max),
	  @parameter2 bigint,
	  @parameter3 nvarchar(max)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME SqlServerDataladderIntegration.StoredProcedures.spMatchAndInsertInCache
Go

CREATE PROCEDURE [dbo].spAddToCacheTable
      @parameter1 nvarchar(max),
	  @parameter2 nvarchar(max)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME SqlServerDataladderIntegration.StoredProcedures.spAddRecordToCache
Go

CREATE FUNCTION [dbo].[spGetSeparator]()
RETURNS 
   nvarchar(max) 
WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME SqlServerDataladderIntegration.UserDefinedFunctions.spGetSeparator
Go

CREATE PROCEDURE [dbo].spGetVersion
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME SqlServerDataladderIntegration.StoredProcedures.spGetVersion
Go