TRACE trigger triggers in SQL Server

Usually, the data of the database is the instructions sent by the program to be increased and censored. In the background for routine maintenance, including backup, index reconstruction work, of course, there is also a direct implementation of the UPDATE statement to repair the error data, if this behavior occurs on the customer, and the customer is a two knife, then there may be consequences for the situation. So, apart from the institutional clarity of the above-mentioned behavior, can there be some technical traceability?

We can get to the currently executing statement by using the following statement:

 declare   @Handle  varbinary  (64   select   @Handle  =   sql_handle  from   Master. sysprocesses  where  spid=  @ @SPID  select  *  from  sys.fn_get_sql ( @Handle  );  go  

That means knowing the connection spid, we can get to which statement the SPID is currently executing. This is a principle of naming the process from "Activity monitor".

Back to the topic, if you want to monitor data changes, we have to place our own monitoring in the trigger to record the time and location of the event, and the cause of the incident results.

So is the above sentence valid in the trigger, the answer is no.

Here's a big trick:

CREATE TABLE #inputbuffer (      nvarchar       ),smallint,         nvarchar(4000)    )insert into #inputbuffer  EXEC('DBCC INPUTBUFFER (@ @SPID)')

The key is to get a statement that triggers the current SPID, returning three fields. If triggered by a stored procedure, returns the stored procedure name

After taking the original statement, we can determine whether the data changes are triggered by that statement, and provide a basis for us to further judge the culprit.

Currently, this behavior is hindsight, and will lose some of the database performance.

If we want to effectively prevent this situation from the root, we must divide the rights and responsibilities from the system.

TRACE trigger triggers in SQL Server