Skip to Main Content

Triggers on dates

912754Jan 26 2012 — edited Jan 26 2012
Hi,

I have a small database project and I need triggers to validate the insertion of dates. Here is the table creation.


create table utente (id_utente number(5) Primary Key,
nome varchar2(40),
sexo varchar2(1) check (sexo='M' or sexo='F'),
morada varchar2(60),
data_nascimento date,
contacto number(10),
numero_BI number(9)
);

create table servico (id_servico number(5) Primary Key,
descricao varchar2(40),
valor number(5),
data_inicio date,
data_fim date
);

create table modalidade (id_modalidade number(5) Primary Key,
descricao varchar2(40),
valor number(5)
);

create table quarto (id_quarto number(5) Primary Key,
descricao varchar2(40)
);

create table inscricao_servico (id_inscricao_servico number(5) Primary Key,
id_servico number(5) references servico(id_servico),
id_utente number(5) references utente(id_utente),
data_pagamento date
);

create table inscricao_mod (id_inscricao_mod number(5) Primary Key,
id_modalidade number(5) references modalidade(id_modalidade),
id_utente number(5) references utente(id_utente),
data_inscricao date,
data_cessacao date
);

create table estadia (id_estadia number(5) Primary Key,
id_utente number(5) references utente(id_utente),
id_quarto number(5) references quarto(id_quarto),
data_entrada date,
data_saida date
);

create table pag_mensalidade (id_pag_mensalidade number(5) Primary Key,
id_inscricao_mod number(5) references inscricao_mod(id_inscricao_mod),
mes_mensalidade number(6),
data_pagamento date
);

And the data insertion:

insert into utente values (1, 'Manel', 'M', 'Rua grande', to_date('80.02.02','yy.mm.dd'), 123456789, 687654321);
insert into utente values (2, 'Artur', 'M', 'Rua pequena', to_date('81.03.04', 'yy.mm.dd'), 223456789, 587654321);
insert into utente values (3, 'Cardoso', 'M', 'Rua estreita', to_date('82.04.05', 'yy.mm.dd'), 323456789, 487654321);
insert into utente values (4, 'AbĂ­lio', 'M', 'Rua larga', to_date('79.07.06', 'yy.mm.dd'), 423456789, 787654321);
insert into utente values (5, 'Antunes', 'M', 'Rua nova', to_date('79.06.01', 'yy.mm.dd'), 423557982, 387654822);

insert into modalidade values (1, 'Total', 800);
insert into modalidade values (2, 'Diurno ate 18', 600);
insert into modalidade values (3, 'Diurno ate 22', 700);

insert into quarto values (1, '412');
insert into quarto values (2, '413');
insert into quarto values (3, '414');
insert into quarto values (4, '415');
insert into quarto values (5, '416');

insert into servico values (1, 'Excursao a Fatima', 150, to_date('11.05.13', 'yy.mm.dd'), to_date('11.05.13', 'yy.mm.dd'));
insert into servico values (2, 'Ida ao cinema', 10, to_date('11.08.22', 'yy.mm.dd'), to_date('11.08.22', 'yy.mm.dd'));
insert into servico values (3, 'Apoio domiciliario', 100, to_date('11.07.01', 'yy.mm.dd'), to_date('11.07.31', 'yy.mm.dd'));

insert into estadia values (1, 1, 1, to_date('11.05.05', 'yy.mm.dd'), null);
insert into estadia values (2, 2, 2, to_date('10.01.02', 'yy.mm.dd'), null);
insert into estadia values (3, 3, 3, to_date('09.12.15', 'yy.mm.dd'), to_date('10.02.25', 'yy.mm.dd'));

insert into inscricao_mod values (1, 1, 1, to_date('09.12.15', 'yy.mm.dd'), null);
insert into inscricao_mod values (2, 2, 2, to_date('11.11.11', 'yy.mm.dd'), null);
insert into inscricao_mod values (3, 1, 3, to_date('10.04.10', 'yy.mm.dd'), to_date('11.09.21', 'yy.mm.dd'));

insert into inscricao_servico values (1, 1, 1, null);
insert into inscricao_servico values (2, 1, 2, to_date('11.10.01', 'yy.mm.dd'));
insert into inscricao_servico values (3, 2, 4, null);
insert into inscricao_servico values (4, 3, 5, to_date('10.12.12', 'yy.mm.dd'));

insert into pag_mensalidade values (1, 1, 201110, to_date('11.10.23', 'yy.mm.dd'));
insert into pag_mensalidade values (2, 2, 201111, to_date('11.11.27', 'yy.mm.dd'));
insert into pag_mensalidade values (3, 3, 201112, NULL);
insert into pag_mensalidade values (4, 1, 201111, NULL);

Now, to check the content of the tables:

select * from utente;
select * from estadia;
select * from inscricao_mod;
select * from inscricao_servico;
select * from modalidade;
select * from pag_mensalidade;
select * from quarto;
select * from servico;


What I need is a trigger that controls the dates insertion on the table utente. 'Data_entrada' is the entrance date of a client and 'Data_saida' the exit date. I need a trigger to avoid insertion of dates like, the client enters the 9th July and leaves the 5th July.

I mean, the 'Data_entrada' must be prior to 'Data_saida'. Note that 'Data_saida' can be null.

Can someone help me?

Thanks

Chiapa
Comments
Post Details
Added on Jan 26 2012
14 comments
144 views