Trigger to record operation table logs

I have been pondering this function for a long time. I didn't know much about sqlserver. The key difficulty is to replace the field name with variables and then obtain the values stored in the table, then assign another variable. I did this because the name of this field is used in the next few places, so that it is easy to modify.

I have been pondering this function for a long time. I didn't know much about sqlserver. The key difficulty is to replace the field name with variables and then obtain the values stored in the table, then assign another variable. I did this because the name of this field is used in the next few places, so that it is easy to modify.

I have been pondering this function for a long time. I didn't know much about sqlserver. The key difficulty is to replace the field name with variables and then obtain the values stored in the table, then assign another variable. The reason why I do this is that the name of this field is used in the next few places, so that it is easy to modify.ImplementationIt took a long time, and there was little information on the Internet. After all, we found a solution. I hope you will not encounter the same problem in the future.ImplementationShare with you

CREATE trigger [dbo]. [trg_new_course]

On [dbo]. [course]

For insert, delete, update

As

Begin

Declare @ tabname varchar (50 ),

@ Pkname varchar (20 ),

@ Pkvalue varchar (20 ),

@ Opttype int,

@ Optip varchar (20 ),

@ Optsql varchar (200 ),

@ Xmlstr nvarchar (500 );

Declare @ optinfo nvarchar (500), @ id_ I int, @ id_d int;

Declare @ min_id int, -- Minimum field number

@ Total int ,--RecordTotal

@ Row_count int, -- cyclic variable

@ Temp_name varchar (100), -- temporary field name

@ Temp_pre_name varchar (100), -- a variable with a prefix of the Field Type

@ Temp_type varchar (100), -- temporary field type

@ Temp_value varchar (100), -- temporary field value

@ Xmlnode_value varchar (100), -- xml node Value

@ SQL _name varchar (100), -- SQLOperationRelated Fields

@ SQL _value varchar (100), -- SQLOperationRelated Field Values

@ SQL nvarchar (200), -- store dynamic

SQL @ pk_pre_name varchar (20) -- key field names with type prefixes

Set @ SQL _name = '';

Set @ SQL _value = '';

Set @ row_count = 1;

Set @ pkname = 'id'; -- Keyword name

Set @ tabname = 'Course ';--OperationTable Name

Set @ optinfo = '';

Select @ id_ I = id from inserted; select @ id_d = id from deleted;

Select @ temp_type = data_type from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @ tabname and column_name = @ pkname;

If (@ temp_type = 'int ')

Begin

Set @ pk_pre_name = 'I' + @ pkname

End

Else if (@ temp_type = 'float ')

Begin

Set @ pk_pre_name = 'F' + @ pkname

End

Else if (@ temp_type = 'decimal ')

Begin

Set @ pk_pre_name = 'D' + @ pkname

End

Else if (@ temp_type = 'datetime ')

Begin

Set @ pk_pre_name = 'da' + @ pkname

End

Else

Begin

Set @ pk_pre_name = 'C' + @ pkname

End

If @ id_ I is null and @ id_d is not null -- deleteOperation

Begin

Set @ pkvalue = @ id_d;

Set @ opttype = 1;

-- If the variable type is not string type

Set @ pkvalue = convert (varchar (200), @ pkvalue );

-- Generate and execute DeletionOperationSQL statement

Set @ optsql = 'delete from' + @ tabname + 'where' + @ pkname + '=' + @ pkvalue;

-- Generate and deleteOperationXml Representation of field information

Set @ optinfo = @ optinfo + '<' + @ pkname + '> ';

Set @ optinfo = @ optinfo + @ pkvalue;

Set @ optinfo = @ optinfo +' ';

End

Else

Begin

Set @ pkvalue = @ id_ I;

Select * into temps from inserted; -- the logical table inerted cannot be found in dynamic SQL statements.

Select @ min_id = max (ordinal_position) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @ tabname;

Select @ total = count (1) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @ tabname;

While (@ row_count <= @ total)

Begin

Select @ temp_name = column_name, @ temp_type = data_type from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @ tabname and ordinal_position = @ min_id;

If (@ temp_type = 'int ')

Begin

Declare @ temp_in int;

SET @ SQL = 'select @ temp_in = '+ @ temp_name +' from temps ;';

EXEC SP_EXECUTESQL @ SQL, n' @ temp_in int output', @ temp_in OUTPUT;

Set @ xmlnode_value = convert (varchar (100), @ temp_in );

Set @ temp_value = @ xmlnode_value;

Set @ temp_pre_name = 'I' + @ temp_name;

End

Else if (@ temp_type = 'float ')

Begin

Declare @ temp_inf float;

SET @ SQL = 'select @ temp_inf = '+ @ temp_name +' from temps ;';

EXEC SP_EXECUTESQL @ SQL, n' @ temp_inf float output', @ temp_inf OUTPUT;

Set @ xmlnode_value = convert (varchar (100), @ temp_inf );

Set @ temp_value = @ xmlnode_value;

Set @ temp_pre_name = 'F' + @ temp_name;

End

Else if (@ temp_type = 'decimal ')

Begin

Declare @ temp_ind float;

SET @ SQL = 'select @ temp_ind = '+ @ temp_name +' from temps ;';

EXEC SP_EXECUTESQL @ SQL, n' @ temp_ind decimal (18, 0) output', @ temp_ind OUTPUT;

Set @ xmlnode_value = convert (varchar (100), @ temp_ind );

Set @ temp_value = @ xmlnode_value;

Set @ temp_pre_name = 'D' + @ temp_name;

End

Else

Begin

Declare @ temp_inc varchar (200 );

SET @ SQL = 'select @ temp_inc = '+ @ temp_name +' from temps ;';

EXEC SP_EXECUTESQL @ SQL, n' @ temp_inc varchar (200) output', @ temp_inc OUTPUT;

Set @ xmlnode_value = convert (varchar (100), @ temp_inc );

Set @ temp_value = ''' + @ xmlnode_value + '''';

Set @ temp_pre_name = 'C' + @ temp_name;

End

-- Generate insert/modifyOperationXml Representation of relevant data information

Set @ optinfo = @ optinfo + '<' + @ temp_pre_name + '> ';

Set @ optinfo = @ optinfo + @ xmlnode_value;

Set @ optinfo = @ optinfo +' ';

If @ id_ I is not null and @ id_d is null -- insertOperation

Begin

-- Generate insertOperationExecuted SQL statement

If (@ temp_name <> @ pkname)

Begin

Set @ SQL _name = @ SQL _name + ',' + @ temp_name;

Set @ SQL _value = @ SQL _value + ',' + @ temp_value;

End

End

Else if @ id_ I is not null and @ id_d is not null -- UpdateOperation

Begin

-- Generate modificationsOperationExecuted SQL statement

If (@ temp_name <> @ pkname)

Begin

Set @ SQL _name = @ SQL _name + ',' + @ temp_name + '=' + @ temp_value;

End

End

Select @ min_id = ordinal_position from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'Course' and ordinal_position <@ min_id;

Set @ row_count = @ row_count + 1;

End

If @ id_ I is not null and @ id_d is null -- insertOperation

Begin

-- Generate and execute insertOperationSQL statement

Set @ opttype = 0;

Set @ optsql = 'insert' + @ tabname + '(' + substring (@ SQL _name, 2, len (@ SQL _name) + ') '+ 'values (' + substring (@ SQL _value, 2, len (@ SQL _value) + ')';

End

Else if @ id_ I is not null and @ id_d is not null -- UpdateOperation

Begin

-- Generate and execute modificationsOperationSQL statement

Set @ opttype = 3;

Set @ optsql = 'update' + @ tabname + 'set' + substring (@ SQL _name, 2, len (@ SQL _name )) + 'where' + @ pkname + '=' + @ pkvalue;

End

Drop table temps;

End

Set @ xmlstr =' ';

Set @ xmlstr = @ xmlstr +' '; Set @ xmlstr = @ xmlstr +' '+ Convert (varchar (3), @ opttype) +' ';

Set @ xmlstr = @ xmlstr +' '+ @ Tabname +' ';

Set @ xmlstr = @ xmlstr +' '+ @ Pk_pre_name +' ';

Set @ xmlstr = @ xmlstr +''; Set @ xmlstr = @ xmlstr +' ';

Set @ xmlstr = @ xmlstr + @ optinfo;

Set @ xmlstr = @ xmlstr +'';

Set @ xmlstr = @ xmlstr +'';

Select @ optip = client_net_address from sys. dm_exec_connections where Session_id =@@ spid;

If (@ pkvalue is null)

Begin

Set @ pkvalue =-1;

End

Insert into optlog values (@ tabname, @ pkname, @ pkvalue, @ opttype, @ optip, getdate (), @ optsql, @ xmlstr );

Print'OperationExecuted successfully ';

End

Which of the following are marked in red?ImplementationThe difficulty is to use the SQL server system storage process sp_executesql. The specific usage can be found on the Internet. This is just my statement. Maybe you still have a good idea.ImplementationThank you for your comments! I know that many of you may have a poor idea, but I hope it will help you.RecordMy technological growth history.