Fire trigger when a certain column changes

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
deanlee17Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
You can use UPDATE(Onhold)
0
deanlee17Author Commented:
In the where clause? Can you give me a code sample please
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
You can add it to the Where clause,

WHERE UPDATE(OnHold)
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

deanlee17Author Commented:
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
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
deanlee17Author Commented:
Same problem
0
deanlee17Author Commented:
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
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
deanlee17Author Commented:
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
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
deanlee17Author Commented:
Perfect. Thanks Phillip
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.