MSSql Trigger on Update Insert (Updatecounter)

sql-server, triggers

Solution

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

Problem

I have a table named Table1 with two fields Systemname and Updatecount. Each insert with Systemname "SAP" should set the Updatecount to 1 (initial value). If the Field Systemname gets an Update with the defined Value "SAP", the Field Updatecount should be increased by 1. How can i define the trigger ?

Original source