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