October 10, 2019 at 8:59 pm
I have a table of objects. Each of them has various references, e.g. from reference 1 to reference 10:
IF OBJECT_ID('tempdb..#objects_tbl') IS NOT NULL
DROP TABLE #objects_tbl;
CREATE TABLE #objects_tbl (
object_unique_id INT NOT NULL,
many_other_columns CHAR NULL,
reference_1 VARCHAR(50) DEFAULT '' NOT NULL,
reference_10 VARCHAR(50) DEFAULT '' NOT NULL
);
ALTER TABLE #objects_tbl
ADD CONSTRAINT objects_tbl_PK PRIMARY KEY (object_unique_id)
INSERT INTO #objects_tbl
SELECT c.* FROM
(
SELECT1 AS content_id,
'' AS other_columns,
'Family or Medical' AS ref1,
'Traveller' AS ref10
UNION ALL
SELECT 2, '','Maintenance','Logistics Issue'
) c
SELECT * from #objects_tblFrom time to time those references could be amended even several times a day and I was required on a periodic basis to generate a snapshot. In order to achieve it I had a trigger, which would write into #objects_tbl_auditing table which would look exactly as #objects_tbl plus two more columns:
IF OBJECT_ID('tempdb..#objects_tbl_auditing ') IS NOT NULL
DROP TABLE #objects_tbl_auditing ;
CREATE TABLE #objects_tbl_auditing (
object_unique_idINT NOT NULL,
many_other_columnsCHAR NULL,
reference_1VARCHAR(50) DEFAULT '' NOT NULL,
reference_10VARCHAR(50) DEFAULT '' NOT NULL,
record_change_statusCHAR(6),
record_change_datetimeDATETIME DEFAULT GETDATE() NOT NULL);
ALTER TABLE #objects_tbl_auditing
ADD CONSTRAINT objects_tbl_auditing_PK PRIMARY KEY (record_change_datetime DESC, object_unique_id)
INSERT INTO #objects_tbl_auditing
SELECT c.* FROM
(
SELECT1 AS content_id,
'' AS other_columns,
'Training' AS ref1,
'Traveller' AS ref10,
'MODIFY' AS change_status,
'2019-10-10 11:26:00.000' change_date
UNION ALL
SELECT1 AS content_id,
'' AS other_columns,
'Family or Medical' AS ref1,
'Weather' AS ref10,
'MODIFY',
'2019-10-10 12:26:00.000'
UNION ALL
SELECT1 AS content_id,
'' AS other_columns,
'Family or Medical' AS ref1,
'Traveller' AS ref10,
'MODIFY',
'2019-10-10 14:00:00.000'
UNION ALL
SELECT 2, '','Strike','Logistics Issue','MODIFY','2019-10-10 10:00:00.000'
UNION ALL
SELECT 2, '','Maintenance','Logistics Issue','MODIFY','2019-10-10 14:00:00.000'
UNION ALL
SELECT 3, '','Visa','Immigration','DELETE','2019-10-10 11:26:52.000'
) c
SELECT * from #objects_tbl_auditingTo extract the data I simply used the following SELECT until now
DECLARE @i_from_date DATE = '2019-10-10',
@i_to_date DATE = '2019-10-10';
WITH object_changes AS(
SELECTobject_unique_id,
many_other_columns,
reference_1,
reference_10,
record_change_status,
ROW_NUMBER() OVER (PARTITION BY object_unique_id ORDER BY record_change_datetime DESC) AS rownum
FROM #objects_tbl_auditing
WHERE CAST( record_change_datetime AS DATE) BETWEEN @i_from_date AND @i_to_date)
SELECT * FROM object_changes
WHERE rownum = 1
But from now on there is a new request – to ONLY export modified columns. I can achieve it by having new triggers like IF UPDATE(Ref1) …. IF UPDATE(Ref10) and write into a table of a different structure, something like
2, Ref1, 'Strike', 'MODIFY'
2,Ref2, 'Maintenance', 'MODIFY'
but I wonder if it is possible to extract the changes only using current tables structure, comparing content of ref columns from #objects_tbl table with #objects_tbl_auditing WHERE rownum = 1 ?
Much obliged as always!
October 11, 2019 at 5:35 pm
You could do comparisons after the fact using the auditing data, but that would be big overhead.
You don't need separate triggers for the original trigger to indicate which column(s) were changed. Just add a smallint bit mask column, and set it appropriately in the trigger to "tell" you later which columns actually changed. For example:
ALTER TRIGGER ...
...
INSERT INTO auditing
SELECT <existing columns>,
/*1=ref1; 2=ref2; 4=ref3; 8=ref4; ...; 512=ref10*/
CASE WHEN ISNULL(i.reference_1, '\~\') <> ISNULL(d.reference_1, '\~\')
THEN 1 ELSE 0 END +
CASE WHEN ISNULL(i.reference_2, '\~\') <> ISNULL(d.reference_2, '\~\')
THEN 2 ELSE 0 END +
--...
CASE WHEN ISNULL(i.reference_10, '\~\') <> ISNULL(d.reference_10, '\~\')
THEN 512 ELSE 0 END AS reference_columns_changed_bits
FROM inserted i
INNER JOIN deleted d ON i.object_unique_id = d.object_unique_id
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 11, 2019 at 6:03 pm
What about something like this on your last query:
DECLARE
@i_from_date DATE = '2019-10-10'
, @i_to_date DATE = '2019-10-10';
WITH [object_changes]
AS
(
SELECT
[object_unique_id]
, [many_other_columns]
, [reference_1]
, [reference_10]
, [record_change_status]
FROM [#objects_tbl_auditing]
WHERE CAST([record_change_datetime] AS DATE)
BETWEEN @i_from_date AND @i_to_date
)
, [actual_changes]
AS
(
SELECT
[object_changes].[object_unique_id]
, [object_changes].[many_other_columns]
, NULLIF([object_changes].[reference_1], LAG([object_changes].[reference_1]) OVER (PARTITION BY
[object_changes].[object_unique_id]
ORDER BY [object_changes].[record_change_status]
))
AS [reference_1]
, NULLIF([object_changes].[reference_10], LAG([object_changes].[reference_10]) OVER (PARTITION BY
[object_changes].[object_unique_id]
ORDER BY [object_changes].[record_change_status]
))
AS [reference_10]
, [object_changes].[record_change_status]
FROM [object_changes]
)
SELECT
[actual_changes].[object_unique_id], [data].[COLUMN], [data].[value], [actual_changes].[record_change_status]
FROM [actual_changes]
CROSS APPLY (VALUES ('Reference_1',[actual_changes].[reference_1]),
('Reference_10',[actual_changes].[reference_10])
) [data] ([COLUMN], [value])
WHERE value IS NOT NULL;
Basically what I am proposing is that if the record ID is not the maximum record ID (I.e. the most recent change), then you compare the record to the previous record in the audit table. If the value is different, you set it to the NEW value. If the value is the same, it gets replaced by NULL. After that, we are doing a cross apply to basically pivot the columns Reference_1 and Reference_10 into a column and store the data. The problem with this output is we are showing ALL changes, and has no date column to reference when a change happened. So, I'd add the record_change_date time to your output and you are good to go I think. This will show you what changed and when. Maybe not the most efficient, but I think it gets you the output you expected.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
October 12, 2019 at 7:58 pm
If I understand your code, if there are multiple changes I will have to dig through all of them, where I am more interested in the latest. I will try to apply pivoting to it - having columns ref1...ref10 converted into records of the latest change
ref1 .....
ref10 ......
WHERE rownum =1
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply