In order to render the SQL statement as a valid standalone SQL a number of conversions maybe applied to the SQL statement.
Note: If a conversion is applied, it may be necessary to reverse the changes after optimization when an alternative SQL is pasted back to the original source code.
During a trigger operation, two logical tables store deleted and inserted records. The deleted and inserted logical tables cannot be referenced outside the trigger body. Therefore to be able to optimize a SQL statement used in a trigger two temporary tables are used to simulate the inserted and deleted tables.
For example:
INSERT INTO EMP_SMALL (EMP_ID,
EMP_NAME,
EMP_SALARY)
SELECT A.EMP_ID,
A.EMP_NAME,
B.EMP_SALARY
FROM EMPLOYEE A,
Inserted B
WHERE A.EMP_ID = B.EMP_ID
SELECT *
INTO #inserted_simulation_table
FROM dbo.EMPLOYEE
WHERE 1 = 2
INSERT INTO EMP_SMALL (EMP_ID,
EMP_NAME,
EMP_SALARY)
SELECT A.EMP_ID,
A.EMP_NAME,
B.EMP_SALARY
FROM EMPLOYEE A,
#inserted_simulation_table B
WHERE A.EMP_ID = B.EMP_ID
For some source code, ? is used to define external parameters, therefore to enable unique referencing, the SQL Scanner adds a number so that each parameter has a unique name within the SQL statement.
For example:
SELECT EMP_ID
FROM EMPLOYEE
WHERE EMP_ID = ?
AND EMP_NAME = ?
SELECT EMP_ID
FROM EMPLOYEE
WHERE EMP_ID = ?1
AND EMP_NAME = ?2
© ALL RIGHTS RESERVED. Feedback Terms of Use Privacy Cookie Preference Center
