Solved

Fire trigger when a certain column changes

Posted on 2014-10-30
11
189 Views
Last Modified: 2014-10-31
Hi guys,

I have the following query....

USE [Database]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[Ascent_UpdateSupplierStatus]
ON [dbo].[ApSupplier]
AFTER INSERT,UPDATE
AS
  
INSERT INTO [TriggerTablesAscent].[dbo].[SupplierStatus](SupplierCode, ActiveStatus, DateUpdated, UpdatedBy)
    SELECT
         i.Supplier,
           CASE i.OnHold WHEN 'Y' THEN 1 ELSE 0 END,
         CURRENT_TIMESTAMP, 
         SYSTEM_USER

    FROM
         inserted as i LEFT OUTER JOIN [TriggerTablesAscent].[dbo].[SupplierStatus] tta ON i.Supplier = tta.SupplierCode
    WHERE
       CASE i.OnHold WHEN 'Y' THEN 1 ELSE 0 END <> tta.ActiveStatus

Open in new window


I have added the where clause as I only want this trigger to fire if the 'Onhold' status is changed. I have read that you can use NEW.Onhold <> OLD.Onhold, but this just errors as if it does not recognise the NEW and OLD calls, they don't appear in intellisense either

Thanks,
Dean
0
Comment
Question by:deanlee17
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40413839
You can use UPDATE(Onhold)
0
 

Author Comment

by:deanlee17
ID: 40414522
In the where clause? Can you give me a code sample please
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40415255
You can add it to the Where clause,

WHERE UPDATE(OnHold)
0
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 

Author Comment

by:deanlee17
ID: 40415382
Ok this isn't working:

USE [SysproCompanyZ]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[Ascent_UpdateSupplierStatus]
ON [dbo].[ApSupplier]
AFTER UPDATE
AS    

INSERT INTO [TriggerTablesAscent].[dbo].[SupplierStatus](SupplierCode, ActiveStatus, DateUpdated, UpdatedBy)
    SELECT
         i.Supplier,
           CASE i.OnHold WHEN 'Y' THEN 1 ELSE 0 END,
         CURRENT_TIMESTAMP, 
         SYSTEM_USER

    FROM
         inserted as i LEFT OUTER JOIN [TriggerTablesAscent].[dbo].[SupplierStatus] tta ON i.Supplier = tta.SupplierCode
    WHERE UPDATE(OnHold) 

Open in new window


It is still firing when any field is updated.

Thanks
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40415385
Try this:

USE [SysproCompanyZ]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[Ascent_UpdateSupplierStatus]
ON [dbo].[ApSupplier]
AFTER UPDATE
AS    

if UPDATE(OnHold)
INSERT INTO [TriggerTablesAscent].[dbo].[SupplierStatus](SupplierCode, ActiveStatus, DateUpdated, UpdatedBy)
    SELECT
         i.Supplier,
           CASE i.OnHold WHEN 'Y' THEN 1 ELSE 0 END,
         CURRENT_TIMESTAMP, 
         SYSTEM_USER

    FROM
         inserted as i LEFT OUTER JOIN [TriggerTablesAscent].[dbo].[SupplierStatus] tta ON i.Supplier = tta.SupplierCode

Open in new window

0
 

Author Comment

by:deanlee17
ID: 40415398
Same problem
0
 

Author Comment

by:deanlee17
ID: 40415431
I know what the problem is, when our internal system does an UPDATE it updates all the records, so even tho OnHold has not changed it has technically been updated. So its fire no matter which field it updated.

Any way around this?

BTW its only doing an insert in the trigger so the FROM clause ive changed to just inserted as i
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40415440
Then you need to compare with the deleted table

Add at the end:

LEFT OUTER JOIN deleted as d ON i.SupplierStatus = d.SupplierStatus

then you can use the WHERE clause

WHERE ISNULL(I.OnHold, 'NULL') <> ISNULL(D.OnHold, 'NULL')
0
 

Author Comment

by:deanlee17
ID: 40415482
Ok almost there with this now

USE [SysproCompanyZ]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[Ascent_UpdateSupplierStatus]
ON [dbo].[ApSupplier]
AFTER UPDATE
AS    


INSERT INTO [TriggerTablesAscent].[dbo].[SupplierStatus](SupplierCode, ActiveStatus, DateUpdated, UpdatedBy)
    SELECT
         i.Supplier,
           CASE i.OnHold WHEN 'Y' THEN 1 ELSE 0 END,
         CURRENT_TIMESTAMP, 
         SYSTEM_USER

    FROM
         inserted as i LEFT OUTER JOIN deleted as d ON i.OnHold = d.OnHold
    WHERE ISNULL(i.OnHold, 'NULL') <> ISNULL(d.OnHold, 'NULL') 

Open in new window


Oddly though the trigger fires if the user changes the status from OnHold = No to OnHold = Yes, but if its the opposite occurs then the trigger does not fire??
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40415486
Because your line 22 reads:

inserted as i LEFT OUTER JOIN deleted as d ON i.OnHold = d.OnHold

whereas it presumably should read

inserted as i LEFT OUTER JOIN deleted as d ON i.SupplierStatus = d.SupplierStatus

assuming SupplierStatus is the Primary Key (if it isn't, then it needs to be the primary key).
0
 

Author Comment

by:deanlee17
ID: 40415696
Perfect. Thanks Phillip
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question