After Insert trigger not running

[Seth Schrock]

I have a SQL Server database that has two tables with INSERT triggers. My front end is an MS Access database connecting via ODBC. The one trigger works fine, but the other one doesn't run at all. What can I do to troubleshoot this problem?

I forgot to mention, if I run an INSERT query from SSMS, then both triggers work.

[Rabbit]

What's the code for the insert trigger?

One thing you can do to track is to turn on SQL Trace mode on ODBC connections so you can see the exact SQL it is sending to the server.

Something else you can do is modify the trigger to insert into an audit table so you know which parts of the trigger is run.

[Seth Schrock]

I actually just figured it out. Here is what I had:

ALTER TRIGGER [Customer].[AddPersonServicesList] 
   ON  [Customer].[AuthorizedPeople]
   AFTER INSERT
AS 
 
DECLARE @PersonID int
DECLARE @CustID int
 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
 
    -- Insert statements for trigger here
 
    --Get inserted Customer ID
    SELECT @PersonID = AuthPersonID_pk, @CustID = CustomerID_fk FROM inserted
 
    --Add related records to Customer Services
    INSERT INTO Customer.PersonServices (AuthPersonID_fk, ServiceID_fk, Allowed)
    SELECT @PersonID, ServiceID_fk, 0 FROM Customer.CustomerServices
    WHERE CustID_fk = @CustID And Used = 1
 
END

The problem was when I was inserting the new record, CustomerID_fk was null and then I would use an UPDATE query to insert it later (long story). I have since fixed it so that I'm using a default value so that the customerID is there when the record is inserted and now the trigger runs perfectly.

Is there a way to see in SQL Profiler what values are being used in the variables? I'm able to see each statement of the trigger being ran, but the @CustID and @PersonID variables just show the names and not the values.

[Rabbit]

Sorry, I'm not sure about SQL profiler, I haven't used it before though I think our DBA's use it.