In addition to the basics of table triggers, this article also uses a bit of knowledge.
1. How to use SQL to get the current session user name and machine name
Select current_user, HOST_NAME ()
2. How to get the current table name in a table trigger
SELECT Object_schema_name (parent_id) + '.' + object_name (parent_id) from sys.triggers
Trigger Complete code
ALTER TRIGGERDbo. Dimtest_adutittriger onDbo. Dimtest afterINSERT,DELETE,UPDATE asBEGIN SETNOCOUNT on;Declare@tbNamevarchar(256)SELECT@tbName = ' dbo. Dimtest 'DECLARE@Action as varchar(20), @Count int= 0;SET@Action=INSERT‘;--Set Action to Insert by default. IF EXISTS(SELECT* fromDELETED)BEGIN SET@Action= Case when EXISTS(SELECT* fromINSERTED) Then‘UPDATE‘--Set Action to Updated. ELSE‘DELETE‘--Set Action to Deleted. END END ELSE IF not EXISTS(SELECT* fromINSERTED)RETURN;-Nothing updated or inserted. IF( @Action=INSERT‘or@Action=UPDATE‘)BEGIN Set@Count= (Select Count(*) fromINSERTED)END IF@Action=DELETE‘BEGIN Set@Count= (Select Count(*) fromDELETED)END Insert intoDbo. AudittableSelectGETDATE (),Current_User, HOST_NAME (),NULL, @tbName, 'Dim‘,@Action,@CountENDGO
SQL Server-use table triggers to record table inserts, updates, rows deleted