tsql - SQL Trigger not firing on update -
i have following trigger should insert records in junction table when entry updated in main table. trigger not firing ( firing earlier)
create trigger [dbo].[trupdateregistrant] on [dbo].[hr_registrants] update declare @registrantid int, @categoriesids varchar(1000), @desiredcategoriesids varchar(1000), @languagesids varchar(1000), @categoryidschanged bit, @desiredcategoriesidschanged bit, @languagesidschanged bit declare curregs cursor select i.registrantid, i.categoriesids, i.desiredcategoriesids, i.languagesids, case when d.categoriesids <> i.categoriesids 1 else 0 end, case when d.desiredcategoriesids <> i.desiredcategoriesids 1 else 0 end, case when d.languagesids <> i.languagesids 1 else 0 end inserted join deleted d on i.registrantid = d.registrantid d.categoriesids <> i.categoriesids or d.desiredcategoriesids <> i.desiredcategoriesids or d.languagesids <> i.languagesids open curregs; fetch next curregs @registrantid, @categoriesids, @desiredcategoriesids, @languagesids, @categoryidschanged, @desiredcategoriesidschanged, @languagesidschanged; while @@fetch_status = 0 begin if @categoriesids not null , @categoryidschanged = 1 begin -- delete existing rows @ first delete dbo.hr_registrantcategories registrantid = @registrantid insert dbo.hr_registrantcategories (registrantid, categoryid) select @registrantid, cast(t.data int) dbo.hr_split(@categoriesids, '|') t end if @desiredcategoriesids not null , @desiredcategoriesidschanged = 1 begin -- delete existing rows @ first delete dbo.hr_registrantdesiredcategories registrantid = @registrantid insert dbo.hr_registrantdesiredcategories (registrantid, categoryid) select @registrantid, cast(t.data int) dbo.hr_split(@desiredcategoriesids, '|') t end if @languagesids not null , @languagesidschanged = 1 begin -- delete existing rows @ first delete dbo.hr_registrantlanguages registrantid = @registrantid insert dbo.hr_registrantlanguages (registrantid, language) select @registrantid, cast(t.data int) dbo.hr_split(@languagesids, '|') t end fetch next curregs @registrantid, @categoriesids, @desiredcategoriesids, @languagesids, @categoryidschanged, @desiredcategoriesidschanged, @languagesidschanged; end close curregs; deallocate curregs;
please suggest how fix ?
try use after update:
create trigger [dbo].[trg_test] on [dbo].[test] after update begin end
or check trigger active:
select t.name, t.is_disabled sys.triggers t t.name = 'trupdateregistrant'
Comments
Post a Comment