更新触发器工作，但无法插入。

I have created a trigger which handles updates, and works well. But I am battling to work out how to handle Inserts.

我已经创建了一个触发器来处理更新，并且工作得很好。但是我正在努力解决如何处理插入的问题。

This is my current trigger:

这是我现在的触发器:

CREATE TRIGGER tr_PersonInCareSupportNeeds_History
ON PersonInCareSupportNeeds
FOR UPDATE
AS
BEGIN

       INSERT INTO [dbo].[PersonInCareSupportNeeds_History]
       ([PersonInCareSupportNeedsID], [EventDate], [EventUser], [ChangedColumn], [PreviousValue], [NewValue])

       SELECT i.[PersonInCareSupportNeedsID], GETDATE(), i.[LastUpdateUser], 'StartDate', CAST(d.[StartDate] AS VARCHAR), CAST(i.[StartDate] AS VARCHAR)
       FROM PersonInCareSupportNeeds I INNER JOIN Deleted D
       ON d.PersonInCareSupportNeedsID = I.PersonInCareSupportNeedsID
       WHERE d.[StartDate] <> i.[StartDate]

       UNION
       -- new values
       SELECT i.[PersonInCareSupportNeedsID], GETDATE(), i.[LastUpdateUser], 'EndDate', CAST(d.[EndDate] AS VARCHAR), CAST(i.[EndDate] AS VARCHAR)
       FROM PersonInCareSupportNeeds I INNER JOIN DELETED D
       ON d.PersonInCareSupportNeedsID = I.PersonInCareSupportNeedsID
       WHERE d.[EndDate] <> i.[EndDate]


END

How can I change this to handle INSERTS as well. I will also hadd a column to handle the action type 'Updated' or 'Inserted'.

我怎样才能改变这个来处理插入。我还将使用一个列来处理“更新”或“插入”的操作类型。

1 个解决方案

#1

Funny, but I just happened to be playing with this:

有趣，但我只是碰巧在玩这个:

create trigger dbo.Things_Log on dbo.Things after Delete, Insert, Update as

  declare @Now as DateTimeOffset = SysDateTimeOffset();

  -- Determine the action that fired the trigger.
  declare @Action VarChar(6) =
    case
      when exists ( select 42 from inserted ) and exists ( select 42 from deleted ) then 'update'
      when exists ( select 42 from inserted ) then 'insert'
      when exists ( select 42 from deleted ) then 'delete'
      else NULL end;
  if @Action is NULL
    return;

  -- Assign a unique value to group the log rows for this trigger firing.
  declare @TriggerId as Int;
  update TriggerIds
    set @TriggerId = TriggerId += 1;

  -- Log the data.
  if @Action in ( 'delete', 'update' )
    insert into ThingsLog
      select @Action + '-deleted', @TriggerId, @Now, dbo.OriginalLoginName(), ThingId, ThingName
        from deleted;
  if @Action in ( 'insert', 'update' )
    insert into ThingsLog
      select @Action + '-inserted', @TriggerId, @Now, dbo.OriginalLoginName(), ThingId, ThingName
        from inserted;
go
-- Logging triggers should always fire last.
execute sp_settriggerorder @triggername = 'dbo.Things_Log', @order = 'Last', @stmttype = 'DELETE';
execute sp_settriggerorder @triggername = 'dbo.Things_Log', @order = 'Last', @stmttype = 'INSERT';
execute sp_settriggerorder @triggername = 'dbo.Things_Log', @order = 'Last', @stmttype = 'UPDATE';
go

The context:

背景:

create function [dbo].[OriginalLoginName]()
  returns NVarChar(128)
  as
  begin
  -- Returns the original login used to create the current session: Domain\username or sqlusername.
  --   This function is not affected by impersonation.
  --   Requires granting execute access to [public] and represents a diminutive security hole.
  declare @Result as NVarChar(128);
  select @Result = original_login_name
    from sys.dm_exec_sessions
    where session_id = @@SPID;
  return @Result;
  end;
go

CREATE TABLE [dbo].[Things](
    [ThingId] [int] IDENTITY(1,1) NOT NULL,
    [ThingName] [varchar](16) NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[ThingsLog](
    [ThingsLogId] [int] IDENTITY(1,1) NOT NULL,
    [Action] [varchar](16) NOT NULL,
    [TriggerId] [int] NOT NULL,
    [TriggerTime] [datetimeoffset](7) NOT NULL,
    [OriginalLoginName] [nvarchar](128) NOT NULL,
    [ThingId] [int] NOT NULL,
    [ThingName] [varchar](16) NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[TriggerIds](
    [TriggerId] [int] NULL
) ON [PRIMARY]

GO
insert into dbo.TriggerIds ( TriggerId ) values ( 0 );

Logging triggers should be configured to fire last. This prevents logging actions that may be subsequently rolled back by other triggers. For bonus points, a query that can report logging triggers that are not configured to fire last (assuming you have a consistent naming convention, e.g. TableName_Log, for the triggers):

日志触发器应该被配置为最后触发。这可以防止可能随后被其他触发器回滚的日志记录操作。对于额外的点，可以报告未配置为最后触发的日志触发器的查询(假设您有一个一致的命名约定，例如:TableName_Log，用于触发器):

select PO.name as TableName, O.name as TriggerName, TE.type_desc,
  case when O.name like PO.name + '_Log%' then 1 else 0 end as LoggingTrigger,
  case when O.name like PO.name + '_Log%' and TE.is_last = 0 then 1 else 0 end as Misconfigured,
  '' as [-], PO.type_desc as TableType, T.is_disabled, TE.is_first, TE.is_last, T.is_instead_of_trigger
  from sys.objects as O inner join
    sys.triggers as T on T.object_id = O.object_id inner join
    sys.objects as PO on PO.object_id = T.parent_id inner join
    sys.trigger_events as TE on TE.object_id = T.object_id
  where
    PO.type = 'U' and -- User table.
    T.parent_class = 1 and -- Object or column trigger.
    T.is_disabled = 0 and -- Is not disabled.
    T.is_instead_of_trigger = 0 -- AFTER, not INSTEAD OF, trigger.
  order by PO.name, O.name, TE.type_desc;

It can be incorporated in a stored procedure that corrects the firing order of logging triggers.

它可以被合并到一个存储过程中，以纠正日志触发器的触发顺序。

#1