Hi!

Your requirement is somewhat based of an existence check, which might be
much better by using the EXIST predicat. E.g.:

if (exists(select 1 from ctctoc a where extract(year from a.fechae) =
extract(year from new.fechae))) then
begin
...
end
else
begin
...
end


Just a thought. ;-)

--
Best Regards,
Thomas Steinmaurer
LogManager Series - Logging/Auditing Suites supporting
InterBase, Firebird, Advantage Database, MS SQL Server and
NexusDB V2
Upscene Productions
http://www.upscene.com
My blog:
http://blog.upscene.com/thomas/

> Solved problem
>
> SET TERM ^ ;
> ALTER TRIGGER CTCTOC_BI 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*/
> if (int_num is null) 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 ; ^
>
> Regards
> =========
> || ISMAEL ||
> =========
> ----- Original Message -----
> From: Ismael L. Donis García
> To: firebird-support@yahoogroups.com
> Sent: Tuesday, September 28, 2010 11:57 AM
> Subject: [firebird-support] Error to insert FB 2.1.4 18349 Win32
>
>
>
> 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]
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>
>
>