Problem with a trigger not firing properly.(create trigger)
ok i have a service running that looks at a table with a list of procedures with set run times. ie proc 1 4:00, proc2 4:50, proc3 5:00..... each proc updates or inserts record in to a monitor table which has a trigger that is fired on inserts/updates which in turn runs a proc to populate an audit table that has the keys from the inserted/updated table. However the audit table is not receiving any records when the procs run. I can see the new records in the monitor table, but no records are being inserted into the audit table. here is the strange part if I run exec proc1 it inserts a new record into the Monitor table and i see a audit record. but not when the service runs it. there are 15 proc and only the three that write to the monitor table are not getting audit records. This service is ran at 56 other locations with out an issue but not at this one. So i know the trigger is working because it runs when i exec proc.... I am not getting any errors. ???????????????????????????????????
Yes, I understand that. But my point is that you want to prove to yourself that the trigger is NOT firing when you run the proc from the service. Depending on how complex the logic is inside the trigger, it might very well be that the trigger is indeed firing, it's just not Writing anything into the audit table. So if you start a trace capturing individual statements and batches (including stored procedure calls) you can see if the trigger is firing or not when the proc is executed from the service. Alternatively, update your question to include the CREATE TRIGGER-statement(s) so someone can have a look and see if it under some circumstances is not Writing what you Think it should to the audit table.