Solved

Fire trigger when a certain column changes

Posted on 2014-10-30
11
187 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
  • 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

770 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