 | | | Dinamic statement in trigger | Dinamic statement in trigger 2007-01-15 - By Anhaus, Thomas
Back
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.
>
> --
> Dusan Kolesar
> Helsinska 19
> 040 13 Kosice
> Slovakia
> e-mail primary : d.kolesar@(protected)
> e-mail alternative : d.kolesar@(protected)
> ICQ# : 160507424
>
>
> --
> MaxDB Discussion Mailing List
> For list archives: http://lists.mysql.com/maxdb
> To unsubscribe:
> http://lists.mysql.com/maxdb?unsub=thomas.anhaus@(protected)
>
>
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
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/maxdb?unsub=mysql@(protected)
|
|
|