Tracking changes - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Tracking changes

Author

Topic

mathomas73
Starting Member

23 Posts

Posted - 2013-06-06 : 05:51:44

Hi everyone

My challenge for to day is to be able to track changes to my database. I have a table called ColourPrice below:

Colour Price
Red 12.99
Green 15.99
Orange 26.99
Blue 8.99

I have then created an empty table in my database called Audit. Example below.

CREATE TABLE DatabaseAudit
(id INT,
ChangeData DATETIME,
FieldChanged NVARCHAR(50),
ChangedFrom NVARCHAR(255),
ChangedTo NVARCHAR(255));

What I want to do is put some code together - I think using ALTER TABLE than enables me to capture an changes that I make to the ColourPrice table in this Audit table. Is this possible and can someone please give me some pointers?

Thank you in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-06 : 06:04:26

you need to use a trigger for that. something like


CREATE TRIGGER TriggerName
ON ColourPrice
FOR UPDATE
AS
BEGIN
IF UPDATE(Colour) OR UPDATE(Price)
BEGIN 
INSERT DatabaseAudit (ChangeDate,FieldChanged,ChangeFrom,ChangedTo)
SELECT GETDATE(),'Colour',d.Colour,i.Colour
FROM INSERTED i
INNER JOIN DELETED d
On d.PK = i.PK
AND ISNULL(d.Colour,'') <> ISNULL(i.Colour,'')

UNION ALL

SELECT GETDATE(),'Price',d.Price,i.Price
FROM INSERTED i
INNER JOIN DELETED d
On d.PK = i.PK
AND ISNULL(d.Price,'') <> ISNULL(i.Price,'')
END 

END

PK is the primary key of ColourPrice table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mathomas73
Starting Member

23 Posts

Posted - 2013-06-06 : 06:11:46

Thanks Visakh, you have no idea how much I appreciate your help. Its invaluable.

Can I ask what PK is in the script you've written? I assume Trigger is standard syntax for SQL then that enables changes to be captured? Not sure what to do but I'll give it a try!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-06 : 06:13:25

quote:
Originally posted by mathomas73

Thanks Visakh, you have no idea how much I appreciate your help. Its invaluable.

Can I ask what PK is in the script you've written? I assume Trigger is standard syntax for SQL then that enables changes to be captured? Not sure what to do but I'll give it a try!

I'd specified it in footnote in last post

just to restate PK represents primary key of the table.

Yep..trigger is one of the standard method used for tracking changes in sql.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mathomas73
Starting Member

23 Posts

Posted - 2013-06-06 : 06:24:27

Sorry, being stupid. I should pay more attention. I've not set a primary key in the ColourPrice table - assume it would be colour as that's the unique value?

mathomas73
Starting Member

23 Posts

Posted - 2013-06-06 : 06:25:27

So I assume I need to go back to the code I use to create the ColourPrice table and set Colour as PK?

mathomas73
Starting Member

23 Posts

Posted - 2013-06-06 : 06:50:35

Hi Visakh. I'm getting close. Created the PK in the ColourPrice table and then run the code - just getting the following error message but the ColourPrice table does exist?

Msg 8197, Level 16, State 4, Procedure TriggerName, Line 2
The object 'ColourPrice' does not exist or is invalid for this operation.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-06 : 06:53:42

quote:
Originally posted by mathomas73

Sorry, being stupid. I should pay more attention. I've not set a primary key in the ColourPrice table - assume it would be colour as that's the unique value?

yep...basically column should be the one which uniquely identifies a row

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-06 : 06:54:57

quote:
Originally posted by mathomas73

Hi Visakh. I'm getting close. Created the PK in the ColourPrice table and then run the code - just getting the following error message but the ColourPrice table does exist?

Msg 8197, Level 16, State 4, Procedure TriggerName, Line 2
The object 'ColourPrice' does not exist or is invalid for this operation.

Is name of your table correct? Seems like your tablename is not ColourPrice seeing the error

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mathomas73
Starting Member

23 Posts

Posted - 2013-06-06 : 07:41:31

Hi there. My code is now as below. When I run it I get the error saying that 'TriggerName' already exists? What does this mean? What does this code create - is it a temporary file or something and how can I prevent this error?

Anyway, I have made an update to ColourPrice table by changing a price, but there are no changes in my audit table? Any ideas. Thank you.

Msg 2714, Level 16, State 2, Procedure TriggerName, Line 7
There is already an object named 'TriggerName' in the database.

CREATE TRIGGER TriggerName
ON ColourPrice
FOR INSERT,UPDATE
AS
BEGIN
INSERT DatabaseAudit (ChangeDate,FieldChanged,ChangedFrom,ChangedTo)
SELECT GETDATE(),'Colour',d.Colour,i.Colour
FROM INSERTED i
LEFT JOIN DELETED d
On d.Colour = i.Colour

UNION ALL

SELECT GETDATE(),'Price',d.Price,i.Price
FROM INSERTED i
LEFT JOIN DELETED d
On d.Colour = i.Colour

END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-06 : 07:48:40

thats because you've already created it. now if you want to modify you need to use ALTER TRIGGER
otherwise use below code which will do this check beforehand


IF  EXISTS(SELECT 1 FROM sys.objects WHERE type='TR' AND OBJECT_NAME(object_id) = 'TriggerName')
 DROP TRIGGER TriggerName
GO

CREATE TRIGGER TriggerName
ON ColourPrice
FOR INSERT,UPDATE
AS
BEGIN 
INSERT DatabaseAudit (ChangeDate,FieldChanged,ChangedFrom,ChangedTo)
SELECT GETDATE(),'Colour',d.Colour,i.Colour
FROM INSERTED i
LEFT JOIN DELETED d
On d.Colour = i.Colour

UNION ALL

SELECT GETDATE(),'Price',d.Price,i.Price
FROM INSERTED i
LEFT JOIN DELETED d
On d.Colour = i.Colour

END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mathomas73
Starting Member

23 Posts

Posted - 2013-06-06 : 08:09:01

Visakh, please forgive all my questions - I am learning I promise. I've made the change but am not clear what create trigger actually creates - is it an object that is held somewhere?

This will sound stupid but I've tried to change a price in my ColourPrice table, so that I can see if that change is then recorded in my Audit table. However I get this error message even though the data is in the exact same format?

Is this the right place for my queries because its very basic compared to other people?

UPDATE ColourPrice SET Price = '4.99'
WHERE Colour = 'Red'

Msg 8114, Level 16, State 5, Procedure TriggerName, Line 7
Error converting data type varchar to numeric

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-06 : 08:14:21

That is error from Trigger

ALTER TRIGGER TriggerName
ON ColourPrice
FOR INSERT,UPDATE
AS
BEGIN 
INSERT DatabaseAudit (ChangeDate,FieldChanged,ChangedFrom,ChangedTo)
SELECT GETDATE(),'Colour',d.Colour,i.Colour
FROM INSERTED i
LEFT JOIN DELETED d
On d.Colour = i.Colour

UNION ALL

SELECT GETDATE(),'Price',CAST(d.Price AS VARCHAR),CAST(i.Price AS VARCHAR)
FROM INSERTED i
LEFT JOIN DELETED d
On d.Colour = i.Colour
END

You can see trigger under that respective Table in SSMS (sql server management studio)

--
Chandu

mathomas73
Starting Member

23 Posts

Posted - 2013-06-06 : 08:30:22

Brilliant, this has worked and has updated my Audit table. Can you please tell me what the issue was with the data type? Does trigger always looks for VARCHAR - I assume because of the different changes that could be made to a table?

What does CAST mean/do?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-06 : 08:34:14

You should use same number of columns with same data types in both SELECT statements whenever you use SET Operators(UNION, UNION ALL ... etc)

SELECT same number of column and also should be same type
UNION ALL
SELECT same number of column and also should be same type

--
Chandu

mathomas73
Starting Member

23 Posts

Posted - 2013-06-06 : 08:42:36

Hi everyone, thanks so much for your help today - again its been amazing. Another quick question while I'm focused on tracking changes in SQL. I have the following code which basically tracks changes to a table ColourPrice and logs any changes in a DatabaseAudit table.

How would my code change so that if changes were made both to ColourPrice and ColourOrders tables (multiple tables), the changes would be logged in the DatabaseAudit table?

Thanks in advance

IF EXISTS(SELECT 1 FROM sys.objects WHERE type='TR' AND OBJECT_NAME(object_id) = 'TriggerName')
DROP TRIGGER TriggerName
GO

CREATE TRIGGER TriggerName
ON ColourPrice
FOR INSERT,UPDATE
AS
BEGIN
INSERT DatabaseAudit (ChangeDate,FieldChanged,ChangedFrom,ChangedTo)
SELECT GETDATE(),'Colour',d.Colour,i.Colour
FROM INSERTED i
LEFT JOIN DELETED d
On d.Colour = i.Colour

UNION ALL

SELECT GETDATE(),'Price',CAST(d.Price AS VARCHAR),CAST(i.Price AS VARCHAR)
FROM INSERTED i
LEFT JOIN DELETED d
On d.Colour = i.Colour

END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-06 : 10:19:42

quote:
Originally posted by mathomas73

Hi everyone, thanks so much for your help today - again its been amazing. Another quick question while I'm focused on tracking changes in SQL. I have the following code which basically tracks changes to a table ColourPrice and logs any changes in a DatabaseAudit table.

How would my code change so that if changes were made both to ColourPrice and ColourOrders tables (multiple tables), the changes would be logged in the DatabaseAudit table?

Thanks in advance

IF EXISTS(SELECT 1 FROM sys.objects WHERE type='TR' AND OBJECT_NAME(object_id) = 'TriggerName')
DROP TRIGGER TriggerName
GO

CREATE TRIGGER TriggerName
ON ColourPrice
FOR INSERT,UPDATE
AS
BEGIN
INSERT DatabaseAudit (ChangeDate,FieldChanged,ChangedFrom,ChangedTo)
SELECT GETDATE(),'Colour',d.Colour,i.Colour
FROM INSERTED i
LEFT JOIN DELETED d
On d.Colour = i.Colour

UNION ALL

SELECT GETDATE(),'Price',CAST(d.Price AS VARCHAR),CAST(i.Price AS VARCHAR)
FROM INSERTED i
LEFT JOIN DELETED d
On d.Colour = i.Colour

END

A trigger is defined for a table so you've create multiple triggers one for each table. the inside logic would be similar to above only changing columnnames accordingly

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources