· 8 years ago · Feb 26, 2018, 08:16 AM
1--Table 'Areas'
2CREATE TABLE Areas (
3AreaID INT IDENTITY(1,1) PRIMARY KEY,
4Areaname VARCHAR(60) UNIQUE NOT NULL
5)
6
7--Table 'Workspaces'
8CREATE TABLE Workspaces (
9AreaID INT
10CONSTRAINT ck_a_areaid REFERENCES Areas(AreaID)
11ON DELETE CASCADE
12ON UPDATE NO ACTION,
13SurfaceID INT IDENTITY(1,1)
14CONSTRAINT ck_surfaceid CHECK (surfaceid > 0 AND surfaceid < 1001),
15Description VARCHAR(300) NOT NULL,
16CONSTRAINT ck_workspaces PRIMARY KEY (AreaID, SurfaceID)
17)
18
19AreaID SurfaceID
201 1
211 2
221 3
232 4
242 5
253 6
26Etc...
27
28AreaID SurfaceID
291 1
301 2
311 3
322 1
332 2
343 1
35Etc...
36
37Update Your_Table
38set SurfaceID = ( select max(isnull(SurfaceID,0))+1 as max
39 from Workspaces t
40 where t.AreaID = INSERTED.AreaID )
41
42CREATE TABLE testTbl
43(
44 AreaID INT,
45 SurfaceID INT, --we want this to be auto increment per specific AreaID
46 Dsc VARCHAR(60)NOT NULL
47)
48
49CREATE TRIGGER TRG
50ON testTbl
51INSTEAD OF INSERT
52
53AS
54
55DECLARE @sid INT
56DECLARE @iid INT
57DECLARE @dsc VARCHAR(60)
58
59SELECT @iid=AreaID FROM INSERTED
60SELECT @dsc=DSC FROM INSERTED
61
62--check if inserted AreaID exists in table -for setting SurfaceID
63IF NOT EXISTS (SELECT * FROM testTbl WHERE AreaID=@iid)
64SET @sid=1
65ELSE
66SET @sid=( SELECT MAX(T.SurfaceID)+1
67 FROM testTbl T
68 WHERE T.AreaID=@Iid
69 )
70
71INSERT INTO testTbl (AreaID,SurfaceID,Dsc)
72 VALUES (@iid,@sid,@dsc)
73
74INSERT INTO testTbl(AreaID,Dsc) VALUES (1,'V1');
75INSERT INTO testTbl(AreaID,Dsc) VALUES (1,'V2');
76INSERT INTO testTbl(AreaID,Dsc) VALUES (1,'V3');
77INSERT INTO testTbl(AreaID,Dsc) VALUES (2,'V4');
78INSERT INTO testTbl(AreaID,Dsc) VALUES (2,'V5');
79INSERT INTO testTbl(AreaID,Dsc) VALUES (2,'V6');
80INSERT INTO testTbl(AreaID,Dsc) VALUES (2,'V7');
81INSERT INTO testTbl(AreaID,Dsc) VALUES (3,'V8');
82INSERT INTO testTbl(AreaID,Dsc) VALUES (4,'V9');
83INSERT INTO testTbl(AreaID,Dsc) VALUES (4,'V10');
84INSERT INTO testTbl(AreaID,Dsc) VALUES (4,'V11');
85INSERT INTO testTbl(AreaID,Dsc) VALUES (4,'V12');
86
87SELECT * FROM testTbl
88
89AreaID SurfaceID Dsc
90 1 1 V1
91 1 2 V2
92 1 3 V3
93 2 1 V4
94 2 2 V5
95 2 3 V6
96 2 4 V7
97 3 1 V8
98 4 1 V9
99 4 2 V10
100 4 3 V11
101 4 4 V12
102
103CREATE TABLE Workspaces (
104 WorkspacesId int not null identity(1, 1) primary key,
105 AreaID INT,
106 Description VARCHAR(300) NOT NULL,
107 CONSTRAINT ck_a_areaid REFERENCES Areas(AreaID) ON DELETE CASCADE ON UPDATE NO ACTION,
108);
109
110select w.*, row_number() over (partition by areaId
111 order by WorkspaceId) as SurfaceId
112from Workspaces
113
114create table TestingTransactions (
115 id int identity,
116 transactionNo int null,
117 contract_id int not null,
118 Data1 varchar(10) null,
119 Data2 varchar(10) null
120);
121
122CREATE TRIGGER dbo.Trigger_TransactionNo_Integrity
123 ON dbo.TestingTransactions
124 INSTEAD OF INSERT
125AS
126BEGIN
127 -- SET NOCOUNT ON added to prevent extra result sets from
128 -- interfering with SELECT statements.
129 SET NOCOUNT ON;
130
131 -- Discard any incoming transactionNo's and ensure the correct one is used.
132 WITH trans
133 AS (SELECT F.*,
134 Row_number()
135 OVER (
136 ORDER BY contract_id) AS RowNum,
137 A.*
138 FROM inserted F
139 CROSS apply (SELECT Isnull(Max(transactionno), 0) AS
140 LastTransaction
141 FROM dbo.testingtransactions
142 WHERE contract_id = F.contract_id) A),
143 newtrans
144 AS (SELECT T.*,
145 NT.minrowforcontract,
146 ( 1 + lasttransaction + ( rownum - NT.minrowforcontract ) ) AS
147 NewTransactionNo
148 FROM trans t
149 CROSS apply (SELECT Min(rownum) AS MinRowForContract
150 FROM trans
151 WHERE T.contract_id = contract_id) NT)
152 INSERT INTO dbo.testingtransactions
153 SELECT Isnull(newtransactionno, 1) AS TransactionNo,
154 contract_id,
155 data1,
156 data2
157 FROM newtrans
158END
159GO
160
161delete from dbo.TestingTransactions
162insert into dbo.TestingTransactions (transactionNo, Contract_id, Data1)
163values (7,213123,'Blah')
164
165insert into dbo.TestingTransactions (transactionNo, Contract_id, Data2)
166values (7,333333,'Blah Blah')
167
168insert into dbo.TestingTransactions (transactionNo, Contract_id, Data1)
169values (333,333333,'Blah Blah')
170
171insert into dbo.TestingTransactions (transactionNo, Contract_id, Data2)
172select 333 ,333333,'Blah Blah' UNION All
173select 99999,44443,'Blah Blah' UNION All
174select 22, 44443 ,'1' UNION All
175select 29, 44443 ,'2' UNION All
176select 1, 44443 ,'3'
177
178select * from dbo.TestingTransactions
179order by Contract_id,TransactionNo
180
181id transactionNo contract_id Data1 Data2
182117 1 44443 NULL Blah Blah
183118 2 44443 NULL 1
184119 3 44443 NULL 2
185120 4 44443 NULL 3
186114 1 213123 Blah NULL
187115 1 333333 NULL Blah Blah
188116 2 333333 Blah Blah NULL
189121 3 333333 NULL Blah Blah