SQL Optimizer for SAP ASE 3.8 - Installation Guide

Trigger Conversion

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:

Original SQL statement

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 

After conversion

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 

Related Topic

SQL Conversion Overview