Solved

Fire trigger when a certain column changes

Posted on 2014-10-30
11
188 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

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…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

821 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