CODEWITHSUNDEEP
sqlt-sqltriggers
Tomas Fiala
Tomas Fiala

Reputation: 13

tsql trigger which should only run if an update is made on specifc column

Basically I have two tables in the same SQL database. [table_1] & [table_2]

[Label] is the Primary Key on both tables

[table_1]'s design is

 [Label] [varchar](50) NOT NULL
 [Use_Date] [date] NULL
 [Sent_Date] [date] NULL
 [Checked_Date] [date] NULL

[table_2]'s design is

 [Label] [varchar](50) NOT NULL
 [DateFirstRegistered] [date] NULL
 [LastUsedDate] [date] NULL
 [UsageCount] [int] NULL

If there is a update on [use_date] in [table_1] the trigger needs to update two columns [LastUsedDate] & [UsageCount] in [table 2]. Any changes in other columns of [table_1] should process without affecting [table_2].

I have a trigger that works when the use_date is updated in [table_1]... but if it is not, it causes submit issues when other columns are updated.

Here is the trigger mentioned

USE [DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[trgOnTable1Update] ON [dbo].[Table_1]
AFTER UPDATE
AS
BEGIN

       UPDATE c
       SET           c.LastUsedDate=a.Use_Date,
                     c.UsageCount=c.UsageCount+1
       FROM   inserted a
                     INNER JOIN deleted b ON a.Label=b.Label
                     INNER JOIN [dbo].[Table_2] c ON a.Label=c.Label
                     WHERE  a.Use_Date<>b.Use_Date

END

I know that I am missing some logical flaw in there but for the life of me I cannot see it... Any help on how to get this working will be greatly appreciated.

Thanks in advance.

Upvotes: 1

Views: 222

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107686

Two things to be mindful of:

  1. Add SET NOCOUNT ON to prevent the rowcount to be reported from within the trigger. Your client program may not be expecting the 2nd resultset, or it could be interpreting this 2nd rowcount instead of the actual originating UPDATE statement, i.e. it may see "0 rows affected" and think the original UPDATE failed.

  2. If you are updating from NULL -> date, the trigger doesn't cater for it. This may not be an issue if you never start it off with NULL.

CREATE TRIGGER [dbo].[trgOnTable1Update] ON [dbo].[Table_1]
AFTER UPDATE
AS
BEGIN
       SET NOCOUNT ON;
       UPDATE c
          SET LastUsedDate=a.Use_Date,
              UsageCount=c.UsageCount+1
         FROM inserted a
         JOIN deleted b ON a.Label=b.Label
         JOIN [dbo].[Table_2] c ON a.Label=c.Label
        WHERE a.Use_Date<>isnull(b.Use_Date,0)
END

Upvotes: 1

Related Questions