3QSHGsxa

1CREATE TABLE dbo.ElementBase
2(
3    id integer NOT NULL,
4    parent_id integer NOT NULL,
5    element_type char(1) NOT NULL,
6
7    -- id key
8    CONSTRAINT [PK dbo.ElementBase id]
9        PRIMARY KEY CLUSTERED (id),
10
11    -- fk target
12    CONSTRAINT [UQ dbo.ElementBase id, element_type]
13        UNIQUE NONCLUSTERED (id, element_type),
14
15    -- self fk
16    CONSTRAINT [FK dbo.ElementBase parent_id id]
17        FOREIGN KEY (parent_id)
18        REFERENCES dbo.ElementBase (id),
19
20    -- valid element types
21    CONSTRAINT [CK dbo.ElementBase element_type]
22        CHECK (element_type IN ('a', 'b', 'c')),
23
24    -- for maintenance
25    INDEX [IX dbo.ElementBase parent_id] 
26        NONCLUSTERED (parent_id)
27); 
28     
29CREATE TABLE dbo.BData
30(
31    id integer NOT NULL,
32    element_type AS CONVERT(char(1), 'b') PERSISTED,
33
34    -- id key
35    CONSTRAINT [PK dbo.BData id]
36        PRIMARY KEY CLUSTERED (id),
37
38    -- fk to ElementBase
39    CONSTRAINT [FK Bdata ElementBase id, element_type]
40        FOREIGN KEY (id, element_type)
41        REFERENCES dbo.ElementBase (id, element_type)
42        ON DELETE CASCADE
43); 
44     
45CREATE TABLE dbo.AData
46(
47    id integer NOT NULL,
48    element_type AS CONVERT(char(1), 'a') PERSISTED,
49    b_element integer NULL,
50
51    -- id key
52    CONSTRAINT [PK dbo.AData id]
53        PRIMARY KEY CLUSTERED (id),
54
55    -- fk to ElementBase
56    CONSTRAINT [FK Adata ElementBase id, element_type]
57        FOREIGN KEY (id, element_type)
58        REFERENCES dbo.ElementBase (id, element_type)
59        ON DELETE NO ACTION,
60
61    -- fk to BData
62    CONSTRAINT [FK dbo.AData dbo.BData id b_element]
63        FOREIGN KEY (b_element)
64        REFERENCES dbo.BData (id)
65        ON DELETE SET NULL,
66
67    -- fk lookup
68    INDEX [IDX dbo.AData b_element] 
69        NONCLUSTERED (b_element),
70); 
71     
72CREATE TABLE dbo.CData
73(
74    id integer NOT NULL,
75    element_type AS CONVERT(char(1), 'c') PERSISTED,
76    b_element integer NOT NULL,
77
78    -- id key
79    CONSTRAINT [PK dbo.CData id]
80        PRIMARY KEY CLUSTERED (id),
81
82    -- fk to ElementBase
83    CONSTRAINT [FK Cdata ElementBase]
84        FOREIGN KEY (id, element_type)
85        REFERENCES dbo.ElementBase (id, element_type)
86        ON DELETE NO ACTION,
87
88    -- fk to BData
89    CONSTRAINT [FK dbo.CData dbo.BData b_element id]
90        FOREIGN KEY (b_element)
91        REFERENCES dbo.BData (id)
92        ON DELETE CASCADE,
93
94    -- fk lookup
95    INDEX [IDX dbo.CData b_element] 
96        NONCLUSTERED (b_element),
97); 
98     
99CREATE TABLE dbo.S
100(
101    s_id integer NOT NULL,
102    b_element integer NOT NULL,
103
104    -- id key
105    CONSTRAINT [PK dbo.S s_id]
106        PRIMARY KEY CLUSTERED (s_id),
107
108    -- fk to BData
109    CONSTRAINT [FK dbo.S dbo.BData b_element id]
110        FOREIGN KEY (b_element)
111        REFERENCES dbo.BData (id)
112        ON DELETE CASCADE,
113
114    -- fk lookup
115    INDEX [IDX dbo.S b_element] 
116        NONCLUSTERED (b_element),
117); 
118     
119CREATE OR ALTER TRIGGER [dbo.ElementBase IOD Cascade]
120ON dbo.ElementBase
121INSTEAD OF DELETE AS
122BEGIN
123    SET ROWCOUNT 0;
124    SET NOCOUNT ON;
125
126    -- Exit if no work to do
127    IF NOT EXISTS (SELECT * FROM Deleted) RETURN;
128
129    -- Holds ElementBase rows identified for deletion
130    CREATE TABLE #ToDelete 
131    (
132        id integer PRIMARY KEY,
133        element_type varchar(1) NOT NULL
134    );
135
136    -- Find all related ElementBase records
137    WITH R AS
138    (
139        -- Anchor: parent ElementBase rows
140        SELECT D.id, D.element_type
141        FROM Deleted AS D
142
143        UNION ALL
144
145        -- Recursive: children
146        SELECT EB.id, EB.element_type
147        FROM R
148        JOIN dbo.ElementBase AS EB
149            ON EB.parent_id = R.id
150            AND EB.id <> R.id
151    )
152    INSERT #ToDelete
153        (id, element_type)
154    SELECT DISTINCT 
155        R.id,
156        R.element_type
157    FROM R
158    OPTION (MAXRECURSION 0);
159
160    -- Delete related CData records (manual cascade)
161    DELETE CD
162    FROM #ToDelete AS TD
163    JOIN dbo.CData AS CD
164        ON CD.id = TD.id
165    WHERE
166        TD.element_type = 'c';
167
168    -- Delete related AData records (manual cascade)
169    DELETE AD
170    FROM #ToDelete AS TD
171    JOIN dbo.AData AS AD
172        ON AD.id = TD.id
173    WHERE
174        TD.element_type = 'a';
175
176    -- Delete ElementBase (BData, S records via cascade)
177    DELETE EB
178    FROM #ToDelete AS TD
179    JOIN dbo.ElementBase AS EB
180        ON EB.id = TD.id;
181END;