MSSql触发器：在更新插入时触发（更新计数器）

Question

我有一个名为Table1的表格，其中有两个字段Systemname和Updatecount。 每次Systemname为"SAP"的插入都应将Updatecount设置为1（初始值）。 如果字段Systemname得到定义值"SAP"的更新，则字段Updatecount应增加1。

如何定义触发器？

- user1242087

Answer 1

create trigger tr on Table1 for insert,update
as
begin
    if update(Systemname)
        update Table1
            set UpdateCount = (case when not exists(select * from deleted) then 1 else UpdateCount + 1 end)
        from Table1
        inner join inserted on Table1.[<YourPKField>] = inserted.[<YourPKField>]
        where inserted.Systemname = 'SAP'
end
GO

- muhmud

Answer 2

这里有一篇关于触发器的好文章：

http://www.codeproject.com/Articles/38808/Overview-of-SQL-Server-database-Triggers

您需要创建：

（Note: The original text is already in Chinese. This is just a proof of concept translation.)

CREATE TRIGGER [TRIGGER_ALTER_COUNT] ON [dbo].[tblTriggerExample] 
FOR INSERT, UPDATE
AS
BEGIN
 DECLARE @Var INT 
 SELECT @Var = COUNT(*) FROM INSERTED
 UPDATE [dbo].[tblTriggerExample] SET AlterCount = AlterCount + Var  
          ,LastUpdate = GETDATE()
    WHERE TransactionID = @TransID
 SELECT @Var = COUNT(*) FROM UPDATED WHERE SystemNAme = 'Var'
 UPDATE [dbo].[tblTriggerExample] SET AlterCount = AlterCount + @Var
          ,LastUpdate = GETDATE()
    WHERE TransactionID = @TransID
END

- Ian P

---

感谢您分享这篇文章的链接。 - callisto