一个存储过程-CSDN博客

-- =============================================
-- Author:  紫雨
-- Create date: 2008-03-15
-- Description: 出入库明细表中增加或者更新记录时，自动更新库存表想关数据和记录；
--              查询比对流状态，如果是审批通过则触发该事件；
-- =============================================
ALTER TRIGGER [dbo].[Tri_Limitation]
   ON [dbo].[Lim_Limitation]
   AFTER insert,update,delete
AS
BEGIN
declare @Dml varchar(20)
declare @RowsD Int
declare @RowsI Int
declare @UpdatedID Int
-- ===============开始变量声明========================================
SET NOCOUNT ON;
--确定是哪一种dml操作
Select @RowsD=Count(*) From Deleted
Select @RowsI=Count(*) From Inserted
If @RowsD=0 And @RowsI=0
    Goto Exit_
If @RowsD=0 And @RowsI>0
    Set @Dml='Insert'
Else
   If @RowsD>0 And @RowsI>0
       Set @Dml='Update'
   Else
       If @RowsD>0 And @RowsI=0
           Set @Dml='Delete'

if @@rowcount = 0 --如果影响的行数为 0，则结束触发器运行，避免占用资源
      return
if (@Dml='Delete')
begin
INSERT INTO [FrameWork].[dbo].[Lim_Limitation_his] ([LimitationType],[CreateDate],[CreateBy]
,[ModifyDate],[Modifyby],[FileRevision],[OrderBy],[FlowState],[ProjectName],[OS],[GADataOfSystem]
,[IssueTitle],[CMVC],[CateGorey],[Severity],[Priority],[EndDate],[TestPhase],[ReleaseName]
,[Description_],[PermanentLimitation],[futureFixweb],[InternalFixTarget],[FixReleasedWeb],[InternalFixReleased]
,[FixIncludedinGSsystem],[FixedQFEBIOSDDwversion],[IsHintTipneeded],[IfyesInfoTipsDocumentNumber]
,[MSLogoImpact],[ProblemFrequency],[HowIntermittent],[Ownere],[ExpectedFix],[RootCauseSuspected]
,[Wordaround],[FiledProbleminthepast],[FiledProbleminthepastComment],[otherprodut],[RemarkComments]
,[Link],[Experienceif],[ExperienceComment],[CurrentAudiNum],[CurrentAudiState],[AudiManNum],[FieldBak01]
,[FieldBak02],[FieldBak03],[FieldBak04],[FieldBak05],[FieldBak06],[FieldBak07],[FieldBak08],[FieldBak09]
,[FieldBakN01],[FieldBakN02],[FieldBakN03],[FieldBakN04],[FieldBakN05],[LimTationID],sqlcomm, exectime)
   SELECT [LimitationType]
      ,[CreateDate]
      ,[CreateBy]
      ,[ModifyDate]
      ,[Modifyby]
      ,[FileRevision]
      ,[OrderBy]
      ,[FlowState]
      ,[ProjectName]
      ,[OS]
      ,[GADataOfSystem]
      ,[IssueTitle]
      ,[CMVC]
      ,[CateGorey]
      ,[Severity]
      ,[Priority]
      ,[EndDate]
      ,[TestPhase]
      ,[ReleaseName]
      ,[Description_]
      ,[PermanentLimitation]
      ,[futureFixweb]
      ,[InternalFixTarget]
      ,[FixReleasedWeb]
      ,[InternalFixReleased]
      ,[FixIncludedinGSsystem]
      ,[FixedQFEBIOSDDwversion]
      ,[IsHintTipneeded]
      ,[IfyesInfoTipsDocumentNumber]
      ,[MSLogoImpact]
      ,[ProblemFrequency]
      ,[HowIntermittent]
      ,[Ownere]
      ,[ExpectedFix]
      ,[RootCauseSuspected]
      ,[Wordaround]
      ,[FiledProbleminthepast]
      ,[FiledProbleminthepastComment]
      ,[otherprodut]
      ,[RemarkComments]
      ,[Link]
      ,[Experienceif]
      ,[ExperienceComment]
      ,[CurrentAudiNum]
      ,[CurrentAudiState]
      ,[AudiManNum]
      ,[FieldBak01]
      ,[FieldBak02]
      ,[FieldBak03]
      ,[FieldBak04]
      ,[FieldBak05]
      ,[FieldBak06]
      ,[FieldBak07]
      ,[FieldBak08]
      ,[FieldBak09]
      ,[FieldBakN01]
      ,[FieldBakN02]
      ,[FieldBakN03]
      ,[FieldBakN04]
      ,[FieldBakN05],[ID], @Dml, {fn NOW()}
FROM deleted
--* A:删除Lim_ProjectFlow表中相应的数据
delete Lim_ProjectFlow where LimitationID=any(select id from deleted)
end
if ((@Dml='Insert') or (@Dml='Update'))
begin
--如果不是更新 FlowState并且不是更新AudiManNum 则写入历史表，同时清空两个字段状态；
if ((not Update(FlowState)) and (not Update(AudiManNum)))
begin
INSERT INTO [FrameWork].[dbo].[Lim_Limitation_his] ([LimitationType],[CreateDate],[CreateBy]
,[ModifyDate],[Modifyby],[FileRevision],[OrderBy],[FlowState],[ProjectName],[OS],[GADataOfSystem]
,[IssueTitle],[CMVC],[CateGorey],[Severity],[Priority],[EndDate],[TestPhase],[ReleaseName]
,[Description_],[PermanentLimitation],[futureFixweb],[InternalFixTarget],[FixReleasedWeb],[InternalFixReleased]
,[FixIncludedinGSsystem],[FixedQFEBIOSDDwversion],[IsHintTipneeded],[IfyesInfoTipsDocumentNumber]
,[MSLogoImpact],[ProblemFrequency],[HowIntermittent],[Ownere],[ExpectedFix],[RootCauseSuspected]
,[Wordaround],[FiledProbleminthepast],[FiledProbleminthepastComment],[otherprodut],[RemarkComments]
,[Link],[Experienceif],[ExperienceComment],[CurrentAudiNum],[CurrentAudiState],[AudiManNum],[FieldBak01]
,[FieldBak02],[FieldBak03],[FieldBak04],[FieldBak05],[FieldBak06],[FieldBak07],[FieldBak08],[FieldBak09]
,[FieldBakN01],[FieldBakN02],[FieldBakN03],[FieldBakN04],[FieldBakN05],[LimTationID],sqlcomm, exectime)
   SELECT [LimitationType]
      ,[CreateDate]
      ,[CreateBy]
      ,[ModifyDate]
      ,[Modifyby]
      ,[FileRevision]
      ,[OrderBy]
      ,[FlowState]
      ,[ProjectName]
      ,[OS]
      ,[GADataOfSystem]
      ,[IssueTitle]
      ,[CMVC]
      ,[CateGorey]
      ,[Severity]
      ,[Priority]
      ,[EndDate]
      ,[TestPhase]
      ,[ReleaseName]
      ,[Description_]
      ,[PermanentLimitation]
      ,[futureFixweb]
      ,[InternalFixTarget]
      ,[FixReleasedWeb]
      ,[InternalFixReleased]
      ,[FixIncludedinGSsystem]
      ,[FixedQFEBIOSDDwversion]
      ,[IsHintTipneeded]
      ,[IfyesInfoTipsDocumentNumber]
      ,[MSLogoImpact]
      ,[ProblemFrequency]
      ,[HowIntermittent]
      ,[Ownere]
      ,[ExpectedFix]
      ,[RootCauseSuspected]
      ,[Wordaround]
      ,[FiledProbleminthepast]
      ,[FiledProbleminthepastComment]
      ,[otherprodut]
      ,[RemarkComments]
      ,[Link]
      ,[Experienceif]
      ,[ExperienceComment]
      ,[CurrentAudiNum]
      ,[CurrentAudiState]
      ,[AudiManNum]
      ,[FieldBak01]
      ,[FieldBak02]
      ,[FieldBak03]
      ,[FieldBak04]
      ,[FieldBak05]
      ,[FieldBak06]
      ,[FieldBak07]
      ,[FieldBak08]
      ,[FieldBak09]
      ,[FieldBakN01]
      ,[FieldBakN02]
      ,[FieldBakN03]
      ,[FieldBakN04]
      ,[FieldBakN05],[ID], @Dml, {fn NOW()}
FROM inserted

update Lim_Limitation set flowstate='',audimannum=0 where id=any(select id from inserted)
--C:把Lim_ProjectFlow表中相应的记录的AudiState(审批状态)和AudiNote（审批意见)清空
update Lim_ProjectFlow set Audistate='',audinote='' where LimitationID=any(select id from inserted)
end
End
-- =========结束库存变更处理===========
-- ===============主体代码结束========================================
EXIT_:
END