n0sya7qF

1USE Demo;
2SET NOCOUNT ON;
3SET STATISTICS TIME, IO OFF;
4/* Just to prove there's no shenanigans */
5DBCC FREEPROCCACHE;
6DBCC DROPCLEANBUFFERS;
7GO
8DROP TABLE IF EXISTS triggertest;
9DROP TABLE IF EXISTS defaulttest;
10CREATE TABLE triggertest
11(id           INT IDENTITY(1, 1),
12 Home         INT NOT NULL
13                  DEFAULT 0,
14 Away         INT NOT NULL
15                  DEFAULT 0,
16 LastModified DATETIMEOFFSET NULL
17);
18CREATE TABLE defaulttest
19(id           INT IDENTITY(1, 1),
20 Home         INT NOT NULL
21                  DEFAULT 0,
22 Away         INT NOT NULL
23                  DEFAULT 0,
24 LastModified DATETIMEOFFSET DEFAULT SYSDATETIMEOFFSET()
25);
26GO
27
28CREATE OR ALTER TRIGGER tr_triggertest ON [triggertest]
29AFTER INSERT, UPDATE
30AS
31UPDATE triggertest
32  SET
33      LastModified = SYSDATETIMEOFFSET()
34WHERE [id] IN
35(
36    SELECT [id]
37    FROM inserted
38);
39GO
40
41SET STATISTICS TIME, IO ON;
42INSERT INTO triggertest(Home,Away)
43VALUES(17,21);
44INSERT INTO defaulttest(Home,Away)
45VALUES(17,21);
46SET STATISTICS TIME, IO OFF;
47select * from triggertest;
48select * from defaulttest;