如何在 SQL Server 上的触发器中编辑 INSERT 的值？

Question

我有表 Tb，

ID | Name   | Desc
-------------------------
 1 | Sample | sample desc

我想在 INSERT 上创建一个触发器，它将更改插入 DES 的值，例如：

INSERT INTO Tb(Name, Desc) VALUES ('x', 'y')

将导致

ID | Name   | Desc
-------------------------
 1 | Sample | sample desc
 2 | x      | Y edited

在上面的示例中，我得到了插入的 Desc 值将其更改为大写并在末尾添加了 edited。

这就是我所需要的，获取正在插入的 DES 并修改它。

我怎样才能做到这一点？

插入后更新处理是否更好？或者用 INSTEAD OF INSERT 制作一个触发器并在每次表结构发生变化时修改它？

Answer 1

使用后插入触发器。从 inserted 伪表连接到主键上的 Tb。然后更新 desc 的值。类似于：（但可能无法编译）

CREATE TRIGGER TbFixTb_Trg 
ON  Tb  
AFTER INSERT 
AS  
BEGIN 
    UPDATE Tb
    SET DESC = SomeTransformationOf(i.DESC)
    FROM Tb
    INNER JOIN inserted i on i.Id = Tb.Id
END  
GO

此触发器在插入发生之后、insert 语句完成之前发生。因此，新的、不正确的值已放置在目标表中。此触发器不需要随着列的添加、删除等而更改。

警告完整性约束在后触发器触发之前强制执行。因此，您无法施加检查约束来强制执行 DESC 的正确形式。因为这会导致语句在触发器有机会修复任何内容之前失败。 （在依赖之前请仔细检查这一段。我已经有一段时间没有写触发器了。）

Answer 2

我不确定您将从哪里获得 desc 的实际新值，但我假设您是从另一个表或其他表中获取它。但您可能有理由想要这样做，所以下面是我将如何进行的示例。

您想要的称为 INSTEAD OF INSERT 触发器，它会触发而不是使用您提供的每个逻辑在表上插入。

CREATE TRIGGER trgUpdateDesc
ON  Tb 
INSTEAD OF INSERT
AS 
BEGIN
    SET NOCOUNT ON;
    INSERT INTO Tb (Name, [Desc])
    SELECT Name, [Desc] + 'edited'
    FROM inserted
END 
GO

我已经对“编辑”一词进行了硬编码，因为我不确定您想要在哪里获取该值，但您可以轻松地将其替换为变量或另一个表中的值。

哦，还要确保将 [] 放在 Desc 周围，因为它是 sql server 中的关键字（代表降序）

希望有帮助！

编辑：

如果您想让它更加健壮，以便它不那么依赖于表结构，您可以使用 AFTER INSERT 触发器来像这样更新该字段。

CREATE TRIGGER [dbo].[trgUpdateDesc]
   ON  [dbo].[Tb] 
   AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;
    UPDATE Tb
    SET [Desc] = UPPER(inserted.[Desc]) +  ' Edited'
    FROM inserted INNER JOIN Tb On inserted.id = Tb.id
END 

Answer 3

临时表可以帮助使用 INSTEAD OF INSERT 触发器，同时避免显式列出所有不相关的表列：

CREATE TRIGGER trgUpdateDesc
    ON Tb 
    INSTEAD OF INSERT
AS 
BEGIN
    SET NOCOUNT ON;
    select * into #tmp from inserted;
    UPDATE #tmp SET Desc = Desc + 'edited' --where ...;
    insert into Tb select * from #tmp;
    drop table #tmp;
END 

将新列添加到表中时不需要修复此代码。

但要注意一些额外的临时表的开销。

另请注意，SQL Server 触发器对批量 DML 操作触发一次，并且必须正确处理多个行插入。

Answer 4

您可能想查看INSTEAD OF 触发器。

CREATE TRIGGER Tb_InsteadTrigger on Tb
INSTEAD OF INSERT
AS
BEGIN
...

这将允许您在数据进入表之前对其进行操作。触发器负责将数据插入到表中。

Answer 5

不要使用 IDENTITY，而使用 UNIQUEIDENTIFIER 和 NEWID()：

CREATE TRIGGER MyTrigger ON MyTable INSTEAD OF INSERT AS BEGIN
; select * into #MyTriggertTempTable from inserted
; update #MyTriggertTempTable set ID=NEWID() where ID is null
; insert into MyTable select * from #MyTriggertTempTable
END

Answer 6

您可以使用触发器在 INSERT 之后伪更新记录，但请注意，您实际上是在更新记录，而不是替换 INSERT 之前的值。

CREATE TRIGGER [MISNT].[MyTriggerToReplaceColumnValueOnINSERT]
  ON [dbo].[myTableThatIsDoingTheINSERT]
  AFTER INSERT   --I prefer AFTER as it really is about after the fact!
AS
BEGIN
    UPDATE upd
    SET upd.ColA = 'some value'
    FROM [dbo].[myTableThatIsDoingTheINSERT] AS upd
        INNER JOIN inserted AS i ON i.ID = upd.ID
    WHERE upd.COLB IS NULL --optional WHERE if you want to only affect certain records
END
GO