Need a trigger which tracks every inserted or updated record

Transact-SQL
1

Hi folks,

I'm new to triggers. I'm looking to create a trigger which will track every account which has been created through an INSERT or changed through an UPDATE. To do so, I created this:

Create Trigger [dbo].[TrackAccountChanges] on [dbo].[Clients] After Insert, Update AS
BEGIN
DECLARE @Account int
SET NOCOUNT ON;
Select @Account = Account from inserted
BEGIN
Insert Into AccountChanges Select CURRENT_TIMESTAMP, @Account
End
End

But if I run, say, "Update clients set Active = 1 where Account in (1, 2, 3)" which affects 3 accounts, the trigger result appearing in the AccountChanges table is:

ID EventDateTime Account
105760 2021-05-28 10:48:17.7900000 1

when what I'm looking for is a trigger which will show all updated records, i.e.:

ID EventDateTime Account
105760 2021-05-28 10:48:17.7900000 1
105760 2021-05-28 10:48:17.7900000 2
105760 2021-05-28 10:48:17.7900000 3

What am I missing? Thanks!

2 
Create Trigger [dbo].[TrackAccountChanges] 
on [dbo].[Clients] 
After Insert, Update 
AS
BEGIN
SET NOCOUNT ON;
Insert Into AccountChanges 
Select CURRENT_TIMESTAMP, Account
From inserted
END
3

Works like a charm. Thanks!

4

Have you considered temporal tables?

Temporal Tables - SQL Server | Microsoft Docs

5

Read the material that you provided on temporal tables. Powerful to say the least. But this goes way beyond the current requirement. I appreciate you pointing it out though.

6

Fair enough but be careful with what you've adopted. It unnecessarily and instantly duplicates data and does not track anything about deletes. It will also log data even if nothing in a row has changed (which can and does actually happen a lot more than people might think).

Also, Not that you need it to but it won't "stand up in court" and it won't pass an audit because it's way too neasy for someone to tamper with. Yep... I know... that's also way beyond the scope of what you need... but there's a reason why you asked for such a trigger and you need to be advised that it's way too easy for people to temporarily disable it or to change the audit table, etc, etc. Yep... they need the privs to do so... and there are usually too many with such privs.

7

Noted. Thanks Jeff.