How to use the return value from a stored procedure as a default value in a column?

Question

I need to use the return value from an stored procedure in firebird 3.0 as the default value in multiple columns.

My Procedure:

CREATE OR ALTER PROCEDURE CURRENTTIME 
returns (
    stime char(12))
as
begin
    sTime = (SELECT trim(Substring(CURRENT_TIMESTAMP FROM 12 FOR 12)) from RDB$
end

So, we have to fill in the current time in some specific columns named "NewOn" and "LastChangeOn". I found a default procedure from firebird itself called Current_Time but there are the milliseconds always '0000' e.g. '12:30:10.0000'. To bad that we need the milliseconds in our database. My own procedure give us the milliseconds but we can not use it in our table.

Example of a table:

CREATE TABLE USER (
    FIRSTNAME         CHAR(30),
    LASTNAME          CHAR(50),
    IDENT             CHAR(40) NOT NULL,
    NEWAT             DATE DEFAULT Current_Date NOT NULL,
    NEWON             CHAR(13) DEFAULT Current_Time(3) NOT NULL,
    NEWFROM           CHAR(25),
    LASTCHANGEAT      DATE DEFAULT Current_Date NOT NULL,
    LASTCHANGEON      CHAR(13) DEFAULT Current_Time(3) NOT NULL,
    LASTCHANGEFROM    CHAR(25)
);

When the table is created like this it calls the firebird-own procedure that gives us only the 0000 milliseconds. I tried to call my procedure like the firebird procedure. Also I tested it with and without "suspend" in my procedure but it makes no difference.

I haven't found a solution to this problem yet, so can anyone help me here?

Answer 1

I have tested it against Firebird 3, but failed to reproduce the problem. You might want to carefully check how you insert values (for example check that you don't assign that time yourself). You could also try to drop the default and set it again.

The column default-clause only allows literal values and context variables like current_time. If you want to assign your own default value, you will need to create a trigger:

create trigger user_BIU 
   before insert or update on "USER"
as
begin
  if (new.newon is null) then
  begin
     new.newon = trim(Substring(CURRENT_TIMESTAMP FROM 12 FOR 12));
  end
end

This has a slightly different effect than a default, as this will assign a value even if explicitly set to null. However as I couldn't reproduce the problem, I don't expect this to solve your problem.