January 25, 2011 at 11:57 am
Hi,
I have a Master_detail table with 5 columns (shown below). I want track any changes to the values in the table in a seperate table called Master_Detail_Change table. I have ld and new columns. Planning to use a Update_trigger to record the changes. Can the trigger be written to handle multiple datatypes?
Master_detail
-------------
Year char(4)
Account_nm varchar(10)
Account_desc varchar(50)
Amount Money
Date Datetime
Update_id Suser_Sname()
Master_detail_Change
---------------------
Old_Account_nm
New_Account_nm
Account_desc
New_Account_desc
Old_Amount
New_Amount
Update_id Suser_Sname()
January 25, 2011 at 2:04 pm
i think you'll want to search a bit more here on SSC for a better example of auditing changes.
first, your example table is missing a primary key...so for your example, it'd be very very difficult to identify which row changed form one value to another, without a way to link it directly back to the original row.
once you have a PK, you typically save copies of both rows, or sometimes just the specific columns you want to track...you don't want to try and use a name-value pair and a catch all column that gets everything converted to the same datatype.
Your table could potentially have hundreds of rows updated int eh same statement as well....
anyway, here's a rough example based on your prototype table.
CREATE TABLE Master_detail(
DetailID int identity(1,1) not null primary key,
[Year] char(4) ,
Account_nm varchar(10) ,
Account_desc varchar(50) ,
Amount Money ,
[Date] Datetime ,
Update_id varchar(30) default Suser_Sname() )
CREATE TABLE Master_detail_Change(
Identifier varchar(10),--typically "Old Value" and "New Value"
DetailID int ,
[Year] char(4) ,
Account_nm varchar(10) ,
Account_desc varchar(50) ,
Amount Money ,
[Date] Datetime ,
Update_id varchar(30))
GO
CREATE TRIGGER TR_AudityMyTable ON Master_detail
FOR UPDATE
AS
BEGIN
SET NOCOUNT ON -- avoid printing "x rows(s) affected"
INSERT INTO Master_detail_Change (Identifier,DetailID,[Year],Account_nm,Account_desc,Amount,[Date],Update_id)
SELECT 'New Value',DetailID,[Year],Account_nm,Account_desc,Amount,[Date],Suser_Sname() FROM INSERTED
UNION ALL
SELECT 'Old Value',DetailID,[Year],Account_nm,Account_desc,Amount,[Date],Suser_Sname() FROM DELETED
END
Lowell
January 25, 2011 at 2:18 pm
Hello Lowell,
I am sorry i forgot to add the primary Key in the Master_Detail Table. Yes, there is one primary key called Detail_Id (as you mentioned) which is the key for the table and its value is unique. So thanks for bringing it up. I will take your example and see if could get it working fine.
I really appreciate your help on this question! Thanks a lot !!
January 26, 2011 at 12:31 pm
You could use SQL_VARIANT data type that can store any other base data type without conversion or lost precision:
DECLARE @x AS SQL_VARIANT, @y AS SQL_VARIANT, @t AS SQL_VARIANT
SELECT @x = CONVERT(MONEY,123.456), @y = GETDATE()
SELECT @x, @y, SQL_VARIANT_PROPERTY ( @x, 'BaseType' ), SQL_VARIANT_PROPERTY ( @y, 'BaseType' )
SET @t=@x; SET @x=@y; SET @y=@t
SELECT @x, @y, SQL_VARIANT_PROPERTY ( @x, 'BaseType' ), SQL_VARIANT_PROPERTY ( @y, 'BaseType' )
set @x=NULL;
SELECT @x, SQL_VARIANT_PROPERTY ( @x, 'BaseType' )
But a key-value changelog is not recommended.
You already have new values in the original table, so there is no need to log new values. They are just bloating your log without adding any new info.
Store just the values you are losing by the change: deleted rows and old updated values.
You can accomplish that by trigger that reacts to updates and deletions and inserting into a log just rows from the "deleted" metatable.
Good luck!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply