第一次接触这个函数，贴出来供大家参考下。ALTER TRIGGER [dbo].[DocumentStatisticsAdd] ON [dbo].[BatchDocument] FOR INSERT,UPDATEAS BEGIN
MERGE INTO DocumentStatistics AS dsTarget USING (SELECT I.[LastUser],I.[IsBackFromAARTO], BS.BaDoStName, DoTeName=CASE WHEN dt.DoTeTypeID='TMS_Section341' THEN 'S341' WHEN dt.DoTeTypeID='TMS_Section56' THEN 'S56' WHEN dt.DoTeTypeID='TMS_ProvincialSection56' THEN 'ProvincialS56' ELSE '' END FROM Inserted I,dbo.BatchDocumentStatus BS,dbo.Batch B,dbo.DocumentTemplate DT WHERE I.BaDoStID=BS.BaDoStID AND I.BatchID=B.BatchID AND DT.DoTeID=B.DoTeID AND BS.BaDoStID=2 --BS.BaDoStName='Scanned' ) as dsSource ON dsTarget.DoStUser = dsSource.LastUser AND dsTarget.DoStType=dsSource.DoTeName AND dsTarget.DoStDate=CONVERT(varchar(100), GETDATE(), 112) AND dsTarget.DoStHour = DATEPART(hh,GETDATE()) WHEN MATCHED THEN UPDATE SET dsTarget.DoStScan = dsTarget.DoStScan + 1,dsTarget.DoStLastUpdateDate=GETDATE(), dsTarget.LastUser=dsTarget.LastUser WHEN NOT MATCHED THEN INSERT(DoStDate,DoStHour,DoStUser,DoStType,DoStScan,DoStEvidence ,DoStCapture,DoStValidate ,DoStQC,DoStExport,DoStFailed,DoStPark,DoStError,DoStCreateDate,DoStLastUpdateDate,LastUser) VALUES(CONVERT(varchar(100), GETDATE(), 112), DATEPART(hh,GETDATE()), dsSource.LastUser,dsSource.DoTeName,1,0,0,0,0,0,0,0,0,GETDATE(),GETDATE(),dsSource.LastUser);
END
转载于:https://www.cnblogs.com/lindasoft/archive/2013/01/07/2849327.html
