SQL Server database development trigger and charindex system function usage

Trigger:
A trigger is a database table trigger. It is a piece of code executed when you insert, modify, or delete a table. It is used as a constraint for this table.
Instance description:
In a problem report case, when a problem event is reported to the database table, that is, when the data is inserted into the database table and the status of the problem is determined to be 0 (Review, add an index for this issue event to another table for query.
The Code is as follows:

Alter trigger [DBO]. [insertproblem] on [DBO]. [problem] After insert as declare @ in_taskid varchar (13); declare @ in_areaid varchar (10); declare @ temp_coordinate varchar (10); declare @ temp_location nvarchar (30 ); declare @ temp_status varchar (10); declare @ temp_count int; select @ temp_status = status from inserted; -- set @ in_areaid = '2016'; Set @ temp_count = 0; if (@ temp_status = '0') begin -- Obtain areaid zip code select @ temp_coordinate = coordinate from inserted according to coordinate calculation; select @ temp_location = location from inserted; /** begindeclare @ area nvarchar (30); declare mycusor cursor for select area from area; open mycusor fetch next from mycusor into @ area while (@ fetch_status = 0) begin try -- select @ temp_count = count (*) from area where (charindex ('huairou ', @ temp_location) <> 0); Set @ temp_count = charindex ('huairou ', @ temp_location); If (@ temp_count <> 0) beginselect @ in_areaid = ID from area where area = @ area; end fetch next from mycusor into @ area end trybegin catchprint @ error; end catchclose mycusor deallocate mycusor end */select * from area; beginif @ temp_count = 0 select @ temp_count = count (*) from area where (charindex ('tongzhou ', @ temp_location) <> 0); If @ temp_count <> 0 select @ in_areaid = ID from area where area = 'tongzhou '; If @ temp_count = 0 select @ temp_count = count (*) from area where (charindex ('pinggu ', @ temp_location) <> 0); If @ temp_count <> 0 select @ in_areaid = ID from area where area = 'pinggu region '; if @ temp_count = 0 select @ temp_count = count (*) from area where (charindex ('shunyi ', @ temp_location) <> 0 ); if @ temp_count <> 0 select @ in_areaid = ID from area where area = 'shunyi region'; If @ temp_count = 0 select @ temp_count = count (*) from area where (charindex ('huairou ', @ temp_location) <> 0); If @ temp_count <> 0 select @ in_areaid = ID from area where area = 'huairou region '; if @ temp_count = 0 select @ temp_count = count (*) from area where (charindex ('miyun Yun ', @ temp_location) <> 0 ); if @ temp_count <> 0 select @ in_areaid = ID from area where area = 'miyun County '; If @ temp_count = 0 select @ temp_count = count (*) from area where (charindex ('yanqing ', @ temp_location) <> 0); If @ temp_count <> 0 select @ in_areaid = ID from area where area = 'yanqing County '; if @ temp_count = 0 select @ temp_count = count (*) from area where (charindex ('changping ', @ temp_location) <> 0 ); if @ temp_count <> 0 select @ in_areaid = ID from area where area = 'changping '; If @ temp_count = 0 select @ temp_count = count (*) from area where (charindex ('mentougou ', @ temp_location) <> 0); If @ temp_count <> 0 select @ in_areaid = ID from area where area = 'mentougou region '; if @ temp_count = 0 select @ temp_count = count (*) from area where (charindex ('fangshan ', @ temp_location) <> 0 ); if @ temp_count <> 0 select @ in_areaid = ID from area where area = 'fangshan district '; If @ temp_count = 0 select @ temp_count = count (*) from area where (charindex ('daxing ', @ temp_location) <> 0); If @ temp_count <> 0 select @ in_areaid = ID from area where area = 'daxing region '; if @ temp_count = 0 set @ in_areaid = '2013'; end -- Obtain the task id select @ in_taskid = PID from inserted; insert into task values (@ in_taskid, @ in_areaid, '1', null); End

Code Description:
1. Alter trigger [DBO]. [insertproblem] on [DBO]. [problem]
After insert
As begin end
The above is the trigger definition. Triggered after the insert operation.
2. Select @ temp_coordinate = coordinate from inserted;
Query the data in the currently inserted statement.
3. charindex ('tongzhou ', @ temp_location)
System Function: query the location of 'tongzhou 'in @ temp_location. If no value exists, 0 is returned. The returned value is int type.

---------------------------------------------------------
Usage of charindex:
Charindex (expression1, expression2 [, start_location])
If either expression1 or expression2 is of the Unicode data type (nvarchar or nchar), the other is converted to the Unicode data type. Charindex cannot be used with the Data Types of text, ntext, and image.
If either expression1 or expression2 is null and the database compatibility level is 70 or higher, charindex returns NULL. If the database compatibility level is 65 or lower, charindex returns NULL only when both expression1 and expression2 are null.
If expression1 cannot be found in expression2, charindex returns 0.

Problems encountered during actual use:
The cursor is used in the above trigger, but in the above usage, when charindex (@ temp1, @ temp2) is used, both parameters are variable (trigger or stored procedure) in the program) it will not be executed. If you do not know the cause of this problem, you can directly jump to another location and do not execute it,
However, this method can be used for separate execution in SQL. I don't know if it is a bug. So all of the above are written out, and loop traversal or cursors are not used.
Therefore, when the charindex function is used in the program, it is best to use a constant like '2016' for @ temp and a variable for @ temp2.