December 1, 2015 at 4:49 pm
Hi All,
I have a scenario where I have to update the old ID value to 0 and new one to 1.
For example,
my ODS table will have below values:
ID Value Date
1 "abc" 1/1/1900
2 "efh" 1/1/1900
3 "xyz" 1/1/1900
ODS history:
ID Value Date Flag Changed_Date
1 "abc" 1/1/1900 1 01/12/2015
2 "efh" 1/1/1900 1 01/12/2015
3 "xyz" 1/1/1900 1 01/12/2015
Now My ODS will have changed value for ID=1
ID Value Date
1 "mno" 1/1/1900
2 "efh" 1/1/1900
3 "xyz" 1/1/1900
Now I want my ODS history table to be
ODS history:
ID Value Date Flag Changed_Date
1 "abc" 1/1/1900 0 getdate()
2 "efh" 1/1/1900 1 01/12/2015
3 "xyz" 1/1/1900 1 01/12/2015
1 "mno" 1/1/1900 1 01/12/2015
Basically, Flag for old value for ID=1 is 0 and changed_Date= getdate() and new record for ID=1 is inserted in History.
How can i do this? Can you guys please pour in some ideas?
December 1, 2015 at 11:08 pm
If you have dates (Changed_Date column) in the history table, then why do you need a flag to tell you whether a row is active or not? You have the real table with the active rows.
You're also making a mistake in saving INSERTs to the history table. That instantly doubles the amount of data you're storing. If you never make an update or a delete to the main table, your history table should be empty because these types of history tables should ONLY record changes and they should be in the form of the old row. The new row will always be available in the real table.
If the whole table is a combination of history and active rows (you have just one table instead of a real table and a history table), then you still don't need the flag because the dates will tell you if a row is active or not.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 1, 2015 at 11:23 pm
Flags are not always selective, which typically makes them a poor indexing choice. You need to understand and follow database normalization basics (at least down to 3NF), before attempting to design tables. After mastering database normalization, consider auditing, change data capture, or create a harness for your own auditing table design (in 3NF). I suggest avoiding triggers. Instead, investigate whether the application can decide what is a change, or not. When there is a change, investigate whether the application can perform the insert into an audit or change table, as needed. Consider the effects of concurrency, and test different transaction isolation levels Appropriate covering indexes will be needed. One so that the application can seek an existing row. Another so that the maximum audit date/attribute can be quickly sought. Post the data definition language (as code) for any table design questions, including all primary, foreign, and candidate keys.
December 2, 2015 at 1:23 pm
I'd urge you to use triggers rather than application code for this type of thing. This makes sure the code is consistently applied and prevents having to re-write code to apply the flag logic if multiple sections of code can update the ODS table.
CREATE TRIGGER ODS__TRG_UPDATE
ON dbo.ODS
AFTER UPDATE
AS
SET NOCOUNT ON;
UPDATE oh
SET Flag = 0
FROM deleted d
INNER JOIN ODS_History oh ON oh.ID = d.ID
WHERE
oh.Flag = 1 AND
ISNULL(oh.Value, '~~~') <> ISNULL(d.Value, '~~~') --ISNULLs just handle value being changed to/from NULL to/from non-NULL
GO --end of trigger
Edit: Edited comment to explain use of ISNULL().
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".
December 2, 2015 at 3:10 pm
Hi,
Thank you all.
I got the solution from below link
My scenario was SCD type 2 described in the link.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply