We help IT Professionals succeed at work.

Looking for code to save changes into log table #2

Hi Experts,

I'm looking for a simple trigger (in T-SQL) that will check if a particular field was changed, should insert a row into log table.

Table name = Skilled_Nursing_Visit_Notes
FieldName = Visit_Date
LogTable = SNVN_Log
Primary Key = SNV_ID

Thanks
Comment
Watch Question

Ryan ChongSoftware Tead Lead / Business Analyst / System Analyst / Data Engineer
SILVER EXPERT

Commented:

try create your log table such as:


CREATE TABLE [dbo].[SNVN_Log](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [rec_id] [int] NULL,
    [Visit_Date_prevValue] [datetime] NULL,
    [Visit_Date_currValue] [datetime] NULL,
    [Added] [datetime] NULL,
 CONSTRAINT [PK_SNVN_Log] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[SNVN_Log] ADD  CONSTRAINT [DF_SNVN_Log_Added]  DEFAULT (getdate()) FOR [Added]
GO


then create a Trigger like this:


Alter trigger tr_Skilled_Nursing_Visit_Notes on Skilled_Nursing_Visit_Notes
after update
as
begin
   set nocount on;

   if exists(select * from inserted i inner join deleted d on i.id = d.id where d.Visit_Date <> i.Visit_Date)
       insert into SNVN_Log
       (rec_id, Visit_Date_prevValue, Visit_Date_currValue)
       select i.id, d.Visit_Date, i.Visit_Date from inserted i inner join deleted d on i.id = d.id
end
go
BRONZE EXPERT

Author

Commented:
Hi,

Tried

Alter trigger tr_Skilled_Nursing_Visit_Note on Skilled_Nursing_Visit_Note
after update
as
begin
   set nocount on;

   if exists(select * from inserted i inner join deleted d on i.id = d.id where d.Visit_Date <> i.Visit_Date)
       insert into SNVN_Log
       (snv_id, Visit_Date_prevValue, Visit_Date_currValue)
       select i.id, d.Visit_Date, i.Visit_Date from inserted i inner join deleted d on i.id = d.id
end
go

Open in new window


Got

Msg 208, Level 16, State 6, Procedure tr_Skilled_Nursing_Visit_Note, Line 8
Invalid object name 'tr_Skilled_Nursing_Visit_Note'.

Thanks,
Ben
Software Tead Lead / Business Analyst / System Analyst / Data Engineer
SILVER EXPERT
Commented:

Invalid object name 'tr_Skilled_Nursing_Visit_Note'.                                    


try change:


Alter trigger


to:


Create trigger


BRONZE EXPERT

Author

Commented:
Thank you!