Avoid endless SQL Server triggers

When using SQL Server, you sometimes want to execute a trigger code for modifying a field in the data table. For example, if you modify a person name, we hope that the database will automatically execute a code to modify the pinyin initial field in the same data table. If you use the after update trigger, the SQL server will crash. Is there any way to avoid this endless loop? The answer is yes. In the trigger, I can determine whether the user has modified the name field. If yes, I will execute subsequent code updates. If the user modifies other fields, no code will be executed.

Assume that a table stores customer information. The first field is visitor_id, the second field is visitor_name, and the third field is visitor_py_code. We write two triggers for new and update actions.

It is easy to write new triggers. The problem is that the update trigger will be executed cyclically if the update statement is used again, and the system will crash. We want to execute the trigger only for updating a single field. Fortunately, SQL provides the columns_updated () function to determine which field is updated. Therefore, the updated trigger is written as follows:

Create trigger [updatepycodeonly] on [DBO]. [visitor]
For update
As
If (columns_updated () & 2) = 2 begin
Declare
@ Visitorid int,
@ Namestr nvarchar (50)
Select @ visitorid = visitor_id, @ namestr = visitor_name from inserted
Update visitor set namepinyin = DBO. getpycode (@ namestr) Where visitor_id = @ visitorid
End

Note the following when using the columns_updated () function::

1. The number of the first field in the table is 0, and the number of the second field is 1.
2. Do not change the field sequence in the table.