· 8 years ago · Jan 08, 2018, 05:42 PM
1CREATE TABLE [dbo].[OrganizerDataDependencyChange](
2[TableID] [INT] NOT NULL,
3[Database] [VARCHAR](150) NOT NULL,
4[Updated] [DATETIME] NULL
5)
6CREATE CLUSTERED INDEX [CX_OrganizerDataDependencyChange_TableID_DB] ON [dbo].[OrganizerDataDependencyChange]
7(
8[TableID] ASC,
9[Database] ASC
10)
11
12CREATE PROCEDURE [dbo].[SaveOrganizerDataDependencyChange]
13(
14@TableID int,
15@Database varchar(150)
16)
17AS
18BEGIN
19
20SET NOCOUNT ON;
21
22 DECLARE @rowcount INT;
23
24INSERT INTO dbo.OrganizerDataDependencyChange
25 ( TableID, [Database], Updated )
26SELECT TOP 1
27 TableID = @TableID,
28 [Database] = ISNULL(@Database, ''),
29 Updated = GETDATE()
30FROM dbo.OrganizerDataDependencyChange
31WHERE NOT EXISTS
32(
33 SELECT
34 1
35 FROM dbo.OrganizerDataDependencyChange
36 WHERE TableID = @TableID
37 AND [Database] = @Database
38)
39
40SET @rowcount = @@ROWCOUNT --How many rows were inserted?
41
42UPDATE dbo.OrganizerDataDependencyChange
43SET Updated = GETDATE()
44WHERE @rowcount = 0 --Only run this if no rows were inserted
45AND TableID = @TableID
46AND [Database] = ISNULL(@Database, '')
47
48END
49
50BEGIN TRANSACTION;
51
52UPDATE dbo.OrganizerDataDependencyChange WITH (HOLDLOCK)
53 SET Updated = GETDATE()
54 WHERE TableID = @TableID
55 AND [Database] = ISNULL(@Database, '');
56
57IF @@ROWCOUNT = 0
58BEGIN
59 INSERT INTO dbo.OrganizerDataDependencyChange
60 ( TableID, [Database], Updated )
61 VALUES(@TableID, ISNULL(@Database, ''), GETDATE());
62END
63
64COMMIT TRANSACTION;
65
66USE tempdb;
67GO
68IF OBJECT_ID(N'dbo.OrganizerDataDependencyChange', N'U') IS NOT NULL
69DROP TABLE dbo.OrganizerDataDependencyChange;
70CREATE TABLE dbo.OrganizerDataDependencyChange
71(
72 PadOneRowPerPage char(5000) NOT NULL
73 , TableID int NOT NULL
74 , DatabaseName sysname NOT NULL
75 , Updated datetime NULL
76 , CONSTRAINT PK_OrganizerDataDependencyChange
77 PRIMARY KEY CLUSTERED (TableID, DatabaseName)
78 WITH (
79 ALLOW_ROW_LOCKS = ON
80 , ALLOW_PAGE_LOCKS = ON
81 , PAD_INDEX = OFF
82 , FILLFACTOR = 100
83 )
84) ON [PRIMARY]
85WITH (
86 DATA_COMPRESSION = NONE
87 );
88GO
89
90IF OBJECT_ID(N'dbo.SaveOrganizerDataDependencyChange', N'P') IS NOT NULL
91DROP PROCEDURE dbo.SaveOrganizerDataDependencyChange;
92GO
93CREATE PROCEDURE dbo.SaveOrganizerDataDependencyChange
94(
95 @TableID int
96 , @DatabaseName varchar(150)
97 , @CallLevel int = 1
98)
99AS
100BEGIN
101 SET NOCOUNT ON;
102
103 DECLARE @msg nvarchar(1000);
104 DECLARE @rowcount int;
105 DECLARE @Updated TABLE (
106 Updated datetime NOT NULL
107 );
108 DECLARE @AppLockResult int;
109 DECLARE @Result int;
110
111 IF @CallLevel > 5
112 BEGIN
113 SET @msg = N'Maximum nesting level reached for SaveOrganizerDataDependencyChange';
114 THROW 50002, @msg, 1;
115 RETURN 0;
116 END
117
118 EXEC @AppLockResult = sys.sp_getapplock
119 @Resource = N'SaveOrganizerDataDependencyChange_Resource'
120 , @LockMode = 'Exclusive' /* Shared, Update, IntentShared,
121 IntentExclusive, or Exclusive */
122 , @LockOwner = 'Transaction'
123 , @LockTimeout = NULL --use @@LOCK_TIMEOUT
124 , @DbPrincipal = N'public'; /* only run if the caller is a member
125 of this role */
126
127 IF @AppLockResult >= 0
128 BEGIN
129 UPDATE dbo.OrganizerDataDependencyChange
130 SET Updated = GETDATE()
131 OUTPUT 1 INTO @Updated (Updated)
132 WHERE TableID = @TableID
133 AND DatabaseName = @DatabaseName;
134
135 IF NOT EXISTS (SELECT 1 FROM @Updated)
136 BEGIN
137 INSERT INTO dbo.OrganizerDataDependencyChange (
138 TableID, DatabaseName, Updated)
139 VALUES (@TableID, @DatabaseName, GETDATE());
140 END
141
142 EXEC sys.sp_releaseapplock
143 @Resource = N'SaveOrganizerDataDependencyChange_Resource'
144 , @LockOwner = 'Transaction'
145 , @DbPrincipal = N'public'; /* only run if the caller is a
146 member of this role */
147
148 RETURN CONVERT(int, 1); --success
149 END
150 ELSE
151 BEGIN
152 SET @CallLevel += 1;
153 EXEC @Result = dbo.SaveOrganizerDataDependencyChange
154 @TableID
155 , @DatabaseName
156 , @CallLevel;
157 IF @Result = 0
158 BEGIN
159 SET @msg = N'SaveOrganizerDataDependencyChange Could not obtain app lock.';
160 THROW 50001, @msg, 1;
161 RETURN 0;
162 END
163 END
164END
165GO
166
167IF OBJECT_ID(N'dbo.SomeTable', N'U') IS NOT NULL
168DROP TABLE dbo.SomeTable;
169CREATE TABLE dbo.SomeTable
170(
171 ID int NOT NULL IDENTITY(1,1)
172 CONSTRAINT PK_SomeTable
173 PRIMARY KEY CLUSTERED
174) ON [PRIMARY]
175WITH (DATA_COMPRESSION = PAGE);
176GO
177
178CREATE TRIGGER dbo.upd_SomeTable
179ON dbo.SomeTable
180AFTER UPDATE, INSERT
181AS
182BEGIN
183 SET NOCOUNT ON;
184 DECLARE @Ret int;
185 DECLARE @TableID int = OBJECT_ID(N'dbo.SomeTable', N'U');
186 DECLARE @DatabaseName sysname = DB_NAME();
187 BEGIN TRY
188 BEGIN TRANSACTION
189 EXEC @Ret = dbo.SaveOrganizerDataDependencyChange
190 @TableID = @TableID
191 , @DatabaseName = @DatabaseName;
192 IF @Ret = 1
193 BEGIN
194 COMMIT TRANSACTION;
195 END
196 ELSE
197 BEGIN
198 ROLLBACK TRANSACTION;
199 END
200 END TRY
201 BEGIN CATCH
202 DECLARE @msg nvarchar(2048);
203 DECLARE @err int;
204 DECLARE @state int;
205 SET @msg = 50000 + ERROR_MESSAGE();
206 SET @err = ERROR_NUMBER();
207 SET @state = ERROR_STATE();
208 IF @@TRANCOUNT > 0
209 BEGIN
210 ROLLBACK TRANSACTION;
211 END
212 ;THROW @err, @msg, @state;
213 END CATCH
214END
215GO
216
217SET NOCOUNT ON;
218GO
219INSERT INTO dbo.SomeTable DEFAULT VALUES;
220GO 10000 --run the above insert 10,000 times
221
222SELECT *
223FROM dbo.OrganizerDataDependencyChange;