SQL Server Getting Column Name

8yparm6h  于 2023-04-04  发布在  其他
关注(0)|答案(3)|浏览(196)
Sr. ID   FName    LName    Mark
1   P1   amar     ranjan   100
2   P2   sameer   kumar    200
3   P1   amar     ranjan   200

Here I updated the first row for Mark column.

On each update to a single row I am creating the new row (see row 3) with updated value. Is it possible to get the column name which have been updated in the table?

zqdjd7g9

zqdjd7g91#

I used Mahmoud Gamal's answer and SQL Fiddle demo as a starting point.

What you need is possible, but if you have access to the update processes I would recommand to manage the updates in any a stored procedure which saved the changes directly in a table.

If you need a trigger, something like this can work, but as you see, it has it's own drawbacks: two temporary table because of the scope and a cursor.

It basicly generates a check for each column each time and inserts the new and old values for a column into the history table.

(a change on the ID column to Primary Key and Identity is recommanded too)

I also made an SQL FIDDLE demo

CREATE TABLE TableName (Sr INT, ID VARCHAR(10), FName VARCHAR(50),
LName VARCHAR(50), Mark VARCHAR(50));

CREATE TABLE HistoryTable (ID VARCHAR(10), ColumnName VARCHAR(50),
PrevValue VARCHAR(50), NewValue VARCHAR(50));


INSERT INTO TableName VALUES
(1, 'P1', 'amar', 'ranjan' , 100),
(2, 'P2', 'sameer', 'kumar', 200),
(3, 'P1', 'amar', 'ranjan', 200);


CREATE TRIGGER TableNameTrigger
ON TableName
AFTER UPDATE
AS 

SELECT * INTO #Deleted  FROM Deleted 
SELECT * INTO #Inserted FROM Inserted  

DECLARE @ColName varchar(64)
DECLARE columnCursor CURSOR FOR
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TableName' AND TABLE_SCHEMA='dbo'

OPEN columnCursor

FETCH NEXT FROM columnCursor INTO @ColName

WHILE (@@FETCH_STATUS <> -1)
BEGIN

EXEC(N'INSERT INTO HistoryTable 
     SELECT Deleted.Sr, ''' + @ColName + ''', Deleted.'+ @ColName + ',' + 'Inserted.' + @ColName +' 
     FROM #Deleted Deleted
     INNER JOIN #Inserted Inserted ON Inserted.Sr = Deleted.Sr
     WHERE Deleted.'+ @ColName + ' != Inserted.' + @ColName)



FETCH NEXT FROM columnCursor INTO @ColName

END

DROP TABLE #Deleted 
DROP TABLE #Inserted 

CLOSE columnCursor

DEALLOCATE columnCursor

GO
ep6jt1vc

ep6jt1vc2#

Since you are using SQL Server 2008, you can use the OUTPUT clause to get the updated data. Something like:

UPDATE TableName
  SET Sr = 'foo',
      ID    = someid,
      FName = 'bar',
      LName = 'other foo',
      Mark  = someid
OUTPUT
  inserted.FName,
  deleted.FName AS oldFName,
  inserted.FName AS newFName
WHERE SR = 1; -- for mark;

However if you need to insert ito another table History the data that was you can create a AFTER UPDATETRIGGER to update the history table by the updated data, somthing like:

USE DatabaseName;
GO
IF OBJECT_ID ('Sch.TableNameTrigger', 'TR') IS NOT NULL
   DROP TRIGGER Sch.TableNameTrigger;
GO
CREATE TRIGGER Sch.TableNameTrigger
ON FirstTable
AFTER UPDATE
AS 
    INSERT INTO HistoryTable
    SELECT * FROM DELETED
GO

SQL Fiddle Demo

l0oc07j2

l0oc07j23#

I would say, create another table called history and store what is updated & when.

Making duplicate entries in one table is BAD design. ALso I am not sure how you are handling duplicate entries?

Lets say you want to print data for P1 how you are handling that?

Edit 1

In one of my website, client wanted to know who added/ edited/ delete what and when. So I created new table called HISTORY and once add/ edit/ delete is done, I am making entry of that in that table. Creating many rows for one user is BAD design.

相关问题