| > In their production they are using "old" and "new" developed
> applications, the old ones uses the CHAR(6) date column and the new
> ones uses the VMS DATE column.
> This causes some problems because he would like to keep theses both
> date fields updated, if an "old" application updates the CHAR(6) the
> VMS DATE field should also be updated and if an "new" application
> updates the VMS DATE field the CHAR(6) field be updated.
I can't think of anyway to do this that does not involve recursive
triggers (which are not allowed).
Perhaps they could use a trigger to update the new field when the old
is updated, so that old applications need not change. New applications
would have to update both. This could be done in a stored procedure so
that it need be written more than once.
Peter
|
| .1
Yes Ian, I have read "all" notes about DATE conversion using CAST and
other methods and gave the customer some solutions but he didn't like
to have this code in multiple places (triggers) we thought of stored
procedures but these could not be called from an trigger.
.2
Yes Peter, it seems very difficult or even impossible to do this by
using triggers, they will all get recursive.
Here's one try that did not work:
create table linus (levnum CHAR(7), DATUM char(6), DATUM_DATE date
VMS);
create trigger LIN_DAT_UPD after update of datum on linus
referencing old as oldrec when (datum_date <> oldrec.datum_date)
(update linus x set datum_date = cast('19' || datum as date vms)
where linus.dbkey=x.dbkey)
for each row;
create trigger LIN_DATD_UPD after update of datum_date on linus
referencing old as oldrec when (datum <> oldrec.datum)
(update linus x set datum_date = datum_date_to_datum6(datum_date)
where linus.dbkey=x.dbkey)
for each row;
insert into linus (levnum) values ('1'); !!!! just to initiate levnum
Here's the test:
update linus set datum='950505' where levnum ='1';
And the result:
%RDB-E-TRIG_REQ_ERROR, error encountered by a request using triggers
-RDMS-F-UPDRECURSTRIG, update would cause recursive execution of
trigger LIN_DATD_UPD
RDB_60 4276 deals with the (unwanted recursive trigger error, Ian Smith
has answered that they will think of another way to evaluate the
possible recursive trigger actions, from detection at compiletime to
run-time. This would make it possible to do this kind of operation.
An extract from the note:
Note 4276.3
>You are welcome to enter a suggestion BUG report which suggests we
>detect the recursive invocation at run-time rather than trying to
>determine it at compiletime. I think that would be doable for a future
>version. I would look at it at least (no promises). Mark it ATTN: IAN
>SMIT
Will this change be implemented in a future release?
Thanks for you answers so far, if someone has a smart solution to this
problem with no or "very" minimal changes to the application code please
post it! The customer would not like to make any changes in the old
applications nor the new ones.
Regards
Peter
|