mssql触发器的经典例子:更新触发器

更新某字段的值等于多少时触发。

USE [kmgaj]
GO
/****** Object:  Trigger [dbo].[update_dt_article]    Script Date: 03/08/2015 19:55:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,大王>
-- Create date: <2015-3-8>
-- Description:	<数据中心:www.yn137.com,试验室:lab.yn137.com>
-- =============================================
ALTER TRIGGER [dbo].[update_dt_article]
   ON  [dbo].[dt_article]
   AFTER UPDATE
AS 
  declare @s int;--定义状态
  declare @user_name varchar(255);--定义插入用户名
  if update([status])
  begin
     select @s=status,@user_name=user_name from inserted;
  end
BEGIN
    if @s=0--如果状态是0说明是通过则更新
    begin
      update dt_count set note=@s,up_time=getdate(),pass_num=pass_num+1 where user_name=@user_name;
    end 
    
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for trigger here

END

USE [kmgaj]

/****** Object: Trigger [dbo].[update_dt_article] Script Date: 03/08/2015 19:55:55 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Author: <Author,大王>

-- Create date: <2015-3-8>

-- Description: <数据中心:www.yn137.com,试验室:lab.yn137.com>

-- =============================================

ALTER TRIGGER [dbo].[update_dt_article]

ON [dbo].[dt_article]

AFTER UPDATE

declare @s int;--定义状态

declare @user_name varchar(255);--定义插入用户名

if update([status])

begin

select @s=status,@user_name=user_name from inserted;

end

BEGIN

if @s=0--如果状态是0说明是通过则更新

begin

update dt_count set note=@s,up_time=getdate(),pass_num=pass_num+1 where user_name=@user_name;

end

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for trigger here

END

转载请注明：中国翼通 » mssql触发器的经典例子:更新触发器

分享到：

与本文相关的文章