The EXEC (EXECUTE) function accesses the internal temporary trigger table of INSERTED or DELETED.

At the beginning, this table had very few fields (less than 10). The former developer wrote all the stored procedures, triggers, and table functions of this table to death. Each time you add some fields, you must manually modify these stored procedures, triggers, and table functions. At present, this table has more than 300 fields and may grow. Therefore, the Insus. NET business is to rewrite all static data to dynamic processing.

However, there was a problem that had been stuck until yesterday. Yesterday was Sunday, but the problem was not solved, so it was not enough to take a rest this day.

The problem is to rewrite the table trigger, involving the EXEC (EXECUTE) function to access the internal temporary trigger table of INSERTED or DELETED, such:
Copy codeThe Code is as follows:
EXECUTE ('select' + @ N + '= ISNULL ([' + @ I + '], 0) FROM inserted ')


When you try to execute the preceding SQL statement, an exception prompt is displayed: invalid object name 'inserted '. We cannot display the internal temporary trigger publishing for access to INSERTED or DELETED.

Because the INSERTED or DELETED tables dynamically reside in the memory, rather than in the database, they are not explicit. The trigger is executed in the execution plan that causes the trigger to be triggered. When we use EXEC (EXECUTE) or sp_executesql to EXECUTE a dynamically generated SQL statement, it is another separate execution plan. Resources between the two cannot be accessed from each other, and the memory is also released after the execution of the plan is completed.

However, the problem still needs to be solved. What Insus. NET thinks of is temporary tables. You can convert the data in the INSERTED or DELETED table to the temporary table first. In this way, we can process the data in the temporary table.

Copy codeThe Code is as follows:
SELECT * INTO # inserted FROM INSERTED
SELeCT * INTO # deleted FROM DELETED



Implementation Code:

Copy codeThe Code is as follows:
DECLARE @ f nvarchar (MAX) = CONVERT (NVARCHAR (MAX), @ I) -- @ I dynamic field
EXECUTE ('select ['+ @ F +'] FROM # deleted)