asked on

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

Phillip Burton

You can use UPDATE(Onhold)

deanlee17

ASKER

In the where clause? Can you give me a code sample please

Phillip Burton

You can add it to the Where clause,

WHERE UPDATE(OnHold)

deanlee17

ASKER

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

Phillip Burton

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

deanlee17

ASKER

Same problem

deanlee17

ASKER

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

ASKER CERTIFIED SOLUTION

Phillip Burton

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

deanlee17

ASKER

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??

Phillip Burton

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).

deanlee17

ASKER

Perfect. Thanks Phillip