Dinamic statement in trigger 2007-01-15 - By Anhaus, Thomas
Back
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/maxdb?unsub=mysql@(protected)
|
|
