CODEWITHSUNDEEP
sqlsql-servertriggers
vishal mishra
vishal mishra

Reputation: 13

how to maintain table log (every changes) history in MS SQL by write trigger

We just want to maintain table log (every changes) history in MS SQL by write trigger please suggest

i tried but not working 

CREATE TRIGGER [dbo].[update_ServiceDescriptionTable]
ON ServiceDescriptionMaster
After UPDATE
AS
BEGIN
        declare @Rate money;
        Select @Rate = Rate from inserted;

        update [dbo].[ServiceDescriptionMasterlog] set Rate = @Rate 
        where Service_Description = '';
END

Upvotes: 1

Views: 79

Answers (1)

Shamim
Shamim

Reputation: 26

Ya good. If you want to maintain evry changes log then you can insert in same log table with all field like as follows:

1) create same table like "ServiceDescriptionMasterlog" with one Extra Field (Column) Entry_DateTime set default bind getdate() method.

2) write a trigger on "ServiceDescriptionMaster" table as follows:

ALTER TRIGGER [dbo].[ServiceDescriptionMaster_OnUpdate]
ON [dbo].[ServiceDescriptionMaster]
After UPDATE
AS
BEGIN
       SET NOCOUNT ON; 
       INSERT INTO [dbo].[ServiceDescriptionMasterLog]
       (S_No,Rate,.....)
       select S_No,Rate,.....
       from Deleted;
END

you can also maintain on delete:

ALTER TRIGGER [dbo].[ServiceDescriptionMaster_OnDelete]
ON [dbo].[ServiceDescriptionMaster]
For Delete
AS
BEGIN
       SET NOCOUNT ON; 
       INSERT INTO [dbo].[ServiceDescriptionMasterLog]
       (S_No,Rate,.....)
       select S_No,Rate,.....
       from Deleted;
END

Upvotes: 1

Related Questions