Which StoredProc triggered a TRIGGER ?

[Windy77]

Background to problem:

We have a problem in that for the last two weeks at the start of business on Thursday morning a single row from a Customer Master table has been missing. The first time we were confused but thought it was accidental, the second time it became more obvious that it may be a deliberate malicious act (recently departed troublesome "colleague") which in reality is only irritating but we need to stop it.

I have searched sysobjects/syscomments for all databases & linked servers but can't find any direct code in any StoredProcs (it may be hidden using a parameter ?). So I have set up an "INSTEAD OF DELETE" trigger to stop it happening / identify when it was attempted, but this won't give me the name of the Stored Procedure so I can correct or remove it.

Problem:

Is there any way of identifying (within the TRIGGER so I can add it to a log table or notifying email) which Stored Procedure has attempted the DELETE function that triggered the TRIGGER ?

It will run again sometime between 17:00 (GMT) UK time and 08:30 tomorrow morning, so although I have stopped it causing damage it would be great to completely resolve the issue which means I have to complete the coding by 17:00 UK today.

Many thanks for all attempts to help.

Paul

 

[dbomrrsm]

can you build something into the trigger code to capture the results of

select * from sysprocesses

and put them in a table - I am not 100% sure this will help but I think the name of the SP - if still running will be caught in the cmd column of the above select results.

hope this helps.

[blue]DBomrrsm[/blue]

 

[donutman]

You can check the SPs that show up under Display Dependencies or if that's not reliable enough, then script all the SPs and do a search for the table name.
Sounds like you may have a nightmare scenario on your hands...let us know the outcome. It could be very illuminating.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[SQLSister]

Why not set up Profiler to run a trace on all the activity tonight? It can tell you exactly which sps were run at exactly what time. IN your instead of trigger, copy the date and time the record was changed to an audit table. Compare with the Profiler trace to see what ran at that time.

Questions about posting. See faq183-874

 

[vongrunt]

Also: log @@SPID in INSTEAD OF trigger. Later it may greatly narrow result set given by profiler.