How to force update a full-text index inside a transaction in mssql?

Question

I’m trying to figure out how to get the following code to return the line that he just inserted, ”the employee pointed out and suggested launching the ALTER FULLTEXT uiris_production REBUILD directory, but this cannot be done as part of a user transaction.

The reason this should be in a transaction is because it comes from a test environment where the test is wrapped in a transaction and then rolled back.

declare @search varchar(64) set @login_test = 'foobar' set @search = '"' + @login_test + '*"' begin transaction insert into users(login) values (@login_test) select login from users where contains(login, @search) commit 

Answer 1

First, make sure that a full text index is configured to automatically track changes. For this request, change_tracking_state must be "A".

 select t.name, fi.change_tracking_state from sys.fulltext_indexes fi inner join sys.tables t on fi.object_id = t.object_id where t.name = 'users' 

But even with automatic tracking of changes in updating full-text text, there is a certain degree of delay. You may need to create a WAITFOR in your unit test to take this into account.

Answer 2

 SELECT SCOPE_IDENTITY() 

will tell you the id of the record you just created.