Instead-of-insert trigger for multiple tables

Question

For a logging system I want to have the following database schema:

CREATE TABLE [dbo].[Categories] (
    [Id]       INT            IDENTITY (1, 1) NOT NULL,
    [App]      NVARCHAR (30)  NULL,
    [Source]   NVARCHAR (30)  NULL,
    [LogLevel] NVARCHAR (5)   NULL,
    [Logger]   NVARCHAR (120) NULL,
    CONSTRAINT [PK_Categories] PRIMARY KEY NONCLUSTERED ([Id] ASC),
    CONSTRAINT [UK_Categories] UNIQUE NONCLUSTERED ([App] ASC, [Source] ASC, [LogLevel] ASC, [Logger] ASC)
);

CREATE TABLE [dbo].[Occurences] (
    [PointInTime] BIGINT NOT NULL,
    [CategoryId]  INT    NOT NULL,
    [Noise]       INT    NOT NULL,
    CONSTRAINT [PK_Occurences] PRIMARY KEY CLUSTERED ([PointInTime] ASC, [CategoryId] ASC, [Noise] ASC),
    CONSTRAINT [FK_Category] FOREIGN KEY ([CategoryId]) REFERENCES [Categories] ([Id])
);

The design goal is to allow for a large amount of logging data as the more expensive strings are factored out in a separate table.

Semantically, both tables form a single logical table defined by this view:

CREATE VIEW [dbo].[HistoricLogEntries]
    AS SELECT o.PointInTime, o.Noise, c.App, c.[Source], c.LogLevel, c.Logger
    FROM Occurences o
    JOIN Categories c ON o.CategoryId = c.Id;

I'd now like to define an instead-of-insert-trigger on the view, which is where my troubles start. I have the following attempt:

CREATE TRIGGER InsteadTrigger on [dbo].[HistoricLogEntries]
INSTEAD OF INSERT
AS
BEGIN
    INSERT INTO Categories
    SELECT i.App, i.[Source], i.LogLevel, i.Logger
    FROM INSERTED i;

    INSERT INTO Occurences
    SELECT i.PointInTime, i.Noise, c.Id AS CategoryId
    FROM INSERTED i
    JOIN Categories c ON i.App = c.App AND i.[Source] = c.[Source] AND i.LogLevel = c.LogLevel AND i.Logger = c.Logger;
END

There is the obvious first problem that the first insert isn't checking whether the tuple is already in the database. I'd know how to do that in the case of a single value insert, but here I have to account for a multiple row insert.

Another thing that I can't explain is that the trigger doesn't even work in case the first insert succeeds. I get a foreign key violation - as if the first insert didn't actually insert anything.

I thought this should be a common setup, so maybe someone has some similar example code?

Answer 1

For SQL2008+ I would use MERGE statement to insert new categories:

MERGE dbo.Categories WITH (HOLDLOCK) AS c
USING INSERTED AS i ON i.App = c.App
AND i.[Source] = c.[Source]
AND i.LogLevel = c.LogLevel
AND i.Logger = c.Logger
WHEN NOT MATCHED BY TARGET THEN
    INSERT (App, [Source], LogLevel, Logger)
    VALUES (i.App, i.[Source], i.LogLevel, i.Logger);

I used HOLDLOCK table hint to prevent race condition:

[...] To prevent concurrent sessions from inserting data with the same key, an incompatible lock must be acquired to ensure only one session can read the key (my note: UK_Categories unique index in this case) and that lock must be held until the transaction completes.[...]

And to prevent FK errors:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Category". The conflict occurred in database "Test", table "dbo.Categories", column 'Id'.

I would add the the name of every column within second INSERT thus:

INSERT INTO Occurences (PointInTime, Noise, CategoryId)
SELECT i.PointInTime, i.Noise, c.Id AS CategoryId
FROM INSERTED i
JOIN Categories c ON i.App = c.App

The reason of these FK errors is an inconsistency within order of columns:

1) The order of column within CREATE TABLE is

   [PointInTime] BIGINT NOT NULL,
    [CategoryId]  INT    NOT NULL,
    [Noise]       INT    NOT NULL,

but

2) Within second insert the order of columns is different (see CategoryId vs Noise):

INSERT INTO Occurences
-- or INSERT INTO Occurences (PointInTime, CategoryId, Noise)
SELECT i.PointInTime, i.Noise, c.Id AS CategoryId
FROM ...

This is the my solution:

ALTER TRIGGER InsteadTrigger on [dbo].[HistoricLogEntries]
INSTEAD OF INSERT
AS
BEGIN       
    MERGE dbo.Categories WITH (HOLDLOCK) AS c
    USING INSERTED AS i ON i.App = c.App
    WHEN NOT MATCHED BY TARGET THEN
        INSERT (App)
        VALUES (i.App);

    INSERT INTO Occurences (PointInTime, Noise, CategoryId)
    SELECT i.PointInTime, i.Noise, c.Id AS CategoryId
    FROM INSERTED i
    JOIN Categories c ON i.App = c.App
END
GO