Database-Triggered Recalculation

It is possible to set up SQL Server databases so that they will inform Resolver One about changes to specific tables; this feature is extremely useful when writing spreadsheets which track live data.

Instructions for installing the SQLServerTrigger assembly follow. We are currently trying to gauge interest in implementing this feature for other database systems; please comment below if you are interested in using this functionality with other systems.

Installing the SQLServerTrigger assembly

  • Locate SQLServerTrigger in the Scripts subfolder of your Resolver One install.
  • Copy SQLServerTrigger.dll to a machine running SQL Server, and note its new location.
  • Make sure the DB owner has the UNSAFE ASSEMBLY and the the VIEW SERVER STATE properties set to true by running the following commands against the master system database as the system administrator:
grant unsafe assembly to <login_name>
grant view server state to <login_name>
  • As the system administrator, make the database trustworthy:
ALTER DATABASE $(database_name) SET TRUSTWORTHY ON;
  • As the database owner, run the following commands on the database, replacing $(database_name), $(dll_path) and $(table_name) as appropriate:
if not exists(select 1 from sys.assemblies
where name = 'SQLServerTrigger')
    CREATE ASSEMBLY SQLServerTrigger FROM '$(dll_path)' WITH
           PERMISSION_SET = UNSAFE
GO

if exists(select 1 from sysobjects
where id = object_id('dbo.Resolver$(table_name)Trigger') and xtype = 'TA')
    drop trigger dbo.[Resolver$(table_name)Trigger]
GO

CREATE TRIGGER [Resolver$(table_name)Trigger] ON [dbo].[$(table_name)]
       AFTER  INSERT, DELETE, UPDATE AS
EXTERNAL NAME SQLServerTrigger.Triggers.SQLServerTrigger
GO

if exists(select 1 from sysobjects
where id = object_id('dbo.GetCurrentTableName') and xtype = 'P')
    drop procedure dbo.GetCurrentTableName
GO

CREATE PROCEDURE dbo.GetCurrentTableName
WITH EXECUTE AS OWNER
AS
BEGIN
    SELECT OBJECT_NAME(RESOURCE_ASSOCIATED_ENTITY_ID)
    FROM SYS.DM_TRAN_LOCKS
    WHERE REQUEST_SESSION_ID = @@SPID
        AND RESOURCE_TYPE = 'OBJECT'
END
GO

GRANT EXECUTE ON DBO.GETCURRENTTABLENAME TO PUBLIC
GO

Comments