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] > >
Yes and no ! First yes, you can create and execute the dynamic declare cursor statement. But you'll not able to compile the fetch statement, because the cursor MYCURSOR must exist at compile time, which isn't true. Besides, I don't understand the sense of that trigger. How is it possible, that an update changes the column names of the firing table, i.e. how can OLD.<column name> and NEW.<column name> ever differ ? Best Regards, Thomas -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
