sql server 函數與觸發器的使用

sql server中觸發器的使用

--從暫存資料表中尋找指定的專家記錄是否存在
create function F_EXPERT_EXIST_IN_TEMP(
   @ExpertId numeric(9),
   @SqlType numeric(9),
   @Trs_flag numeric(9)

)
returns int
as
begin
    declare @ret int
    select @ret = 0
      select @ret = count(*) from [86_xiehe_time$_temp]
      where EXPERTID = @ExpertId and SQL_TYPE = @SqlType and TRS_FLAG = @Trs_flag
    return @ret
end

 

--工作經曆觸發器(只有專家才有工作經曆)
 --新增
 CREATE TRIGGER [WorkHistory_I_TRIG] ON [XWCMWORKHISTORY] FOR INSERT AS
   BEGIN
  --需要判斷在暫存資料表中是否已經存在  
  declare @nIsExist int
  select @nIsExist = DBO.F_EXPERT_EXIST_IN_TEMP(OBJID,1,0) from INSERTED
  if(@nIsExist>0)
   return
  --不存在的話則添加到暫存資料表中
      INSERT INTO [86_xiehe_time$_temp] (EXPERTID,SQL_TYPE,TRS_FLAG)
      SELECT OBJID,1,0 FROM INSERTED
   END

GO
 
 --修改
 CREATE TRIGGER [WorkHistory_U_TRIG] ON [XWCMWORKHISTORY] FOR UPDATE AS
   BEGIN
  --需要判斷更新前的動作表中有記錄  
  declare @nIsExist int
  select @nIsExist = DBO.F_EXPERT_EXIST_IN_TEMP(OBJID,3,0) from DELETED
  if(@nIsExist=0)
   begin
    --不存在的話則添加到暫存資料表中
        INSERT INTO [86_xiehe_time$_temp] (EXPERTID,SQL_TYPE,TRS_FLAG)
       SELECT OBJID,3,0 FROM DELETED
   end

  --判斷是否需要添加到更新後的表
  select @nIsExist = DBO.F_EXPERT_EXIST_IN_TEMP(OBJID,1,0) from INSERTED
  if(@nIsExist=0)
   begin
        INSERT INTO [86_xiehe_time$_temp] (EXPERTID,SQL_TYPE,TRS_FLAG)
        SELECT OBJID,1,0 FROM INSERTED
   end
   END

GO
 
 --刪除
 CREATE TRIGGER [WorkHistory_D_TRIG] ON [XWCMWORKHISTORY] FOR DELETE AS
   BEGIN
  --需要判斷在暫存資料表中是否已經存在  
  declare @nIsExist int
  select @nIsExist = DBO.F_EXPERT_EXIST_IN_TEMP(OBJID,3,0) from DELETED
  if(@nIsExist>0)
   return

  --不存在的話則添加到暫存資料表中
      INSERT INTO [86_xiehe_time$_temp] (EXPERTID,SQL_TYPE,TRS_FLAG)
      SELECT OBJID,3,0 FROM DELETED
   END

GO