Re: Dinamic statement in trigger

On Mon, 15 Jan 2007 08:27:22 +0100, Anhaus, Thomas <[email protected]>  
wrote:

> Dusan Kolesar wrote :
>>
>> Hello,
>> is it possible to access trigger variables in dinamic sql statement:
>>
>> CREATE TRIGGER EMPLOYEE_UPDATE FOR EMPLOYEE AFTER UPDATE EXECUTE
>> (
>>    VAR
>>      EVENTTABLEID FIXED(10);
>>      COLNAME VARCHAR(30);
>>      STMT VARCHAR(100);
>>      COLCHNG FIXED(38);
>>      I FIXED (38);
>>
>>    SET COLCHNG = 0;
>>    SET I = 1;
>>
>>    DECLARE COLNAME_CURSOR CURSOR FOR
>>      SELECT COLUMNNAME FROM DOMAIN.COLUMNS WHERE TABLENAME =
>> 'EMPLOYEE';
>>    WHILE ($RC <> 100) DO
>>    BEGIN
>>      FETCH COLNAME_CURSOR INTO :COLNAME;
>>      SET stmt = 'IF (OLD.' || colName || ' <> NEW.' ||
>> colName || ') THEN
>> SET colChng = ' || colChng|| ' + ' ||  i;
>>      EXECUTE stmt;
>>      SET I = I*2;
>>    END;
>>
>>    CALL ADMIN.GENERATE_EVENTS(1, :OLD.ID, 2, :COLCHNG);
>> )
>>
>> EXECUTE stmt; doesn't work
>> it should do next task:
>> IF (NEW.COLNAME <> OLD.COLNAME) THEN
>>    SET colChng = colChng + i;
>> for every column of table "EMPLOYEE" but returns an error.
>>
>> Can I obtain such result in trigger code ?
>>
>> Thank you for your support, Dusan.
>>
>>
>
> Sorry, but this is not possible.  You can't create statements of the PL
> language dynamically at runtime. This is possible for sql statements
> only.
>
> Best Regards,
> Thomas
>
>

Hello Thomas,
When dynamic statement is not available for PL/SQL
is it posible to do something like this in trigger:
stmt =
   'DECLARE MYCURSOR CURSOR FOR ' ||
     'SELECT CASE WHEN OLD.' || colName || ' <> NEW.' || colName || ' THEN  
0 ELSE 1 END ' ||
       'FROM ADMIN.DUAL';
EXECUTE stmt;
FETCH MYCURSOR INTO :result;

Or it is not aloved to use OLD.COLUMNNAME, NEW.COLUMNNAME in dynamic  
statement at all ?

Thank you for response, Dusan



-- 
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:    http://lists.mysql.com/[email protected]