为了一个日志系统,我想要以下的数据库架构:
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])
);
设计目标是为了允许大量的日志数据,因为更昂贵的字符串被分解到一个单独的表中。从语义上讲,这两个表形成一个由此视图定义的单一逻辑表:
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;
我现在想在视图上定义一个替代插入触发器,这是我的问题所在。我尝试了以下内容:
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
首先,第一个问题很明显,即第一个插入操作没有检查元组是否已在数据库中存在。如果是单个值插入,我知道如何处理,但在这里我必须考虑多行插入。
另一件我无法解释的事情是,即使第一个插入成功,触发器也不起作用。我会收到外键违规的提示,好像第一个插入实际上没有插入任何内容。
我认为这应该是一个常见的设置,因此可能有人有类似的示例代码吗?