MSSql Update Trigger (Updatecounter)

Question

I have a table called Table1 with two fields Systemname and Updatecount. Each insert with the system name "SAP" should set the Updatecount to 1 (initial value). If the System Name field receives an update with the specified SAP value, the Update Field value should be increased by 1.

How can I define a trigger?

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 

Answer 2

There is a good article on triggers here:

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

You need to create:

 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