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]
