CODEWITHSUNDEEP
sqlt-sqlsql-server-2012
DotnetSparrow
DotnetSparrow

Reputation: 27996

SQL Trigger not firing on update

I have following trigger which should insert records in a junction table when an entry is updated in main table. but this trigger is not firing ( It was firing earlier)

CREATE TRIGGER [dbo].[trUpdateRegistrant]
ON [dbo].[hr_Registrants]
FOR UPDATE  
AS

DECLARE @RegistrantID INT, @CategoriesIDs VARCHAR(1000), @DesiredCategoriesIDs VARCHAR(1000), @LanguagesIDs VARCHAR(1000), 
    @CategoryIDsChanged BIT, @DesiredCategoriesIDsChanged BIT, @LanguagesIDsChanged BIT

DECLARE curRegs CURSOR FOR
SELECT I.RegistrantID, 
    I.CategoriesIDs, 
    I.DesiredCategoriesIDs, 
    I.LanguagesIDs,
    CASE WHEN D.CategoriesIDs <> I.CategoriesIDs THEN 1 ELSE 0 END,
    CASE WHEN D.DesiredCategoriesIDs <> I.DesiredCategoriesIDs THEN 1 ELSE 0 END,
    CASE WHEN D.LanguagesIDs <> I.LanguagesIDs THEN 1 ELSE 0 END
FROM INSERTED I JOIN DELETED D ON I.RegistrantID = D.RegistrantID 
WHERE D.CategoriesIDs <> I.CategoriesIDs 
    OR D.DesiredCategoriesIDs <> I.DesiredCategoriesIDs 
    OR D.LanguagesIDs <> I.LanguagesIDs

OPEN curRegs;
FETCH NEXT FROM curRegs INTO @RegistrantID, @CategoriesIDs, @DesiredCategoriesIDs, @LanguagesIDs, 
    @CategoryIDsChanged, @DesiredCategoriesIDsChanged, @LanguagesIDsChanged;

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @CategoriesIDs IS NOT NULL AND @CategoryIDsChanged = 1
    BEGIN
        -- delete existing rows at first
        DELETE dbo.hr_RegistrantCategories WHERE RegistrantID = @RegistrantID

        INSERT INTO dbo.hr_RegistrantCategories (RegistrantID, CategoryID)
        SELECT @RegistrantID, CAST(T.Data AS INT) 
        FROM dbo.hr_Split(@CategoriesIDs, '|') T
    END

    IF @DesiredCategoriesIDs IS NOT NULL AND @DesiredCategoriesIDsChanged = 1
    BEGIN
        -- delete existing rows at first
        DELETE dbo.hr_RegistrantDesiredCategories WHERE RegistrantID = @RegistrantID

        INSERT INTO dbo.hr_RegistrantDesiredCategories (RegistrantID, CategoryID)
        SELECT @RegistrantID, CAST(T.Data AS INT) 
        FROM dbo.hr_Split(@DesiredCategoriesIDs, '|') T
    END

    IF @LanguagesIDs IS NOT NULL AND @LanguagesIDsChanged = 1
    BEGIN
        -- delete existing rows at first
        DELETE dbo.hr_RegistrantLanguages WHERE RegistrantID = @RegistrantID

        INSERT INTO dbo.hr_RegistrantLanguages (RegistrantID, Language)
        SELECT @RegistrantID, CAST(T.Data AS INT) 
        FROM dbo.hr_Split(@LanguagesIDs, '|') T
    END

    FETCH NEXT FROM curRegs INTO @RegistrantID, @CategoriesIDs, @DesiredCategoriesIDs, @LanguagesIDs, 
        @CategoryIDsChanged, @DesiredCategoriesIDsChanged, @LanguagesIDsChanged;
END

CLOSE curRegs;
DEALLOCATE curRegs;

Please suggest how to fix it ?

Upvotes: 0

Views: 15448

Answers (2)

Joe Perkins
Joe Perkins

Reputation: 170

Triggers are fired only once per transaction, a mass update, delete or insert is executed in a single transaction.

You may need to use a curser or a while to guarantee the trigger is fired for each row.

Is there a way to loop through a table variable in TSQL without using a cursor?

Upvotes: 3

Devart
Devart

Reputation: 121912

Try to use AFTER UPDATE:

CREATE TRIGGER [dbo].[trg_test]

ON [dbo].[test]
AFTER UPDATE 

AS BEGIN


END

Or check is the trigger active:

SELECT t.name, t.is_disabled 
FROM sys.triggers t
WHERE t.name = 'trUpdateRegistrant'

Upvotes: 2

Related Questions