--- In firebird-support@yahoogroups.com, "Ismael L. Donis García" <ismael@...> wrote:

>
> Hi to all
>
> I have a problem I could have solved which
>
> Have a table
>
> CREATE TABLE CTCTOC
> (
> IDCTOC INT_NNULL NOT NULL,
> NUM INT_NNULL,
> FECHAE DAT_NNULL,
> FECHAV DAT_NNULL,
> EMPRESA VCH_7_NNULL,
> IDORG INT_NNULL,
> IDPROV INT_NNULL,
> IDTCTO INT_NNULL,
> OBJETO VCH_54_NULL DEFAULT '',
> RESP VCH_35_NULL DEFAULT '',
> TEMP SIN_NNULL_0 DEFAULT 0,
> CONSTRAINT PK_CTCTOC PRIMARY KEY (IDCTOC)
> );
>
> GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
> ON CTCTOC TO SYSDBA WITH GRANT OPTION;
>
> With a trigger
>
> SET TERM ^ ;
> CREATE TRIGGER CTCTOC_BI FOR CTCTOC ACTIVE
> BEFORE INSERT POSITION 0
> AS
> DECLARE int_num INTEGER;
> begin
> /* Trigger para actualizar la llave primaria y el número de contrato*/
> select max(a.num) as num from ctctoc a where (extract(year from a.fechae) = extract(year from new.fechae)) into :int_num;
> if (row_count = 0) then
> begin
> new.idctoc = extract(year from new.fechae) || 1;
> new.num = 1;
> end
> else
> begin
> new.idctoc = extract(year from new.fechae) || (int_num + 1);
> new.num = (int_num + 1);
> end
> end^
> SET TERM ; ^
>
> Executing...
> Error: *** IBPP::SQLException ***
> Context: Statement::Execute( insert into ctctoc (fechae, fechav, empresa, idorg, idprov, idtcto, objeto, resp, temp)
> values ('2010/09/28','2010/09/30','3525',1,2,1,'Test of contract','Ismael L. Donis García',0)
> )
> Message: isc_dsql_execute2 failed
>
> SQL Message : -625
> The insert failed because a column definition includes validation constraints.
>
> Engine Code : 335544347
> Engine Message :
> validation error for column IDCTOC, value "*** null ***"
>
>
> Regards
> =========
> || ISMAEL ||
> =========
>
> [Non-text portions of this message have been removed]
>

I'm not sure about but I think that your select that fills :int_num is returning nothing and causing int_num be null. Thus therefore, causes idctoc return null when you use int_num.

if is that, you can correct it using a coalesce in this line

new.idctoc = extract(year from new.fechae) || (coalesce(int_num,0) + 1);

sorry my english this time as I wrote it in kinda hurry.
WTH,
Regards