> Hi,
> I have a table (BROKERS) with a PK of domain D_PK of type Integer. I
> also have a stored procedure that generates a PK value for this table,
> as follows:
>
> CREATE PROCEDURE P_BROKER_ID returns (
> BROKER_ID Integer)
> AS
> DECLARE VARIABLE DT1 TIMESTAMP;
> DECLARE VARIABLE DT2 TIMESTAMP;
> DECLARE VARIABLE DT DECIMAL(9,4);
> DECLARE VARIABLE ID INTEGER;
> begin
> DT1 = CURRENT_TIMESTAMP;
> DT2 = CAST('1.1.2005' AS TIMESTAMP);
> DT = DT1 - DT2;
> ID = CAST((DT * 24 * 60 * 60) AS INTEGER);
> BROKER_ID = ID;
> end
>
> in other words, the value of BROKER_ID is equal to the number of
> elapsed seconds since 1 Jan 2005. I've tested this and it works. I

I've had to fix systems which do something like this and end up getting
errors when 2 people both create a record at the same time. or as is more
often the case, when 2 systems are being merged sometime in the future,
there are duplicate PK values all over the place.
I assume you know the limitations of this method.
Alan

> intend to have a BEFORE INSERT trigger that injects this value into
> the PK of BROKERS, as follows (there IS a good reason why I am not
> using an autoinc trigger/generator):
>
> CREATE TRIGGER BROKERS_NEW_ID FOR BROKERS ACTIVE BEFORE INSERT
> POSITION 0 AS
>
> DECLARE VARIABLE N INTEGER;
> BEGIN
> EXECUTE PROCEDURE P_BROKER_ID(N);
> IF (NEW.ID IS NULL) THEN
> NEW.ID = N;
> END;
>
> When I come to save this code I get the following error:
>
> ISC ERROR MESSAGE
> invalid request BLR at offset 30
> parameter mismatch for procedure P_BROKER_ID
>
> Has this got something to do with the types being different i.e.
> Integer and D_PK, even though they are both integer types? What should
> I do in this case?
>
> TIA
> Ron Szumski