question

ljhopkins avatar image
ljhopkins asked

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. ???????????????????????????????????
triggertable
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Have you checked if the trigger is indeed not firing? It could be that the trigger is firing but not doing what you expect it to do. If you start a trace capturing individual statements on your server and have the service run the proc, you'll see if the trigger is executed or not, and which other statements run. Prove to yourself that the trigger is indeed not firing, then you can start troubleshooting why. And if it IS firing, you can start looking into why it's not auditing as expected.
0 Likes 0 ·
I know the trigger is firing because if i EXEC proc1 it writes to the monitor table and the monitor trigger writes a record to the audit table. but when the windows service exec proc1 it writes to the monitor table but the trigger does not write to the audit table
0 Likes 0 ·
it is so strange that the proc/service and triggers are on 50+ other servers/locations and is working correctly.
0 Likes 0 ·

1 Answer

· Write an Answer
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.