July 10, 2002 at 7:22 am
Is there something special about triggers on subscription tables?
I have a Transactional publication replication that pushes data to a table. On that table (on subscriber) I have a trigger that should fire on insert and update. It fires but it fires multiple times, regardless what conditions are set in the trigger.
I.e.:
CREATE TRIGGER [tr_FinalDateChnge] ON [dbo].[tblData]
FOR UPDATE
AS
SET NOCOUNT ON
DECLARE @SDG int
SELECT @SDG=i.SDG
FROM tblData td inner join inserted i on td.SDG=i.SDG
inner join Deleted d on d.SDG=i.SDG
where i.FinalDateMod != d.FinalDateMod
--SELECT @SDG=SDG FROM inserted
If @SDG IS NOT NULL
Exec zp_lkEmailEnum @SDG
and
CREATE TRIGGER [tr_FinalRepInsert] ON [dbo].[tblData]
FOR INSERT
AS
SET NOCOUNT ON
DECLARE @SDG int
DECLARE @FinalF varchar(20)
SELECT @SDG=i.SDG, @FinalF=i.FinalFile
FROM tblData d inner join inserted i on d.SDG=i.SDG
If @FinalF IS NOT NULL
Exec zp_lkEmailEnum @SDG
any ideas why it fires even when other fields in the table are updated?
Thanks
Jakub
July 10, 2002 at 7:43 am
Do you have nested triggers enabled? In the code you posted looks like you're assuming it will be one row per trigger, that's not always the case.
Andy
July 10, 2002 at 7:54 am
Probably not.
how can I tell if I have them enabled?
Thanks,
Jakub
July 10, 2002 at 8:06 am
I found out hot to enable nested triggers in BOL.
DUH 🙂
Should I have them enabled? I thought that triggers fire once per row, regardless how many rows are updated.
Jakub
July 10, 2002 at 10:43 am
Generally you don't want nested triggers enabled. Just wasnt sure if maybe that was why the triggers were firing multiple times as you mentioned. Triggers fire once per transaction - could be 1 row or a million. That means you should try to build set based solutions in your trigger. If you need to call the proc in your example for each row, you'll have to cursor through the inserted table.
Andy
July 10, 2002 at 11:27 am
I see,
Thanks for your help.
Jakub
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply