RE: Dinamic statement in trigger

Dusan Kolesar 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
> 

Ok, I understand. Unfortunately the code above is no possible solution,
since it's not possible 
to specify OLD and NEW as tables in SQL statements inside trigges.
I think you have to compare each column explicitly, i.e. if you have a
table T(COL1, COL2, COL3, ...), the code 
would look like

IF OLD.COL1 <> NEW.COL1 THEN COLCHNG = COLCHNG + 1;
IF OLD.COL2 <> NEW.COL2 THEN COLCHNG = COLCHNG + 1;
IF OLD.COL3 <> NEW.COL3 THEN COLCHNG = COLCHNG + 1; 
... 

Of course, this is may be a boring job, if the table has many columns.
However, it seems to be the only possible solution.

Best Regards,
Thomas

-- 
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:    http://lists.mysql.com/[email protected]