CREATE TRIGGER DBO.MattersInsert ON Matters FOR INSERT NOT FOR REPLICATION AS DECLARE @UserId AS CHAR(16) DECLARE @CurrentDate AS DATETIME SELECT @UserId = Professionals FROM CurrentProfessionals WHERE HostID = @@SpID SELECT @CurrentDate = GetDate() INSERT INTO AuditMatters (AuditMatters, AuditDate, AuditType, ColumnLabel, Professionals, SearchAuditDate, Atom, MatterId, AfterValue) SELECT CASE WHEN @UserId IS NULL THEN SUBSTRING(CAST(NEWID() AS CHARACTER(36)),1, 16) ELSE RTrim(SUBSTRING(@UserId, 1, 8)) + SUBSTRING(CAST(NEWID() AS CHARACTER(36)),1, 16 - Len(SUBSTRING(@UserId, 1, 8))) END , @CurrentDate, 'Add', NULL, @UserId, CAST(CAST(YEAR(@CurrentDate) AS varchar(4))+'-'+CAST(MONTH(@CurrentDate) AS varchar(2))+'-'+CAST(DAY(@CurrentDate) AS varchar(2)) AS datetime), INSERTED.Matters , INSERTED.MatterId , '@@'+'Matter Id: ' + INSERTED.MatterId + CASE WHEN INSERTED.ClientSort IS NOT NULL AND INSERTED.ClientSort <> '' AND INSERTED.ClientSort <> '@@' THEN CHAR(13) + CHAR(10) +'Client Sort: ' + INSERTED.ClientSort ELSE '' END + CASE WHEN INSERTED.ShortDesc IS NOT NULL AND INSERTED.ShortDesc <> '' AND INSERTED.ShortDesc <> '@@' THEN CHAR(13) + CHAR(10) +'Description: ' + INSERTED.ShortDesc ELSE '' END + CASE WHEN INSERTED.AreaOfLaw IS NOT NULL AND INSERTED.AreaOfLaw <> '' AND INSERTED.AreaOfLaw <> '@@' THEN CHAR(13) + CHAR(10) +'Area Of Law: ' + INSERTED.AreaOfLaw ELSE '' END + CASE WHEN INSERTED.MatterType IS NOT NULL AND INSERTED.MatterType <> '' AND INSERTED.MatterType <> '@@' THEN CHAR(13) + CHAR(10) +'Matter Type: ' + INSERTED.MatterType ELSE '' END + CASE WHEN INSERTED.OpenedDate IS NOT NULL then CHAR(13)+CHAR(10)+'Opened: ' + CASE WHEN INSERTED.OpenedDate IS NULL THEN '' ELSE CAST(MONTH(INSERTED.OpenedDate) AS varchar(2))+'/'+CAST(DAY(INSERTED.OpenedDate) AS varchar(2))+'/'+CAST(YEAR(INSERTED.OpenedDate) AS varchar(4))+CASE WHEN DATEPART(hh, INSERTED.OpenedDate)+DATEPART(mi, INSERTED.OpenedDate)+DATEPART(ss, INSERTED.OpenedDate)+DATEPART(ms, INSERTED.OpenedDate) <> 0 THEN ' '+CASE WHEN DATEPART(hour, INSERTED.OpenedDate) > 12 THEN CAST(DATEPART(hour, INSERTED.OpenedDate) -12 AS varchar(2)) ELSE CAST(DATEPART(hour, INSERTED.OpenedDate) AS varchar(2)) END+':'+CASE WHEN DATEPART(minute, INSERTED.OpenedDate) <10 THEN '0' ELSE '' END+CAST(DATEPART(minute, INSERTED.OpenedDate) AS varchar(2)) +CASE WHEN DATEPART(hour, INSERTED.OpenedDate) > 12 THEN ' P' ELSE ' A' END+'M' ELSE '' END END ELSE '' END + CASE WHEN INSERTED.Status IS NOT NULL AND INSERTED.Status <> '' AND INSERTED.Status <> '@@' THEN CHAR(13) + CHAR(10) +'Status: ' + INSERTED.Status ELSE '' END + CASE WHEN INSERTED.StatusDate IS NOT NULL then CHAR(13)+CHAR(10)+'Status Date: ' + CASE WHEN INSERTED.StatusDate IS NULL THEN '' ELSE CAST(MONTH(INSERTED.StatusDate) AS varchar(2))+'/'+CAST(DAY(INSERTED.StatusDate) AS varchar(2))+'/'+CAST(YEAR(INSERTED.StatusDate) AS varchar(4))+CASE WHEN DATEPART(hh, INSERTED.StatusDate)+DATEPART(mi, INSERTED.StatusDate)+DATEPART(ss, INSERTED.StatusDate)+DATEPART(ms, INSERTED.StatusDate) <> 0 THEN ' '+CASE WHEN DATEPART(hour, INSERTED.StatusDate) > 12 THEN CAST(DATEPART(hour, INSERTED.StatusDate) -12 AS varchar(2)) ELSE CAST(DATEPART(hour, INSERTED.StatusDate) AS varchar(2)) END+':'+CASE WHEN DATEPART(minute, INSERTED.StatusDate) <10 THEN '0' ELSE '' END+CAST(DATEPART(minute, INSERTED.StatusDate) AS varchar(2)) +CASE WHEN DATEPART(hour, INSERTED.StatusDate) > 12 THEN ' P' ELSE ' A' END+'M' ELSE '' END END ELSE '' END FROM INSERTED