question

KaiYang-6525 avatar image
0 Votes"
KaiYang-6525 asked ErlandSommarskog commented

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

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 '='.

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered ErlandSommarskog commented

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


· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I did some updating of the code:

Alter trigger [PMDB].[TG_count]
on [PMDB].[Alias_A] after insert, update
as
begin --list all fields name below
if update(P_ID)
or update(AliasType)
or update(AliasLabel)
or update(OS_ID)
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.ID = d.ID --ID is PK
END
end

Msg 209, Level 16, State 1, Procedure TG_count, Line 12 [Batch Start Line 0]
Ambiguous column name 'N_count'.
I did try to modify the code of "set N_count = N_count + 1" into "set Alias_A.N_count = Alias_A.N_count + 1", but still get error message:
Msg 4104, Level 16, State 1, Procedure TG_count, Line 11 [Batch Start Line 0]
The multi-part identifier "Alias_A.N_count" could not be bound.

I don't understand what that mean of the message and how to fix it. Please help
Thank you



0 Votes 0 ·

set a.N_count = ISNULL(a.N_count, 0) + 1

1 Vote 1 ·

After review the table, I think the current value of N_count is null. Should I initial set the N_count = 0? otherwise, 1+ null = null?

0 Votes 0 ·

I did try to modify the code of "set N_count = N_count + 1" into "set Alias_A.N_count = Alias_A.N_count + 1", but still get error message:
Msg 4104, Level 16, State 1, Procedure TG_count, Line 11 [Batch Start Line 0]
The multi-part identifier "Alias_A.N_count" could not be bound.

I don't understand what that mean of the message and how to fix it. Please help

You cannot have an alias on the left side of SET. Since those columns are from the target table, there is no ambiguity.

Another note: I don't know why Tom used deleted in the trigger. I would have used inserted instead. Both works, but there is little point to have the trigger FOR INSERT when the trigger code looks at deleted, as this table is always empty on INSERT.

And, yes, you need some initialisation. I would make the column NOT NULL with a default value of 0 (in which case I would use inserted and not deleted.)

0 Votes 0 ·