May 7, 2017 at 7:13 pm
Hello,
Basically the the subject line says it all. I have been tasked with populating an empty table, but any subsequent changes to the rows mean I need to move the old row into another table, an archive/history table if you will. I'm thinking of writing a trigger to copy the row over, but from what I have read here and there, triggers may not be the best solution in that there may be better alternatives. Also, I am planning on creating the table loading from the spreadsheet onto the table using SSIS, should I be looking to do the row archiving through that as part of the loading process? Perhaps use a conditional split to move the old row into another table before overwriting the old one? I also thought about the possibly using table partitioning. I'm tempted to post in both forums but I know this is frowned upon.
I'm just looking for a bit of guidance/direction to get me off the starting block. Thank you.
Regards,
D.
May 7, 2017 at 10:37 pm
you could put all the steps in a single stored procedure.
-- check for existence of record in destination table using the parameter values you pass into stored procedure.
If it exists, move to archive table. insert new record.
No triggers etc required.
May 7, 2017 at 11:30 pm
Piet,
Thank you for getting back, yes, that sounds like a plan.
Regards,
D.
May 7, 2017 at 11:42 pm
You can also use the OUTPUT clause to populate the "audit/history" table
CREATE TABLE #Table (
SomeInt INT NOT NULL
, SomeString VARCHAR(20) NULL
);
GO
CREATE TABLE #Table_Audit (
RowID INT IDENTITY(1,1) NOT NULL
, SomeInt INT NOT NULL
, SomeString VARCHAR(20) NULL
, ChangeTime DATETIME NOT NULL DEFAULT(GETDATE())
);
GO
CREATE PROCEDURE #Upsert
@SomeInt INT
, @SomeString VARCHAR(20)
AS
BEGIN
UPDATE #Table
SET SomeString = @SomeString
OUTPUT Deleted.SomeInt, Deleted.SomeString
INTO #Table_Audit ( SomeInt, SomeString )
WHERE SomeInt = @SomeInt;
INSERT INTO #Table ( SomeInt, SomeString )
SELECT @SomeInt, @SomeString
WHERE NOT EXISTS (SELECT 1 FROM #Table WITH (XLOCK, HOLDLOCK)
WHERE SomeInt = @SomeInt);
END;
GO
EXEC #Upsert 1, 'Newval 1';
EXEC #Upsert 2, 'Newval 2';
EXEC #Upsert 1, 'Updated val 1';
EXEC #Upsert 1, 'UpdateVal 1';
EXEC #Upsert 2, 'UpdateVal 2';
SELECT * FROM #Table_Audit AS ta
DROP PROCEDURE #Upsert;
DROP TABLE #Table;
DROP TABLE #Table_Audit;
May 10, 2017 at 9:10 am
How confident are you that the changed data is always going to come in through the stored procedure call?
I'm not a fan of triggers but they're a better option for tracking any change to the data if the change happens outside the procedure. Just be sure the trigger can handle a set of changes not just one row.
May 10, 2017 at 9:31 am
Could you not achieve a lot of this more efficiently with a Merge statement ?
May 11, 2017 at 4:07 am
RandomEvent - Wednesday, May 10, 2017 9:31 AMCould you not achieve a lot of this more efficiently with a Merge statement ?
Yes, you can. And you can even use the output clause with that to get the deleted.* values into the "archive" table in the same statement. Using merge may not always be most efficient performance-wise (topics comparing the performance of merge to separate inserts/updates/deletes can be found here and on other sites in abundance), but from a coding point-of-view merge is certainly more efficient.
One other way could theoretically be to use CDC for this. That would be the best solution performance-wise, as a) the archiving process is outside the user's transacion and b) it works for any and all (logged) DDL on the table, whether it is from a procedure or a direct call into insert, update or delete. !!BUT!! -depending on the source table's definition- the fn_cdc_get_net_changes_ functions do not always correctly return only the deleted rows (old values of defered updates are also returned as deletes), so until that is fixed I disadvice using CDC for tracking deleted values.
May 11, 2017 at 6:42 am
I have a number of data feeds, with shadow tables, where I maintain an archive of the Shadow table with a trigger like:
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[TR_ShadowUD]
ON [dbo].[Shadow]
AFTER UPDATE, DELETE
AS
BEGIN
DECLARE @Now smalldatetime = CURRENT_TIMESTAMP;
INSERT INTO dbo.ShadowA
(
FromTime,ToTime,...
)
SELECT FromTime,@Now,...
FROM deleted;
UPDATE S
SET FromTime = @Now
FROM dbo.shadow S
JOIN inserted I
ON S.PK = I.PK;
END;
GO
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply