 | | | Dinamic statement in trigger | Dinamic statement in trigger 2007-01-15 - By Dusan Kolesar
Back
On Mon, 15 Jan 2007 14:53:14 +0100, Anhaus, Thomas <thomas.anhaus@(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/maxdb?unsub=mysql@(protected)
|
|
|