Microsoft SQL Server Specific Information

Schema Assist for Dependencies

To be documented

Trigger Invalidation with SQL Server

Connection properties:

CREATE DATABASE [heimdall]
GO

/*** create the trigger tracking table ***/
USE [heimdall]
CREATE TABLE [dbo].[table_change](
    [ts] [bigint] NULL,
    [name] [varchar](64) NOT NULL,
    CONSTRAINT [PK_table_change] PRIMARY KEY ([name]));

CREATE FUNCTION [dbo].[get_time_ms]() RETURNS BIGINT
AS
BEGIN
DECLARE @timems BIGINT
set @timems= (SELECT cast(DATEDIFF(s, '1970-01-01 00:00:00', GETUTCDATE()) as BIGINT)*1000+cast(DATEDIFF(ms,CONVERT(VARCHAR,GETUTCDATE(),120),GETUTCDATE()) as BIGINT))
return @timems
END

/*** Create the tracking procedure, which will be used by the triggers ***/
CREATE OR ALTER PROCEDURE [heimdall].[dbo].[track_changes] 
    @tablename NVARCHAR(128)
as
    SET NOCOUNT OFF
    UPDATE [heimdall].[dbo].[table_change] SET ts=[heimdall].[dbo].get_time_ms() WHERE name = @tablename
    IF @@ROWCOUNT != 0
        RETURN
    INSERT INTO [heimdall].[dbo].[table_change] VALUES ( dbo.get_time_ms(), @tablename )
GO

/*** trigger to apply to each table that needs trigger based eviction ***/
/**** for table vevo.dbo.Product ***/
USE [vevo2]; CREATE OR ALTER TRIGGER [dbo].[Product_changed]
    ON [vevo2].[dbo].[Product]
    AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    exec [heimdall].[dbo].[track_changes] 'vevo2.dbo.Product'
END
GO