I've written an After Update Trigger on tblVisitLog when a specific field, TimeOUT gets updated a new record is INSERTED in tblClinicalDoc. There is a possibility that a record with an identifying ID (Encounter_code) is already in the Target Table, so I added an IF EXIST and an IF NOT EXISTS for the UPDATE or INSERT statements.
I'm sure there must be a more elegant and efficient way of writing this trigger than what I have. Here is what I have:
USE [test_db1]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[CreateRowInClinicalDoc]
ON [dbo].[tblVisitLog]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF UPDATE(TimeOUT)
IF EXISTS (SELECT c.Encounter_code FROM tblClinicalDoc c
INNER JOIN INSERTED i ON c.Encounter_code = i.Encounter_code
WHERE I.TimeOut IS NOT NULL)
UPDATE tblClinicalDoc
SET EditDate = GetDate()
FROM INSERTED i
INNER JOIN tblClinicalDoc c ON c.Encounter_code = i.Encounter_code
END
BEGIN
SET NOCOUNT ON;
IF NOT EXISTS (SELECT c.Encounter_code FROM tblClinicalDoc c
INNER JOIN INSERTED i ON c.Encounter_code = i.Encounter_code
WHERE I.TimeOut IS NOT NULL)
INSERT INTO tblClinicalDoc
SELECT DISTINCT v.CaseNumber AS CaseNumber, v.Episode AS Episode, v.Encounter_code AS Encounter_code, v.Provider AS Provider,
(r.Discipline + '_'+ r.ReportType) AS Note_Type, (r.Discipline + '_' +r.ReportType + ' - ' + v.Case_Name + ' ' + convert(nvarchar(10),
v.TimeIN, 112)) AS Note_Synopsis,
(UPPER(w.ClientCode) + '_' + v.Discipline + '_' + v.VisitType + '_' + RIGHT('0000000' + CONVERT(VARCHAR,v.Encounter_code), 7)+ '_' + convert(nvarchar(10), v.TimeIN, 112) + '.pdf') AS Document_Name,
GetDate() AS EditDate, v.Provider AS EditBy, '' AS InterfaceDate, '' AS InterfaceAgent, '-2' AS Sent, 0 AS Ack, '' AS Document_ID, '' AS Confired
FROM inserted v
INNER JOIN [WebLoginSelector].[dbo].LookupDatabaseCode w ON DB_NAME() = w.ClientDatabaseCode
INNER JOIN LookupReportTypes r ON v.Discipline = r.Discipline AND v.VisitType = r.VT
WHERE v.TimeOUT IS NOT NULL
END
GO
Thanks in advance for all replies,
JackW9653
