下面是原始触发器:

CREATE TRIGGER trg_InsertUpdateDelete_CarSale
ON dbo.CarSale 
FOR INSERT, UPDATE, DELETE
AS
BEGIN 

    DECLARE @InsertedRow int = (Select Count(CarSale_ID) from inserted)
    DECLARE @DeletedRow int = (Select Count(CarSale_ID) from deleted)
    DECLARE @RowCount int = @DeletedRow
    DECLARE @OpStatus varchar(20)

    If @DeletedRow > 0 AND @InsertedRow > 0
        Set @OpStatus =  Updated 
    Else If @DeletedRow > 0
        Set @OpStatus =  Deleted 
    Else Begin
        Set @OpStatus =  Inserted 
        Set @RowCount = @InsertedRow
    End
      
    DECLARE @CarCondition varchar(100) =  excellent 
          
    INSERT INTO dbo.CarTables_Audit
       (TableName, CarCondition, TotalRowCount, OperationStatus)
    VALUES
      ( dbo.CarTable , @CarCondition, @RowCount, @OpStatus)
END

但是,这在如何处理<条码> 有条件<>/代码”方面似乎明显是错误的,问题本身也不明确。

It seems odd to have an audit table with a field like TotalRowCount for handling multiple rows, but only a single condition. Since SQL Server can batch up triggers from multiple sessions for performance, you should also expect to have times when the trigger runs several rows and different conditions, and therefore the audit table would lie. But nothing in the question right now tells us how you want to handle that situation.

我可能这样做:

CREATE TRIGGER trg_InsertUpdateDelete_CarSale
ON dbo.CarSale 
FOR INSERT, UPDATE, DELETE
AS
BEGIN 
    INSERT INTO dbo.CarTables_Audit
       (TableName, CarCondition, TotalRowCount, OperationStatus)      
    SELECT  dbo.CarTable , coalesce(i.Condition, d.Condition), count(CarSale_ID)
        , CASE WHEN count(d.CarSale_ID) = 0 THEN  Inserted 
               WHEN count(i.CarSale_ID) = 0 THEN  Deleted 
               ELSE  Updated  END
    FROM inserted i
    FULL JOIN deleted d on i.CarSale_ID = d.CarSale_ID
    GROUP BY coalesce(i.Condition, D.Condition)
END

而且,这似乎是错误的,因为你再说一遍:你可以坐在一起的多场会议,意思是,你可能有一些合法删除,而其他合法插入的条件相同,这将算作更新。

还有一次尝试:

CREATE TRIGGER trg_InsertUpdateDelete_CarSale
ON dbo.CarSale 
FOR INSERT, UPDATE, DELETE
AS
BEGIN 
    WITH PreGrouped as (
        -- First group individual sales and set condition and status for each   
        SELECT Coalesce(i.CarSale_ID, d.CarSale_ID) CarSale_ID, 
            coalesce(i.Condition, d.Condition) Condition,
            case when i.CarSale_ID IS NULL THEN  Deleted 
                 when d.CarSale_ID IS NULL THEN  Inserted 
                 else  Updated  END as OperationStatus           
        FROM inserted i
        FULL JOIN deleted d on i.CarSale_ID = d.CarSale_ID
        GROUP BY coalesce(i.CarSale_ID, d.CarSale_ID)
    )
    INSERT INTO dbo.CarTables_Audit
       (TableName, CarCondition, TotalRowCount, OperationStatus) 
    -- Then group by condition and status
    SELECT  dbo.CarSale , Condition, COUNT(CarSale_ID), OperationStatus
    FROM PreGrouped 
    GROUP BY Condition, OperationStatus
END

This looks like something I d be comfortable putting in an audit table. Now the only issue that remains if someone creates a sale and very quickly deletes it, so they are batched together for the trigger, it would count as an update. But I would find that set of events very strange and hard to produce.

值得注意的是,这有可能每当触发点火时在审计表中插入多个段落,但只有在确实存在多个批量的情况下。 我也假设,现在的定有违约值的定时栏,但我们只是重新插入该栏,让违约值做事。 确实很难在没有时间调查的情况下进行审计。