[spl] varying default values
Posted in 2000
the problem:
i have a family of stored procedures to do their jobs on a defined
structure of tables. one of the procedure logically starts an action by
creating a new record in my ACTION table and gets an action identifier
(serial). it stores it in a global variable CurrentTask.
when updating and deleting records from my DATA table the original
records must be stored in an ARCHIVE (same structure like DATA plus
DeleteActionIdent). The DeleteActionIdent shoul has the value of the
current CurrentTask.
the solution (2nd try :-)
the action starting procedure alters ARCHIVE in that way:
ALTER TABLE ARCHIVE MODIFY DeleteActionIdent INT DEFAULT CurrentTask;
then triggers for DELETE and UPDATE easily stores the old DATA record in
ARCHIVE.
(btw.: the action ending procedure alters ARCHIVE again:
ALTER TABLE ARCHIVE MODIFY DeleteActionIdent INT
DEFAULT 0 CHECK (DeleteActionIdent > 0);to disable the update and delete of DATA records from outside an action.
it works very well ;-)
unfortunately the first ALTER wants a constant default value... not a
stored procedure variable. so i get always syntactical errors.
is there an easy way to implement the desired functionality (without
declaring a copy stored procedere that gets the whole stuff from the old
DATA record and insert it into ARCHIVE)?
thanks for helping
uwe doetzkies