Encounter a problem, is somehow delete data in a SQL database, can not find out the reason, on the Internet to find a trigger method record delete log, but not all, of the record data can't find who is to perform the delete operation, the database is a great god, please help solve, thank you,
The following is an example of an online:
- reference and record operation example

- create a record log table
The create table t_log (
Id int identity (1, 1) primary key,
Work stops nchar (30) not null default host_name (),
The operator sysname DEFAULT SUSER_SNAME (),
Operating time datetime default getdate (),
The event type nvarchar (30),
Parameter int,
Execution of the statement nvarchar (255))
Go

- the demonstration data table
Create table test (id int)
Go

- record operation trigger
Create the trigger t_update on test
For insert, update, delete
As
Insert t_log (event type, parameters, the execution of statements) exec (' DBCC inputbuffer (@ @ spid) ')
Go

- initialize the table
Insert into test values (1)
Go

Update the test set id=100
Go

- display record
Select * from t_log
Go

- delete the demo environment
Drop table t_log, test

CodePudding user response:

Do you have any good methods to monitor who to perform the delete operation

CodePudding user response:

Refer to https://blog.csdn.net/ap0405140/article/details/8896330

CodePudding user response:

reference 1st floor weixin_40555511 response:

do you have any good way to monitor who perform the delete operation to the

Tested your code no problem ah,
What do you mean by say the data is not complete? Operator column is empty?

But despite the code that's right, SUSER_SNAME (use of) still need to consider, if is winform program, using the Windows account direct database, you can record the correct operator, but if it is a web application, then the whole data chain, all need to join another operator information,