By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,566 Members | 1,870 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,566 IT Pros & Developers. It's quick & easy.

After Insert trigger not running

Seth Schrock
Expert 2.5K+
P: 2,951
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.
Feb 5 '16 #1
Share this Question
Share on Google+
3 Replies


Rabbit
Expert Mod 10K+
P: 12,430
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.
Feb 5 '16 #2

Seth Schrock
Expert 2.5K+
P: 2,951
I actually just figured it out. Here is what I had:
Expand|Select|Wrap|Line Numbers
  1. ALTER TRIGGER [Customer].[AddPersonServicesList] 
  2.    ON  [Customer].[AuthorizedPeople]
  3.    AFTER INSERT
  4. AS 
  5.  
  6. DECLARE @PersonID int
  7. DECLARE @CustID int
  8.  
  9. BEGIN
  10.     -- SET NOCOUNT ON added to prevent extra result sets from
  11.     -- interfering with SELECT statements.
  12.     SET NOCOUNT ON;
  13.  
  14.     -- Insert statements for trigger here
  15.  
  16.     --Get inserted Customer ID
  17.     SELECT @PersonID = AuthPersonID_pk, @CustID = CustomerID_fk FROM inserted
  18.  
  19.     --Add related records to Customer Services
  20.     INSERT INTO Customer.PersonServices (AuthPersonID_fk, ServiceID_fk, Allowed)
  21.     SELECT @PersonID, ServiceID_fk, 0 FROM Customer.CustomerServices
  22.     WHERE CustID_fk = @CustID And Used = 1
  23.  
  24. 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.
Feb 5 '16 #3

Rabbit
Expert Mod 10K+
P: 12,430
Sorry, I'm not sure about SQL profiler, I haven't used it before though I think our DBA's use it.
Feb 6 '16 #4

Post your reply

Sign in to post your reply or Sign up for a free account.