Milan Babuskov wrote:

> Federico Tello Gentile wrote:
>
>
> No commits in between. You see, isql would commit after *each*
> statement. To do the same in FlameRobin 0.2.5, you'd have to:
>
> There are potential bugs if you execute DDL and don't commit right away.
> For example, the one I know for sure: it you add a column to table and
> update it before you commit it makes a mess.

Well, if that happens it is not a potential bug, it is a bug. Maybe if
you drop and recreate a table without committing, the foreing keys or
triggers get messed up. I don't know.

I know the error happend after inserting data back in the tables, and
the insert that fails in on table "socio".

>
> If it's not the problem, send it to me privately. Perhaps I'll notice
> something.

Here you are.

drop table pago;
drop table liquidacion;
drop table socio;
drop table cuota;
drop table colaboracion;
drop table institucion;
drop table persona;
drop domain address;
drop domain yearcol;
drop domain monthcol;
drop domain name;
drop domain email;
drop domain phone;
drop domain amount;
drop domain boolean;
drop generator id_generator;
drop exception CUOTA_INICIAL_INCORRECTA;
drop exception cuota_tiene_pagos;
drop exception pago_liquidado;
drop exception socio_duplicado;
create generator id_generator;
create domain boolean as char default 'N' not null check (value = 'S' or
value = 'N');
create domain address as varchar(200);
create domain yearcol as smallint check (value > 1900);
create domain monthcol as smallint check (value between 1 and 12);
create domain name as varchar(100);
create domain email as varchar(40) not null check (value = '' or value
like '%@%');
create domain phone as varchar(20);
create domain amount as decimal(8,2) check (value > 0);
create table persona (
id integer not null primary key,
nombre name not null,
telefono phone,
direccion_cobro address not null,
direccion address,
correo email
);
create table institucion(
id integer not null primary key,
nombre name not null,
telefono phone,
direccion address
);
create table colaboracion(
id integer not null primary key,
nombre name not null,
institucion integer not null references institucion(id) on delete
no action on update cascade
);
create table cuota(
id integer not null primary key,
cuota_mes monthcol,
cuota_ano yearcol,
monto amount,
colaboracion integer not null references colaboracion(id) on delete
no action on update cascade
);
create table socio(
id integer not null primary key,
numero integer not null,
baja boolean,
persona integer not null references persona(id) on delete no action
on update cascade,
colaboracion integer not null references colaboracion(id) on delete
no action on update cascade,
primera_cuota integer not null references cuota(id) on delete no
action on update cascade
);
create table liquidacion(
id integer not null primary key,
fecha date not null
);
create table pago(
id integer not null primary key,
fecha date not null,
socio integer not null references socio(id) on delete no action on
update cascade,
cuota integer not null references cuota(id) on delete no action on
update cascade,
liquidacion integer references liquidacion(id) on delete no action
on update cascade,
constraint pago_unico unique (socio, cuota)
);
create exception CUOTA_INICIAL_INCORRECTA 'La cuota inicial no
corresponde con la colaboracion a la que se asocia.';
create exception cuota_tiene_pagos 'No se puede editar la cuota si ya
tiene pagos.';
create exception pago_liquidado 'No se puede editar el pago si ya fue
liquidado.';
create exception socio_duplicado 'La persona ya es socio activo de esa
colaboracion.';
SET TERM ^ ;
CREATE TRIGGER ADD_INSTITUCION_ID FOR INSTITUCION
ACTIVE
BEFORE INSERT
AS
BEGIN
IF (NEW.ID IS NULL) THEN
NEW.ID = GEN_ID(ID_GENERATOR, 1);
END^
CREATE TRIGGER ADD_PERSONA_ID FOR PERSONA
ACTIVE
BEFORE INSERT
AS
BEGIN
IF (NEW.ID IS NULL) THEN
NEW.ID = GEN_ID(ID_GENERATOR, 1);
END^
CREATE TRIGGER ADD_COLABORACION_ID FOR COLABORACION
ACTIVE
BEFORE INSERT
AS
BEGIN
IF (NEW.ID IS NULL) THEN
NEW.ID = GEN_ID(ID_GENERATOR, 1);
END^
CREATE TRIGGER ADD_SOCIO_ID FOR SOCIO
ACTIVE
BEFORE INSERT
AS
BEGIN
IF (NEW.ID IS NULL) THEN
NEW.ID = GEN_ID(ID_GENERATOR, 1);
END^
CREATE TRIGGER ADD_CUOTA_ID FOR CUOTA
ACTIVE
BEFORE INSERT
AS
BEGIN
IF (NEW.ID IS NULL) THEN
NEW.ID = GEN_ID(ID_GENERATOR, 1);
END^
CREATE TRIGGER ADD_PAGO_ID FOR PAGO
ACTIVE
BEFORE INSERT
AS
BEGIN
IF (NEW.ID IS NULL) THEN
NEW.ID = GEN_ID(ID_GENERATOR, 1);
END^
CREATE TRIGGER ADD_LIQUIDACION_ID FOR LIQUIDACION
ACTIVE
BEFORE INSERT
AS
BEGIN
IF (NEW.ID IS NULL) THEN
NEW.ID = GEN_ID(ID_GENERATOR, 1);
END^
create trigger check_socio for socio
active
before insert or update
as
declare variable amount integer;
begin
select count (*)
from COLABORACION, CUOTA
where new.primera_cuota = cuota.id and
cuota.colaboracion = colaboracion.id and
colaboracion.id = new.colaboracion
into :amount;
if(amount = 0) then
EXCEPTION CUOTA_INICIAL_INCORRECTA;
end^
create trigger check_dup_socio for socio
active
before insert or update
as
declare variable amount integer;
begin
if (new.baja = 'N') then
select count (*)
from socio
where persona = new.persona and
colaboracion = new.colaboracion and
baja = 'N'
into :amount;
if(amount > 0) then
EXCEPTION socio_duplicado;
end^
create trigger check_update_cuota for cuota
active
before update
as
declare variable amount integer;
begin
select count (*)
from PAGO
where pago.CUOTA = old.id
into :amount;
if(amount > 0) then
EXCEPTION cuota_tiene_pagos;
end^
create trigger check_update_pago for pago
active
before update
as
begin
if(old.liquidacion is not null) then
EXCEPTION pago_liquidado;
end^
SET TERM ; ^