Demo for trigger vs. default values #1

TriggervsDefault1.sql

USE Demo;
SET NOCOUNT ON;
SET STATISTICS TIME, IO OFF;
/* Just to prove there's no shenanigans */
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
GO
DROP TABLE IF EXISTS triggertest;
DROP TABLE IF EXISTS defaulttest;
CREATE TABLE triggertest
(id           INT IDENTITY(1, 1),
 Home         INT NOT NULL
                  DEFAULT 0,
 Away         INT NOT NULL
                  DEFAULT 0,
 LastModified DATETIMEOFFSET NULL
);
CREATE TABLE defaulttest
(id           INT IDENTITY(1, 1),
 Home         INT NOT NULL
                  DEFAULT 0,
 Away         INT NOT NULL
                  DEFAULT 0,
 LastModified DATETIMEOFFSET DEFAULT SYSDATETIMEOFFSET()
);
GO

CREATE OR ALTER TRIGGER tr_triggertest ON [triggertest]
AFTER INSERT, UPDATE
AS
UPDATE triggertest
  SET
      LastModified = SYSDATETIMEOFFSET()
WHERE [id] IN
(
    SELECT [id]
    FROM inserted
);
GO

SET STATISTICS TIME, IO ON;
INSERT INTO triggertest(Home,Away)
VALUES(17,21);
INSERT INTO defaulttest(Home,Away)
VALUES(17,21);
SET STATISTICS TIME, IO OFF;
select * from triggertest;
select * from defaulttest;