插入和更新触发器

68 阅读 0 评论 45 点赞

我是靠谱客的博主辛勤睫毛，最近开发中收集的这篇文章主要介绍插入和更新触发器，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:        触发器
-- Create date: 2011-2-28
-- Description:
-- =============================================
ALTER TRIGGER [ RECORDCODEROOM_28_Inser ]
    ON    [ dbo ] . [ RECORDCODEROOM_28 ]
for insert
   AS
BEGIN
    SET NOCOUNT ON ;
    declare @id uniqueidentifier
    select @id = id from inserted
    if not exists ( select * from [ WTC_TB_RECORDCODEROOM ] with (nolock) where id = @id )
    INSERT INTO [ ytSummitTeleConf_DB ] . [ dbo ] . [ WTC_TB_RECORDCODEROOM ]
           ( [ ID ]
           , [ SeqNo ]
           , [ ConfRoom ]
           , [ RecordCode ]
           , [ AddTime ] )
       select
             [ ID ]
           , [ SeqNo ]
           , [ ConfRoom ]
           , [ RecordCode ]
           , [ AddTime ] from inserted

END

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER trigger [ RECORDCODEROOM_28_Update ]
on [ dbo ] . [ RECORDCODEROOM_28 ]
for update
as
begin

declare @id uniqueidentifier ,
@seqno int ,
@confroom varchar ( 50 ),
@recordcode varchar ( 50 ),
@addtime datetime

       select
             @id = id
           , @seqno = [ SeqNo ]
           , @ConfRoom = [ ConfRoom ]
           , @RecordCode = [ RecordCode ]
           , @AddTime = [ AddTime ]
            from inserted
    IF not exists ( select 1 from [ WTC_TB_RECORDCODEROOM ] with (nolock) where id = @id )
     Begin
    INSERT INTO [ ytSummitTeleConf_DB ] . [ dbo ] . [ WTC_TB_RECORDCODEROOM ]
           ( [ ID ]
           , [ SeqNo ]
           , [ ConfRoom ]
           , [ RecordCode ]
           , [ AddTime ] )
         select
             [ ID ]
           , [ SeqNo ]
           , [ ConfRoom ]
           , [ RecordCode ]
           , [ AddTime ]
         from inserted
     End
    ELSE
     Begin
      update [ WTC_TB_RECORDCODEROOM ]
        set    [ SeqNo ] = @seqno
           , [ ConfRoom ] = @ConfRoom
           , [ RecordCode ] = @RecordCode
           , [ AddTime ] = @AddTime
      Where id = @id ;
     End
end

转载于:https://www.cnblogs.com/callbin/archive/2011/03/01/1968227.html