CREATE OR ALTER TRIGGER ACTAS_BI FOR ACTAS
ACTIVE BEFORE INSERT POSITION 0
AS
DECLARE VARIABLE ANIO SMALLINT;
DECLARE VARIABLE GEN_NAME VARCHAR(31);
DECLARE VARIABLE BASE INTEGER;
DECLARE VARIABLE EXISTE INTEGER;
BEGIN
  IF (NEW.NRO_ACTA IS NULL) THEN BEGIN
    NEW.NRO_ACTA = GEN_ID(ORDEN_GEN, 1);
  END

  ANIO = EXTRACT(YEAR FROM NEW.FECHA);
  GEN_NAME = UPPER('ACTAS_' || ANIO || '_GEN');

    -- Base anual: 250000 para 2025
  BASE = ((ANIO - 2000) * 10000) + 1;

    -- Consulto si ya existe la sequence
  SELECT
      COUNT(*)
    FROM RDB$GENERATORS
    WHERE
      RDB$GENERATOR_NAME = :GEN_NAME
    INTO
      :EXISTE;

    -- Si no existe, la creo y la inicializo
  IF (ANIO > 2025) THEN BEGIN
    IF (EXISTE = 0) THEN BEGIN
      EXECUTE STATEMENT 'CREATE SEQUENCE ' || GEN_NAME;
      EXECUTE STATEMENT 'ALTER SEQUENCE ' || GEN_NAME || ' RESTART WITH ' || BASE;
    END
    -- Asigno número directo
    EXECUTE STATEMENT
      'SELECT NEXT VALUE FOR ' || GEN_NAME || ' FROM RDB$DATABASE'
        INTO NEW.NUMERO_ACTA;
  END ELSE BEGIN
    NEW.NUMERO_ACTA = ((EXTRACT(YEAR FROM NEW.FECHA) - 2000) * 1000) + GEN_ID(ACTAS_GEN, 1);
  END

END