baZV0c0d

1CREATE TABLE dbo.Device
2(
3    ID INT IDENTITY(1,1) NOT NULL,
4    Name VarChar(100) NOT NULL,
5    ParentDeviceID INT NULL UNIQUE,
6    ParentCombinerID INT NULL,
7    CONSTRAINT [PK_Device] PRIMARY KEY CLUSTERED 
8    (
9        [ID] ASC
10    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
11) ON [PRIMARY]
12
13ALTER TABLE [dbo].[Device]  WITH CHECK ADD  CONSTRAINT [FK_Device_Device] FOREIGN KEY([ParentDeviceID])
14REFERENCES [dbo].[Device] ([ID])
15GO
16
17ALTER TABLE [dbo].[Device]  WITH CHECK ADD  CONSTRAINT [FK_Combiner_Device] FOREIGN KEY([ParentCombinerID])
18REFERENCES [dbo].[Combiner] ([ID])
19GO
20
21ALTER TABLE [dbo].[Device]
22ADD CONSTRAINT CHK_Device CHECK (ParentDeviceID IS NULL OR ParentCombinerID IS NULL)
23GO 
24     
25CREATE TABLE dbo.Combiner
26(
27    ID INT IDENTITY(1,1) NOT NULL,
28    Name VarChar(100) NOT NULL,
29    ParentDeviceID INT NULL UNIQUE,
30    ParentCombinerID INT NULL,
31    CONSTRAINT [PK_Combiner] PRIMARY KEY CLUSTERED 
32    (
33        [ID] ASC
34    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
35) ON [PRIMARY]
36
37ALTER TABLE [dbo].[Combiner]  WITH CHECK ADD  CONSTRAINT [FK_Combiner_Device] FOREIGN KEY([ParentDeviceID])
38REFERENCES [dbo].[Device] ([ID])
39GO
40
41ALTER TABLE [dbo].[Combiner]  WITH CHECK ADD  CONSTRAINT [FK_Combiner_Combiner] FOREIGN KEY([ParentCombinerID])
42REFERENCES [dbo].[Combiner] ([ID])
43GO
44
45ALTER TABLE [dbo].[Combiner]
46ADD CONSTRAINT CHK_Combiner CHECK (ParentDeviceID IS NULL OR ParentCombinerID IS NULL)
47GO 
48     
49-- On the Insert/Update of a dbo.Combiner record a value stored on ParentDeviceID is not already on dbo.Device.ParentDeviceID
50CREATE TRIGGER dbo.CombinerCheck ON dbo.Combiner
51AFTER INSERT, UPDATE
52AS
53BEGIN
54    IF EXISTS
55    (
56        SELECT *
57        FROM INSERTED I
58            INNER JOIN Device D
59                ON I.ParentDeviceID = D.ParentDeviceID
60    )
61    BEGIN
62        RAISERROR ('This Combiner cannot be added since it''s parent Device is already a parent to a different Device.', 16, 1);
63        ROLLBACK TRANSACTION; --stops the Insert/Update
64        RETURN 
65    END
66END
67
68--On the insert/update of a dbo.Device record a value stored on ParentDeviceID is not already on dbo.Combiner.ParentDeviceID
69CREATE TRIGGER dbo.DeviceCheck ON dbo.Device
70AFTER INSERT, UPDATE
71AS
72BEGIN
73    IF EXISTS
74    (
75        SELECT *
76        FROM INSERTED I
77            INNER JOIN Combiner C
78                ON I.ParentDeviceID = C.ParentDeviceID
79    )
80    BEGIN
81        RAISERROR ('This Device cannot be added since it''s parent Device is already a parent to a Combiner.', 16, 1);
82        ROLLBACK TRANSACTION; --stops the Insert/Update
83        RETURN
84    END
85END 
86     
87+----+------+----------------+------------------+
88| ID | Name | ParentDeviceID | ParentCombinerID |
89+----+------+----------------+------------------+
90|  1 | D1   | NULL           | NULL             |
91|  2 | D2   | 1              | NULL             |
92|  3 | D3   | 2              | NULL             |
93+----+------+----------------+------------------+ 
94     
95Device
96+----+------+----------------+------------------+
97| ID | Name | ParentDeviceID | ParentCombinerID |
98+----+------+----------------+------------------+
99|  1 | D1   | NULL           | NULL             |
100|  2 | D2   | NULL           | 1                |
101|  3 | D3   | NULL           | 1                |
102|  4 | D4   | NULL           | 1                |
103+----+------+----------------+------------------+
104
105Combiner
106+----+------+----------------+------------------+
107| ID | Name | ParentDeviceID | ParentCombinerID |
108+----+------+----------------+------------------+
109|  1 | C1   |              1 | NULL             |
110+----+------+----------------+------------------+ 
111     
112dbo.Device
113+----+------+----------------+------------------+
114| ID | Name | ParentDeviceID | ParentCombinerID |
115+----+------+----------------+------------------+
116|  1 | D1   | NULL           | NULL             |
117|  2 | D2   | 1              | NULL             |
118|  3 | D3   | NULL           | 1                |
119|  4 | D4   | NULL           | 1                |
120|  5 | D5   | NULL           | 1                |
121|  6 | D6   | NULL           | 2                |
122|  7 | D7   | NULL           | 2                |
123|  8 | D8   | NULL           | 2                |
124+----+------+----------------+------------------+
125
126dbo.Combiner
127+----+------+----------------+------------------+
128| ID | Name | ParentDeviceID | ParentCombinerID |
129+----+------+----------------+------------------+
130|  1 | C1   | 2              | NULL             |
131|  2 | C2   | NULL           | 1                |
132+----+------+----------------+------------------+