msGDe7Zi

1USE tempdb;
2IF OBJECT_ID(N'dbo.MainTable_Holding', N'U') IS NOT NULL
3DROP TABLE dbo.MainTable_Holding;
4IF OBJECT_ID(N'dbo.MainTable', N'U') IS NOT NULL
5DROP TABLE dbo.MainTable;
6	
7CREATE TABLE dbo.MainTable
8(
9    MainTableID int NOT NULL
10        CONSTRAINT PK_MainTable
11        PRIMARY KEY
12        CLUSTERED
13        IDENTITY(1,1)
14    , someval varchar(100) NOT NULL
15);
16	
17CREATE TABLE dbo.MainTable_Holding
18(
19    op_id int NOT NULL IDENTITY(1,1)
20    , MainTableID int NOT NULL
21    , someval varchar(100) NOT NULL
22    , op tinyint NOT NULL
23        CONSTRAINT CK_MainTable_Holding_op 
24        CHECK (op IN (1, 2, 3, 4))
25);
26
27CREATE CLUSTERED INDEX CX_MainTable_Holding
28ON dbo.MainTable_Holding (op_id);
29GO
30	
31CREATE TRIGGER MainTableHoldingTrigger
32ON dbo.MainTable
33AFTER INSERT, UPDATE, DELETE
34AS
35BEGIN
36    DECLARE @op_ins tinyint;
37    DECLARE @op_del tinyint;
38    SET @op_ins = 1;
39    SET @op_del = 2;
40    IF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
41    BEGIN
42        SET @op_ins = 3; --updated to
43        SET @op_del = 4; --updated from
44    END
45
46    INSERT INTO dbo.MainTable_Holding (MainTableID, someval, op)
47    SELECT d.MainTableID
48        , d.someval
49        , @op_del --delete
50    FROM deleted d;
51    INSERT INTO dbo.MainTable_Holding (MainTableID, someval, op)
52    SELECT i.MainTableID
53        , i.someval
54        , @op_ins --insert
55    FROM inserted i;
56END
57GO
58	
59INSERT INTO dbo.MainTable (someval)
60VALUES (REPLICATE(CHAR((CRYPT_GEN_RANDOM(1) % 26) + 65), 10));
61GO 10 --insert 10 rows
62	
63DELETE 
64FROM dbo.MainTable
65WHERE dbo.MainTable.MainTableID <= 5;
66	
67UPDATE dbo.MainTable
68SET dbo.MainTable.someval = 'XXXXX'
69WHERE dbo.MainTable.MainTableID = 6;
70	
71SELECT mth.MainTableID
72    , mth.someval
73    , Operation = CASE mth.op 
74        WHEN 1 THEN 'INSERT'
75        WHEN 2 THEN 'DELETE'
76        WHEN 3 THEN 'UPDATE TO'
77        WHEN 4 THEN 'UPDATE FROM'
78      END
79FROM dbo.MainTable_Holding mth;
80	
81dbo.MainTable_Holding