Data modification - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Data modification

Author

Topic

VGuyz
Posting Yak Master

121 Posts

Posted - 2008-11-12 : 01:57:58

Dear all,

I want to know the data modified in a column of any table.
For eg.,

Table1

Sno EmployeeName Designation
1 mark Senior manager
2 Michel manager
3 Louis Senoir supervisor
4 Milan Supervisor

Now i have updated the employee Louis designation to 'Manager' from 'senior supervisor'.

Sno EmployeeName Designation
1 mark Senior manager
2 Michel manager
3 Louis Manager
4 Milan Supervisor

How can i get the data before modification. (i.e.,)
I want to maitain a log for any updation done in any table.

Kindly give me any idea regarding this scenario.

Thanks in advance..,

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2008-11-12 : 02:08:59

You can archive your target by creating Update Trigger on required table and through this trigger can save your ex-copy of data in a history table.

VGuyz
Posting Yak Master

121 Posts

Posted - 2008-11-12 : 02:13:16

Is there any system tables to maintain these types of modification as a log.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2008-11-12 : 02:13:48

CREATE TRIGGER [trg_UpdateYOURTABLE]
ON [YOURTABLE]
AFTER UPDATE
AS
BEGIN

INSERT INTO rhz.HistoryYOURTABLE(Sno EmployeeName Designation)
SELECT Sno EmployeeName Designation FROM INSERTED

END

VGuyz
Posting Yak Master

121 Posts

Posted - 2008-11-12 : 02:20:27

Ya you are right i'm having more than 100 master tables, how can i create trigger for all the tables.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 02:24:13

need to create seperate trigger for each as

CREATE TRIGGER ON TableName
FOR INSERT,UPDATE,DELETE
AS
BEGIN
--your code logic here
END

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2008-11-12 : 02:31:52

visakh is right....

But keep in mind that SQL Server don't provide any facility like Oracle Total Recall, which maintains your data change history and you can recall your previous image of committed data.

VGuyz
Posting Yak Master

121 Posts

Posted - 2008-11-12 : 02:37:08

K visakh , If i'm using below mentioned trigger means, How can i
mention my code here,i'm using Stored procedures to Insert/Update for all tables ,can i inherit those SP's in the trigger.i'm new to sql don't mistake me..Expecting u'r reply..:)

CREATE TRIGGER ON TableName
FOR INSERT,UPDATE,DELETE
AS
BEGIN
--your code logic here
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 03:47:19

quote:
Originally posted by VGuyz

K visakh , If i'm using below mentioned trigger means, How can i
mention my code here,i'm using Stored procedures to Insert/Update for all tables ,can i inherit those SP's in the trigger.i'm new to sql don't mistake me..Expecting u'r reply..:)

CREATE TRIGGER ON TableName
FOR INSERT,UPDATE,DELETE
AS
BEGIN
--your code logic here
END

inside begin end you can use the internal table INSERTED and DELETED to get old and new values from your table.
For each trigger created, when trigger action(INSERT,DELETE,UPDATE) happens on table on which it is defined, the trigger fires and one of following happens

1. If its INSERT, the INSERTED table will contain the new values that you inserted
2. If UPDATE, the INSERTED table will contain new values and DELETED will contain old values
3. If DELETE, the deleted values will be available in DELETED table.

so using above you can log the values to log table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources