PLnubRDE

1CREATE TABLE dbo.SomeData
2(
3    ID int NOT NULL
4        CONSTRAINT PK_SomeData
5        PRIMARY KEY CLUSTERED
6        IDENTITY(1,1)
7    , SomeVal varchar(100) NOT NULL
8    , SomeDate datetime NOT NULL
9);
10	
11CREATE TABLE dbo.SomeData_Changes
12(
13    ChangeID bigint NOT NULL
14        CONSTRAINT PK_SomeData_Changes
15        PRIMARY KEY CLUSTERED
16        IDENTITY(1,1)
17    , ChangeGroup int NOT NULL
18    , ChangeDate datetime
19        CONSTRAINT DF_SomeData_Changes_ChangeDate
20        DEFAULT ((GETDATE()))
21    , OpType varchar(1) NOT NULL
22    , ID int NOT NULL
23    , SomeVal varchar(100) NOT NULL
24    , SomeDate datetime NOT NULL
25);
26	
27CREATE SEQUENCE dbo.SomeData_Changes_Group
28AS int
29START WITH 1
30INCREMENT BY 1
31NO CYCLE
32CACHE 1000;
33GO
34	
35CREATE TRIGGER SomeData_ChangeTracker
36ON dbo.SomeData
37AFTER INSERT, UPDATE, DELETE
38AS
39BEGIN
40    DECLARE @Group int;
41    SET @Group = NEXT VALUE FOR dbo.SomeData_Changes_Group;
42
43    IF EXISTS (
44        SELECT 1
45        FROM deleted
46        )
47    BEGIN
48        INSERT INTO dbo.SomeData_Changes (OpType, ChangeGroup, ID, SomeVal, SomeDate)
49        SELECT 'D'
50            , @Group
51            , d.ID
52            , d.SomeVal
53            , d.SomeDate
54        FROM deleted d
55    END
56
57    IF EXISTS (
58        SELECT 1
59        FROM inserted
60        )
61    BEGIN
62        INSERT INTO dbo.SomeData_Changes (OpType, ChangeGroup, ID, SomeVal, SomeDate)
63        SELECT 'I'
64            , @Group
65            , i.ID
66            , i.SomeVal
67            , i.SomeDate
68        FROM inserted i
69    END
70    PRINT N'In trigger';
71END
72GO
73	
74INSERT INTO dbo.SomeData (SomeVal, SomeDate)
75VALUES ('test row 1', GETDATE());
76
77SELECT *
78FROM dbo.SomeData_Changes;
79	
80UPDATE dbo.SomeData
81SET SomeVal = 'Test Row 1'
82WHERE ID = 1;
83
84SELECT *
85FROM dbo.SomeData_Changes;
86	
87UPDATE dbo.SomeData
88SET SomeVal = 'Test Row 1'
89WHERE ID = 1;
90
91SELECT *
92FROM dbo.SomeData_Changes;
93	
94ChangeGroup
95	
96DELETE 
97FROM dbo.SomeData
98WHERE ID = 1;
99
100SELECT *
101FROM dbo.SomeData_Changes;
102	
103ChangeGroup
104	
105IF OBJECT_ID(N'dbo.SomeData', N'U') IS NOT NULL
106BEGIN
107    DROP TRIGGER SomeData_ChangeTracker;
108    DROP TABLE dbo.SomeData;
109END
110IF OBJECT_ID(N'dbo.SomeData_Changes', N'U') IS NOT NULL
111BEGIN
112    DROP TABLE dbo.SomeData_Changes;
113    DROP SEQUENCE dbo.SomeData_Changes_Group;
114END
115GO
116	
117DELETE 
118FROM dbo.SomeData_Changes
119WHERE dbo.SomeData_Changes.ChangeDate < DATEADD(DAY, -5, GETDATE());