On Mon, 15 Jan 2007 14:53:14 +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
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
Hello
I want to find out in UPDATE trigger if value of some column was changed
or not.
I want to make some change list with information which column was changed.
So, maybe statement :
stmt = 'SELECT 1 FROM FROM ADMIN.DUAL WHERE OLD.' || colName || ' <> NEW.'
|| colName';
EXECUTE stmt;
IF $rc = 0 THEN
INSERT INTO CHNGTABLE ('column' || colName || 'was changed');
will work ?
But this gives me an error "Table must be in from list".
Is it only syntax problem, or it is not possible to access old and new
values in trigger dynamicly?
Regards, Dusan
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
