After insert, update trigger not running [solved]

Question

I have two triggers After Insert or Update and Instead of Insert. It appears that the after trigger is not running or sending the correct data.

I have verified the correct operation of Z_UpdateStageTable stored procedure and the Instead of Insert trigger. Removing the Instead of Insert trigger doesn't have any affect. The After Insert, Update trigger was working correctly at one time, I haven't made any changes to it. I have tried deleting it and adding it, but it still doesn't run or have the correct data.

Any Ideas?

Instead of Insert:

ALTER TRIGGER [DeleteExistingFilter]
   ON  [dbo].[Z_MobileSyncFilters]
INSTEAD OF INSERT
AS 
BEGIN
SET NOCOUNT ON;
DELETE FROM Z_MobileSyncFilters WHERE UserID = (SELECT UserID FROM INSERTED);
INSERT INTO Z_MobileSyncFilters
SELECT *
FROM INSERTED;
END

After Insert, Update:

TRIGGER [UpdateStageTable] 
   ON  [dbo].[Z_MobileSyncFilters]
   AFTER INSERT,UPDATE
AS 
BEGIN
SET NOCOUNT ON;
DECLARE @AllWos AS VARCHAR(5000);
DECLARE @PmWos AS VARCHAR(5000);
DECLARE @RepWos AS VARCHAR(5000);
SET @AllWos = (SELECT AllWos FROM INSERTED);
SET @RepWos = (SELECT AllWos FROM INSERTED);
SET @PmWos = (SELECT AllWos FROM INSERTED);
EXEC Z_UpdateStageTable @AllWos;
EXEC Z_UpdateStageTable @RepWos;
EXEC Z_UpdateStageTable @PmWos;
END

Answer 1

Hold on a second, if userid is your PK then Z_MobileSyncFilters will not have data yet, this is also an instead of trigger

this wholw block doesn't do anything really, why do you need this trigger?

DELETE FROM Z_MobileSyncFilters WHERE UserID = (SELECT UserID FROM INSERTED);
INSERT INTO Z_MobileSyncFilters
SELECT *
FROM INSERTED;

you second trigger is flawed because it will faile if you have a multi row operation

why do you have 2 insert trigger (1 instead 1 after) on this table?

Answer 2

You can add PRINT statements to the trigger and manually insert from ManagementStudio/Enterprise Manager to see where the trigger fails.

I see a problem when you insert multiple records in a single statement, as the SELECT FROM Inserted will return more than 1 record.

You can also update the SET statement to SELECT @Var = AllWos FROM Inserted

Answer 3

Is there a typo in the SET part of the AFTER trigger? You're selecting the same thing into three different variables.

Rather than confirming the behavior of Z_UpdateStageTable, I'd try to replace it with something dirt simple (a parameterless sql statement, say) to test whether the trigger's being called. It's possible that the sproc's not being called with what you think it's being called with.