using trigger to count how many times of a row is updated or inserted

Kai Yang 166 Reputation points
2021-08-19T20:00:01.427+00:00

Hello List,
I was asked to add a column (N_count int) in a table. It will record how many times that each row is inserted or updated in the table.
I wrote a trigger to do this, but it has a bug in it. I don't know to fix it. Could you help me?
Thank you.

Create trigger [PMDB].[TG_count]
on [PMDB].[Alias_A] after insert, update
as
begin
if update(TableName) or update(PK) or update(FieldName) or exists (select * from deleted)
set N_count = N_count + 1
end

Msg 102, Level 15, State 1, Procedure TG_count, Line 6 [Batch Start Line 0]
Incorrect syntax near '='.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,560 questions
0 comments No comments
{count} votes

Accepted answer
  1. Tom Phillips 17,716 Reputation points
    2021-08-19T20:24:53.56+00:00

    You are missing the update statement.

    Create trigger [PMDB].[TG_count]
    on [PMDB].[Alias_A] after insert, update
    as
    begin
    if update(TableName) or update(PK) or update(FieldName) or exists (select * from deleted)
    BEGIN
        UPDATE a
            set N_count = N_count + 1
        FROM deleted d
            INNER JOIN [PMDB].[Alias_A] a
            ON a.PK = d.PK
    END 
    end
    

0 additional answers

Sort by: Most helpful