CODEWITHSUNDEEP
sql-server
user2342574
user2342574

Reputation: 107

how to use update trigger in sql server 2008 with specific column

Hello friends currently my trigger updates on table update, and I need to change this to only fire when specific column changes.

   /****** Object:  Table [dbo].[User_Detail]   ******/


SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON GO

   CREATE TABLE [dbo].[User_Detail](
  [sno] [int] IDENTITY(1,1) NOT NULL,
  [userid] [nvarchar](50) NULL,
  [name] [nvarchar](max) NULL,
  [jointype] [nvarchar](50) NULL,
  [joinside] [nvarchar](50) NULL,
  [lleg] [nvarchar](50) NULL,
  [rleg] [nvarchar](50) NULL,
  [ljoining] [int] NULL,
  [rjoining] [int] NULL,
  [pair] [int] NULL
    ) ON [PRIMARY]

  GO

/** Object: Table [dbo].[User_Detail] table data **/

 SET IDENTITY_INSERT [dbo].[User_Detail] ON
  INSERT [dbo].[User_Detail] values (1, N'LDS', N'LDS Rajput', N'free', N'Left', N'jyoti123', N'SUNIL', 6, 4, 4)
  INSERT [dbo].[User_Detail]  VALUES (2, N'jyoti123', N'jyoti rajput', N'free', N'Left', N'mhesh123', N'priya123', 3, 2, 2)

 SET IDENTITY_INSERT [dbo].[User_Detail] OFF

/** Object: Table [dbo].[User_Detail] trigger **/

CREATE TRIGGER triggAfterUpdate ON User_Detail
 FOR UPDATE
     AS
        declare @userid nvarchar(50);
        declare @pair varchar(100);

        select @userid=i.userid from inserted i;    
        select @pair=i.pair from inserted i;    

        SET NOCOUNT ON
        if update(pair)

        begin   
        insert into Complete_Pairs(userid,pair) 
        values(@userid,1);
  end

 GO


/****** Object:  Table [dbo].[Complete_Pairs]    Script Date: 05/22/2014 21:20:35 ******/

SET ANSI_NULLS ON GO

   SET QUOTED_IDENTIFIER ON
   GO

 CREATE TABLE [dbo].[Complete_Pairs](
[Sno] [int] IDENTITY(1,1) NOT NULL,
[userid] [nvarchar](50) NULL,
[pair] [int] NULL
 ) ON [PRIMARY]

GO

my query is TRIGGER triggAfterUpdate is fired only when pair column in User_Details table is update only and when we update other column like ljoin or rjoin then my trigger is not fired

please any one can suggest us how it can done or provide solution

Upvotes: 0

Views: 1137

Answers (1)

Martijn van Put
Martijn van Put

Reputation: 3313

There is no solution to fire a trigger on a certain column condition. You can test if the column is updated by using the IF UPDATE construction you already using.

See: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/3763ff31-1e28-435f-be67-116655821df6/how-to-create-column-level-trigger?forum=transactsql

Upvotes: 0

Related Questions