MSSQL触发器在列上更新

Question

我有2个表，我希望表1在表2中插入或更新一个触发器，但我不确定如何做。

表1：

CREATE TABLE [dbo].[DevicePorts](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [IsInUse] [bit] NOT NULL,
)

表2：

CREATE TABLE [dbo].[DevicePortActivities](
    [ID] [uniqueidentifier] NOT NULL,
    [StartTime] [datetimeoffset](7) NOT NULL,
    [EndTime] [datetimeoffset](7) NULL,
    [FK_DevicePortID] [int] NOT NULL FOREIGN KEY REFERENCES DevicePorts(ID),
)

触发器的开始：

CREATE TRIGGER PortInUse
   ON  DevicePorts
   AFTER UPDATE
AS BEGIN
    SET NOCOUNT ON;
    IF UPDATE (IsInUse) 
    BEGIN
        IF IsInUse = 1
        THEN
            INSERT INTO [dbo].[DevicePortActivities]
            (
                [ID]
                ,[StartTime]
                ,[EndTime]
                ,[FK_DevicePortID]
            )
            VALUES
            (
                NEWID(),
                SYSDATETIMEOFFSET(),
                null,
                <DevicePortID>
            )
        ELSE 
            UPDATE [dbo].[DevicePortActivities]
            SET EndTime = SYSDATETIMEOFFSET()
            WHERE FK_DevicePortID = <DevicePortID> AND EndTime is null
        END
    END 
END
GO

我要做的是在修改" isinuse"时，它应该将行插入"设备partactivities"或更新。

条件是，如果" isInuse"为true，则应插入记录，如果是错误，则应更新最后的记录，其中"末日"为null。

Answer 1

您需要将inserted视为 table 。我建议您查看MERGE（因为不同的行可能具有单个UPDATE应用的不同更改）。

类似：

CREATE TRIGGER PortInUse
   ON  DevicePorts
   AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    MERGE INTO [dbo].[DevicePortActivities] t
    USING (select i.ID,i.IsInUse as NewUse,d.IsInUse as OldUse
        from inserted i inner join deleted d on i.ID = d.ID) s
    ON
        t.FK_DevicePortID = s.ID
    WHEN MATCHED AND t.EndTime is null AND NewUse = 0 and OldUse = 1
    THEN UPDATE SET EndTime = SYSDATETIMEOFFSET()
    WHEN NOT MATCHED AND NewUse = 1 and OldUse = 0
    THEN INSERT ([ID]
                ,[StartTime]
                ,[EndTime]
                ,[FK_DevicePortID])
         VALUES (NEWID(),
                SYSDATETIMEOFFSET(),
                null,
                s.ID);
END

Answer 2

我找到了我的问题的解决方案

CREATE TRIGGER [dbo].[PortInUse]
   ON  [dbo].[DevicePorts]
   AFTER UPDATE
AS BEGIN
    SET NOCOUNT ON;
    IF UPDATE (IsInUse) 
    BEGIN
        DECLARE @IsInUse bit;
        DECLARE @PortID int;
        SELECT @IsInUse = i.IsInUse FROM inserted i; 
        SELECT @PortID = i.ID FROM inserted i; 
        IF (@IsInUse = 1)
            INSERT INTO [dbo].[DevicePortActivities]
            (
                [ID]
                ,[StartTime]
                ,[EndTime]
                ,[FK_DevicePortID]
            )
            VALUES
            (
                NEWID(),
                SYSDATETIMEOFFSET(),
                null,
                @PortID
            );
        ELSE 
            UPDATE [dbo].[DevicePortActivities]
            SET EndTime = SYSDATETIMEOFFSET()
            WHERE FK_DevicePortID = @PortID AND EndTime is null;
    END 
END

我不确定是否有更好的方法来执行此操作，但是它可以正常工作。