CODEWITHSUNDEEP
sql-servertriggers
user1242087
user1242087

Reputation: 63

MSSql Trigger on Update Insert (Updatecounter)

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 ?

Upvotes: 6

Views: 11440

Answers (2)

Ian P
Ian P

Reputation: 1724

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

Upvotes: 3

muhmud
muhmud

Reputation: 4604

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

Upvotes: 5

Related Questions