I am new to SQL Server 2008 and I need advice from all of you. I want to find out the changed value in inserted and deleted tables of the SQL Server 2008 for a table because I am currently doing the audit trail to keep the old and new value. How can I loop all the column to find out which field's value change from the deleted and inserted table? I had tried the if else statement to compare
For example:
create trigger trg_XXX on dbo.table
after update
as
begin
declare
@oldID varchar(6),
@newID varchar(6)
select @oldID = ID from deleted
select @newID = ID from inserted
if(@oldID != @newID)
insert into table (a, b) values (@oldID, @newID)
else
print('do nothing')
Is there a way to do using cursor to loop through the deleted and inserted table or any alternative way? Can give me some example?
I'm not quite sure, what your goal ist, i think it might be something like this.
Let's say we have a table like this:
CREATE TABLE Product
(
ID INT PRIMARY KEY,
Name NVARCHAR(100) NOT NULL,
Price DECIMAL(10,2) NOT NULL
);
and some audit table like this:
CREATE TABLE ProductAudit
(
AuditID INT IDENTITY PRIMARY KEY,
ProductID INT NOT NULL
OldName NVARCHAR(100) NULL,
OldPrice DECIMAL(10,2) NULL,
NewName NVARCHAR(100) NULL,
NewPrice DECIMAL(10,2) NULL
);
Then you create a trigger:
CREATE TRIGGER TR_AUDIT
ON Product
FOR INSERT, UPDATE, DELETE
AS
BEGIN
INSERT INTO ProductAudit (ProductID, OldName, OldPrice, NewName, NewPrice)
SELECT
COALESCE(I.ID, D.ID),
D.Name,
D.Price,
I.Name,
I.Price
FROM
INSERTED I FULL OUTER JOIN DELETED D ON I.ID = D.ID;
END
GO
There you have it.
I think you are looking for what unofficially called magic tables in SQL server.
The tables "INSERTED" and "DELETED" are called magic tables of the
SQL Server. We can not see these tables in the data base. But we can access these
tables from the "TRIGGER"
When we insert the record into the table, the magic table "INSERTED" will be created
In that table the current inserted row will be available. We can access this
record in the "TRIGGER".
When we update a record on the table where trigger is created, the magic tables "INSERTED" and "DELETED" both will be created, the Old data of the updating record will be available in "DELETED" table and, the new data will be available in "INSERTED" table, while accessing them inside the trigger.
When we delete the record from the table, the magic table "DELETED" will be created
In that table the current deleted row will be available. We can access this
record in the "TRIGGER".
Example:
Following code Explains the magic table "INSERTED":
CREATE TRIGGER LogMessage
ON EMP
FOR INSERT
AS
DECLARE @EMPNAME varchar(50)
SELECT @EMPNAME= (SELECT EMPNAME FROM INSERTED)
INSERT INTO LOGTABLE(UserId,Message) values (@EMPNAME,'Record Added')
GO
Following code Explain the magic table "DELETED"
CREATE TRIGGER LogMessage
ON EMP
FOR DELETE
AS
DECLARE @EMPNAME varchar(50)
SELECT @EMPNAME= (SELECT EMPNAME FROM DELETED)
INSERT INTO LOGTABLE(UserId,Message) values (@EMPNAME,'Record Removed')
GO
Sources(All credit to these articles & authors):
http://www.codeproject.com/Questions/285423/what-is-magic-table-different-types-of-magic-table
http://www.dotnetspider.com/resources/29332-Magic-tables-SQL-Server.aspx
.
.
Alternatively
, you can try:
Obtaining Changes by Using the Change Tracking Functions, its MSDN link to explain how
to track changes by using in-built functions.
CHANGETABLE(CHANGES …) function
This rowset function is used to query for change information. The function queries the data stored in the internal change tracking tables. The function returns a results set that contains the primary keys of rows that have changed together with other change information such as the operation, columns updated and version for the row.
CHANGE_TRACKING_CURRENT_VERSION() function
Is used to obtain the current version that will be used the next time when querying changes. This version represents the version of the last committed transaction.
CHANGE_TRACKING_MIN_VALID_VERSION()function
Is used to obtain the minimum valid version that a client can have and still obtain valid results from CHANGETABLE(). The client should check the last synchronization version against the value thatis returned by this function. If the last synchronization version is less than the version returned by this function, the client will be unable to obtain valid results from CHANGETABLE() and will have to reinitialize.
Refer syntax & usage at http://technet.microsoft.com/en-us/library/cc280358%28v=sql.105%29.aspx
Hope it helps.
First: do NOT use a cursor inside a trigger - EVER!
Second: to identify what fields were included in the update statement you can use:
UPDATE() or COLUMNS_UPDATED()
Note: This does NOT list the fields that have had their value changed, just the list of columns included in the SET portion of the UPDATE statement.
Third: There are a variety of methods that you can use to audit changes to your tables (the accepted answer on Most efficient method to detect column change in MS SQL Server has a good list and guidelines on how to use, if you are using SQL Server Enterprise you could also look at using Change Data Capture
Some example audit code I would use (where I want to record a column by column audit table):
INSERT INTO AuditTable (ColumnChanged, OldValue, NewValue) /* I assume there are default columns logging who/when the change was done by? */
SELECT 'ColumnA' as ColumnChanged, d.ColumnA, i.ColumnA
FROM inserted i join deleted d ON d.PKID = i.PKID
WHERE
/* both aren't null and the value has changed */
(d.ColumnA IS NOT NULL AND i.ColumnA IS NOT NULL AND d.ColumnA != i.ColumnA)
/* it was null and now it isn't */
OR (d.ColumnA IS NULL AND i.ColumnA IS NOT NULL)
/* it wasn't null and now it is */
OR (d.ColumnA IS NOT NULL AND i.ColumnA IS NULL)
UNION
SELECT 'ColumnB' as ColumnChanged, d.ColumnB, i.ColumnB
FROM inserted i join deleted d ON d.PKID = i.PKID
WHERE
/* both aren't null and the value has changed */
(d.ColumnB IS NOT NULL AND i.ColumnB IS NOT NULL AND d.ColumnB != i.ColumnB)
/* it was null and now it isn't */
OR (d.ColumnB IS NULL AND i.ColumnB IS NOT NULL)
/* it wasn't null and now it is */
OR (d.ColumnB IS NOT NULL AND i.ColumnB IS NULL)
.... /* continuing for each column */
It would be simpler (from a sql perspective and faster [due to less writes] to audit by row) ie:
INSERT INTO AuditTable (OldValueA, NewValueA, OldValueB, NewValueB)
SELECT d.ColumnA, i.ColumnA, d.ColumnB, i.ColumnB
FROM inserted i join deleted d ON d.PKID = i.PKID
WHERE
/* same check for columnA */
/* both aren't null and the value has changed */
(d.ColumnA IS NOT NULL AND i.ColumnA IS NOT NULL AND d.ColumnA != i.ColumnA)
/* it was null and now it isn't */
OR (d.ColumnA IS NULL AND i.ColumnA IS NOT NULL)
/* it wasn't null and now it is */
OR (d.ColumnA IS NOT NULL AND i.ColumnA IS NULL)
/* now check columnB */
(d.ColumnB IS NOT NULL AND i.ColumnB IS NOT NULL AND d.ColumnB != i.ColumnB)
OR (d.ColumnB IS NULL AND i.ColumnB IS NOT NULL)
OR (d.ColumnB IS NOT NULL AND i.ColumnB IS NULL)
.... /* continuing for each column */
You could make a dynamic "comparer" by converting the inserted and deleted tables into 2 xml, iterating through the properties and comparing the xml value.
Example:
CREATE TRIGGER MY_COMPARER
ON TABLE_NAME
AFTER UPDATE
AS
BEGIN
DECLARE @columnIndex INT = 1;
DECLARE @maxColumns INT = (select count(*) from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TABLE_NAME');
DECLARE @insertedXML XML = (select top 1 * from INSERTED FOR XML PATH('ROOT'), ELEMENTS XSINIL);
DECLARE @deletedXML XML = (select top 1 * from DELETED FOR XML PATH('ROOT'), ELEMENTS XSINIL);
WHILE @columnIndex <= @maxColumns BEGIN
DECLARE @insertedXMLValue XML = (select @insertedXML.query('/ROOT/*[sql:variable("@columnIndex")]'));
DECLARE @deletedXMLValue XML = (select @deletedXML.query('/ROOT/*[sql:variable("@columnIndex")]'));
DECLARE @insertedValue NVARCHAR(MAX) = CONVERT(NVARCHAR(MAX), @insertedXMLProp);
DECLARE @deletedValue NVARCHAR(MAX) = CONVERT(NVARCHAR(MAX), @deletedXMLProp);
IF (@insertedValue != @deletedValue)
print('Column: ' + CONVERT(NVARCHAR(MAX), @columnIndex) + ' has changed')
SET @columnIndex = @columnIndex + 1;
END
END
