· 7 years ago · Jan 30, 2019, 01:48 AM
1CREATE TABLE [d2c].[linkage_profile](
2 [linkage_profile_id] [int] IDENTITY(1,1) NOT NULL Primary Key,
3 [first_name] [nvarchar](250) NULL,
4 [last_name] [nvarchar](250) NULL,
5 [middle_name] [nvarchar](250) NULL,
6 [suffix] [nvarchar](50) NULL,
7 [is_pregnant] [bit] NULL,
8 [expected_date] [datetime2](7) NULL,
9 [user_id] [int] NOT NULL,
10 [is_grace_period] [bit] NOT NULL,
11 [closed_date] [datetime2](7) NULL,
12 [birth_gender_id] [int] NOT NULL,
13 [status_id] [int] NOT NULL,
14 [assigned_date] [datetime2](7) NULL,
15 [created_by] [nvarchar](50) NULL,
16 [date_created] [datetime2](7) NOT NULL,
17 [modified_by] [nvarchar](50) NULL,
18 [date_modified] [datetime2](7) NULL,
19 [state_no] [nvarchar](20) NULL,
20 [county_id] [int] NULL,
21 [area_name] [nvarchar](3) NULL,
22 [ehars_client_profile_id] [int] NULL,
23 [priority_id] [int] NULL,
24 [assigned_by] [nvarchar](50) NULL,
25
26Alter TRIGGER [d2c].[tr_linkage_profile] ON d2c.linkage_profile FOR INSERT, UPDATE, DELETE
27
28AS
29SET NOCOUNT ON
30
31DECLARE @bit INT ,
32@field INT ,
33@fieldId INT ,
34@maxfield INT ,
35@char INT ,
36@fieldname VARCHAR(128) ,
37@FieldDisplayName VARCHAR(128),
38@TableDisplayName VARCHAR(128),
39@SchemaName VARCHAR(128) ,
40@TableName VARCHAR(128) ,
41@PKCols VARCHAR(1000) ,
42@sql VARCHAR(5000),
43@UpdateDate VARCHAR(21) ,
44@UserName VARCHAR(128) ,
45@Type CHAR(1) ,
46@PKSelect VARCHAR(1000) ,
47@linkageID VARCHAR(1000) ,
48@FullTableName VARCHAR(256),
49@oldvalue VARCHAR(1000),
50@newvalue VARCHAR(1000),
51@oldvalue1 VARCHAR(1000),
52@newvalue1 VARCHAR(1000),
53--@modified_by VARCHAR(20)='modified_by',
54@isviewable char(1),
55@next bit
56
57SELECT @TableName = 'linkage_profile' , @schemaName = 'd2c'
58
59SELECT @FullTableName = @SchemaName + '.' + @TableName
60
61-- date and user
62SELECT @UserName = SYSTEM_USER ,
63@UpdateDate = CONVERT(VARCHAR(8), GETDATE(), 112)
64+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114)
65
66-- Action
67IF EXISTS (SELECT * FROM inserted)
68IF EXISTS (SELECT * FROM deleted)
69SELECT @Type = 'U'
70
71 ELSE
72 SELECT @Type = 'I'
73 ELSE
74SELECT @Type = 'D'
75
76-- get list of columns
77SELECT * INTO #ins FROM inserted
78SELECT * INTO #del FROM deleted
79
80
81-- Get primary key columns for full outer join
82SELECT @PKCols = COALESCE(@PKCols + ' and', ' on')
83+ ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
84FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
85INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
86WHERE pk.TABLE_NAME = @TableName
87AND pk.TABLE_SCHEMA = @SchemaName
88AND CONSTRAINT_TYPE = 'PRIMARY KEY'
89AND c.TABLE_NAME = pk.TABLE_NAME
90AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
91AND c.CONSTRAINT_SCHEMA = pk.TABLE_SCHEMA
92
93-- Get primary key select for insert
94SELECT @PKSelect = COALESCE(@PKSelect+'+','') + '''' +
95 '''+convert(varchar(100),coalesce(i.' + COLUMN_NAME +',d.' + COLUMN_NAME + '))+'''''
96FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
97INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
98WHERE pk.TABLE_NAME = @TableName
99AND pk.TABLE_SCHEMA = @SchemaName
100AND CONSTRAINT_TYPE = 'PRIMARY KEY'
101AND c.TABLE_NAME = pk.TABLE_NAME
102AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
103AND c.CONSTRAINT_SCHEMA = pk.TABLE_SCHEMA
104
105-- Get client id select for insert
106if @FullTableName <> 'd2c.ehars_client_profile'
107BEGIN
108SELECT @linkageID = COALESCE(@linkageID+'+','') + '''' +
109 '''+convert(varchar(100),coalesce(i.' + COLUMN_NAME +',d.' + COLUMN_NAME + '))+'''''
110FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
111WHERE c.TABLE_NAME = @TableName
112AND c.TABLE_SCHEMA = @SchemaName
113AND c.COLUMN_NAME = 'linkage_profile_id'
114END
115
116if @FullTableName = 'd2c.ehars_client_profile'
117SELECT @linkageID = 'NULL'
118
119
120IF @PKCols IS NULL
121BEGIN
122RAISERROR('no PK on table %s', 16, -1, @FullTableName)
123RETURN
124END
125---------------------Display value for table
126SET @TableDisplayName = 'Linkage Profile Data'
127
128--------------------------
129SELECT @field = 0,
130@maxfield = MAX(ORDINAL_POSITION)
131FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND TABLE_SCHEMA = @SchemaName
132
133WHILE @field < @maxfield
134BEGIN
135SELECT @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS
136 WHERE TABLE_NAME = @TableName
137 AND TABLE_SCHEMA = @SchemaName
138 AND ORDINAL_POSITION = @field
139
140SELECT @fieldid = COLUMNPROPERTY(OBJECT_ID(@FullTableName), @fieldname, 'ColumnID')
141
142SELECT @field = MIN(ORDINAL_POSITION)
143FROM INFORMATION_SCHEMA.COLUMNS
144WHERE TABLE_NAME = @TableName
145AND TABLE_SCHEMA = @SchemaName
146AND ORDINAL_POSITION > @field
147select @bit = (@fieldid - 1 )% 8 + 1
148select @bit = power(2,@bit - 1)
149select @char = ((@fieldid - 1) / 8) + 1
150
151
152----
153--select TABLE_NAME, COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME in ('barrier_client', 'client_attempt','disposition', 'ehars_client_profile', 'note_attachement', 'linkage_profile', 'provider_attempt', 'referral_client', 'result')
154
155
156--------------display values for fields
157 SET @FieldDisplayName = CASE @fieldname --I have a lot of case statement here, removed to keep it short
158 WHEN 'assigned_by' THEN 'Assigned By'
159 WHEN 'assigned_date' THEN 'Assigned Date'
160 END
161
162-- Managing Is viewable field for all types
163 select @isviewable = '0'
164 ----------
165 if @type in('U')
166 begin
167 select @isviewable = '1'
168 end
169 -----------
170if @type in('D')
171if @fieldname in ('referral_id','other_desc','barrier_id')
172 Begin
173 select @IsViewable='1'
174 End
175 -----------
176if @fieldname in ('created_by','date_created')
177 Begin
178 select @IsViewable='0'
179 End
180 -----------setting next at default 0
181Select @next = 0
182--------------------------------field specific values 5 Linkage Profile
183 if @fieldname = 'User_ID'
184 BEGIN
185 select @oldvalue =c.first_name from [d2c].[user] c inner join deleted d on d.User_ID = c.User_ID
186 select @oldvalue1 =c.last_name from [d2c].[user] c inner join deleted d on d.User_ID = c.User_ID
187 Select @oldvalue = CONCAT (@oldvalue , ' ', @oldvalue1)
188 if @oldvalue = 'd.expected_date ' set @oldvalue = 'NULL'
189
190 select @newvalue = c.first_name from [d2c].[user] c inner join inserted i on i.User_ID = c.User_ID
191 select @newvalue1 =c.last_name from [d2c].[user] c inner join inserted i on i.User_ID = c.User_ID
192 Select @newvalue = CONCAT (@newvalue , ' ', @newvalue1)
193 if @newvalue = 'i.expected_date ' set @newvalue = 'NULL'
194 Select @next = 1
195
196 end
197
198 if @fieldname = 'birth_gender_id'
199 BEGIN
200 select @oldvalue =c.name from lu.birth_gender c inner join deleted d on d.birth_gender_id = c.birth_gender_id
201 if @oldvalue = 'd.closed_date' set @oldvalue = 'NULL'
202
203 select @newvalue = c.name from lu.birth_gender c inner join inserted i on i.birth_gender_id = c.birth_gender_id
204 if @newvalue = 'i.closed_date' set @newvalue = 'NULL'
205 Select @next = 1
206
207 end
208
209 if @fieldname = 'Status_ID'
210 BEGIN
211 select @oldvalue =c.status_name from lu.status c inner join deleted d on d.Status_ID = c.Status_ID
212 if @oldvalue = 'd.closed_date' set @oldvalue = 'NULL'
213
214 select @newvalue = c.status_name from lu.status c inner join inserted i on i.Status_ID = c.Status_ID
215 if @newvalue = 'i.closed_date' set @newvalue = 'NULL'
216 Select @next = 1
217
218 end
219 if @fieldname = 'county_id'
220 BEGIN
221 select @oldvalue =c.name from lu.county c inner join deleted d on d.county_id = c.county_id
222 if @oldvalue = 'd.state_no' set @oldvalue = 'NULL'
223
224 select @newvalue = c.name from lu.county c inner join inserted i on i.county_id = c.county_id
225 if @newvalue = 'i.state_no' set @newvalue = 'NULL'
226 Select @next = 1
227
228 end
229
230 if @fieldname = 'priority_id'
231 BEGIN
232 select @oldvalue =c.priority_name from lu.priority c inner join deleted d on d.priority_id = c.priority_id
233 if @oldvalue = 'd.ehars_client_profile_id' set @oldvalue = 'NULL'
234
235 select @newvalue = c.priority_name from lu.priority c inner join inserted i on i.priority_id = c.priority_id
236 if @newvalue = 'i.ehars_client_profile_id' set @newvalue = 'NULL'
237 Select @next = 1
238
239 end
240
241--------------------------------------------------------------------------running field specific value in insert
242 If @next = 1
243
244 if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in ('I','D')
245 print substring(COLUMNS_UPDATED(),@char, 1);
246 BEGIN
247 SELECT @sql = '
248 insert d2c.Audit_client_Data ( Type,TableSchema,TableName,tabledisplayname,fielddisplayname,
249 PK, linkage_profile_id,is_viewable,FieldName, OldValue, NewValue,
250 UpdateDate, UserName,Application)
251 select ''' + @Type + ''','''+ @SchemaName + ''',''' + @TableName + ''',''' + @TableDisplayName + '''
252 ,''' + @FieldDisplayName + ''',' + @PKSelect + ',' + @linkageID + ', ' + @isviewable+'
253 ,''' + @fieldname + ''''+ ', ''' +@oldvalue+''', ''' +@newvalue+''', ' + '''' + @UpdateDate + ''''+ '
254 ,''' + @UserName + ''''+ ',''' + REPLACE(APP_NAME(), '''', '''''') + '''' +
255 ' from #ins i full outer join #del d'+ @PKCols+ ' where i.' + @fieldname + ' <> d.' + @fieldname + ' or (i.' + @fieldname + ' is null and d.'+ @fieldname + ' is not null)' + ' or (i.' + @fieldname + ' is not null and d.' + @fieldname + ' is null)'
256
257 EXEC (@sql)
258
259 END
260
261--------------------condition 1 change this for each trigger
262
263if @next = 0
264if @fieldname not in ('modified_by','date_modified')
265 if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in ('I','D')
266
267 BEGIN
268
269 select @oldvalue ='d.'+@fieldname
270 select @newvalue = 'i.'+@fieldname
271 SELECT @sql = '
272 insert d2c.Audit_client_Data ( Type,TableSchema,TableName,tabledisplayname,fielddisplayname,
273 PK, linkage_profile_id,is_viewable,FieldName, OldValue, NewValue,
274 UpdateDate, UserName,Application)
275 select ''' + @Type + ''','''+ @SchemaName + ''',''' + @TableName + ''',''' + @TableDisplayName + '''
276 ,''' + @FieldDisplayName + ''',' + @PKSelect + ',' + @linkageID+ ', ' + @isviewable+'
277 ,''' + @fieldname + ''''+ ', ' +@oldvalue+', ' +@newvalue+', ' + '''' + @UpdateDate + ''''+ '
278 ,''' + @UserName + ''''+ ',''' + REPLACE(APP_NAME(), '''', '''''') + '''' +
279 ' from #ins i full outer join #del d'+ @PKCols+ ' where i.' + @fieldname + ' <> d.' + @fieldname + ' or (i.' + @fieldname + ' is null and d.'+ @fieldname + ' is not null)' + ' or (i.' + @fieldname + ' is not null and d.' + @fieldname + ' is null)'
280 EXEC (@sql)
281
282
283 END
284---------------------
285END
286
287"if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in ('I','D')" which is at the very bottom. HELP!!!