create table Banco
(
BancoID int not null,
Nombre varchar(50) not null,
constraint pkBanco primary key(BancoID)
)
create table Cuenta
(
BancoID int not null,
CuentaID varchar(50) not null,
Tipo varchar(1) not null,
Debitos float,
Creditos float,
constraint pkCuentas primary key(BancoID,CuentaID),
constraint fkBancos foreign key(BancoID) references Banco
)
create table Periodo
(
PeriodoID int not null,
Inicio datetime not null,
Final datetime not null,
Estado varchar(1),
constraint pkPeriodo primary key(PeriodoID)
)
create table Cheque
(
BancoID int not null,
CuentaID varchar(20) not null,
ChequeID int not null,
fecha datetime not null,
Beneficiario varchar(50) not null,
Concepto varchar(100) not null,
Valor float not null,
Estado varchar(1) not null,
constraint pkCheque primary key(BancoID,CuentaID,ChequeID),
constraint fkCuentas foreign key(BancoID,CuentaID) references Cuenta
create table Deposito
(
BancoID int not null,
CuentaID varchar(20) not null,
DepositoID int not null,
fecha datetime not null,
Observacion varchar(100),
Valor float not null,
Estado varchar(1) not null,
constraint pkDeposito primary key(BancoID,CuentaID,DepositoID),
constraint fkCuenta foreign key(BancoID, CuentaID) references Cuenta
)
EJERCICIO 1.
ALTER trigger trgInsertarCheque on Cheque AFTER insert
as
declare @estado varchar(1)
select @estado=Estado from inserted
if @estado='G' OR @estado='P' OR @estado='N'
UPDATE Cheque SET Estado=@estado
ELSE
ROLLBACK
go
ALTER trigger trgInsertarDeposito on Deposito AFTER insert
as
declare @estado varchar(1)
select @estado=Estado from inserted
if @estado='G' OR @estado='P' OR @estado='N'
UPDATE Cheque SET Estado=@estado
ELSE
ROLLBACK
go
SELECT *FROM Cheque
UPDATE Cheque SET Estado='N'
EJERCICIO 2.
alter table Cuenta ADD constraint chkDebito check(Debitos<Creditos)
EJERCICIO 3.
ALTER TRIGGER trgEstadoInicial on Cheque FOR insert
as
declare @estado varchar(1)
declare @cheque int
select @estado=Estado from inserted
begin tran trEstadoInicial
if @estado='G'
commit tran trEstadoInicial
ELSE
ROLLBACK tran trsEstadoInicial
go
insert into Cheque values(101,'70-200',4,GETDATE(),'MIGUEL','PAGO PLANILLA',500,'G')
ALTER proc spProcesAnularChk @chequeID int, @estado varchar(1)
as
BEGIN TRAN
declare @valor float
declare @codigo varchar(20)
UPDATE Cheque SET Estado=@estado WHERE ChequeID=@chequeID
select @codigo=CuentaID, @valor=Valor from Cheque where ChequeID=@chequeID
UPDATE Cuenta set Debitos=
case
when @estado='P' THEN Debitos+@valor
WHEN @estado='N' THEN Debitos-@valor
END
where CuentaID=@codigo
if @@ERROR=0
COMMIT
ELSE
ROLLBACK
go
spProcesAnularChk 1,'P'
SELECT *FROM Cuenta
SELECT *FROM Cheque
INSERT INTO Cheque VALUES(101,'70-200',2,GETDATE(),'SUAMY LAGOS','PAGO
PLANILLA',200,'G')
EJERCICIO 5.
select *from Cheque
SELECT *FROM Cuenta
insert into Deposito values (101,'720-200',2,GETDATE(),'PAGO PLANILLA #15',2000,'A')
UPDATE Cuenta SET Creditos=10000
alter trigger trgProcesarChck on Cheque for UPDATE
AS
DECLARE @total float
DECLARE @cuenta varchar(20)
DECLARE crsDebitos cursor for
select CuentaID,SUM(Valor) from inserted
group by CuentaID
OPEN crsDebitos
fetch next from crsDebitos into @cuenta,@total
while @@FETCH_STATUS=0
begin
update Cuenta set Creditos=Creditos-@total where
CuentaID=@cuenta
fetch next from crsDebitos into @cuenta,@total
end
close crsDebitos
deallocate crsDebitos
GO
UPDATE Cheque SET Estado='P'
SELECT *from Cheque
SELECT *FROM Cuenta
insert into Cheque values(101,'70-200',5,GETDATE(),'SUAMY LAGOS','PAGO
PLANILLA',2000,'G')
UPDATE Cheque SET Estado='P' WHERE CuentaID='70-200' AND ChequeID=1
SELECT CuentaID,SUM(Valor) FROM Cheque
GROUP BY CuentaID
alter FUNCTION dbo.fTotales( @banco int ,@cuenta varchar(20), @periodo datetime,
@tipo varchar(1)) returns float
as
begin
declare @totalCheques float,@totaldepositos float
declare @fechaIni datetime, @fechaFin datetime
select @fechaIni=Inicio,@fechaFin=Final from Periodo
select @totalCheques=sum(Valor) from Cheque where BancoID= @banco and
CuentaID=@cuenta and @periodo>=Periodo.Inicio and @periodo<=Periodo.Final
group by BancoID,CuentaID
return @totalcheques
end
EJERCICIO 7.
alter function dbo.fCuentas(@banco int,@cuenta varchar(20))
returns @cuentas table(banco int ,nombreBanco varchar(50),cuenta
varchar(20),documento int,
fecha datetime,observacion varchar(100),debito float,credito float,estado
varchar(1))
as
begin
declare @derechoHabiente table (cuentaID varchar(20))
insert into @derechoHabiente select CuentaID from Cuenta where
CuentaID=@cuenta
insert into @cuentas select
b.BancoID,b.Nombre,c.CuentaID,ck.ChequeID,ck.fecha,
ck.Concepto,c.Debitos,c.Creditos,ck.Estado from Cuenta c
left join Banco b on b.BancoID=c.BancoID
left join Cheque ck on ck.CuentaID=c.CuentaID
return
end
go
select *from Cuenta
select *from dbo.fCuentas(101,'70-200')
--8
ALTER function dbo.fTotalEstado(@banco int,@cuenta
varchar(20),@estado varchar(1),@tipo varchar(1))
returns float
as
begin
declare @totalcheque float
select @totalcheque=valor from dbo.vTotalCheque
where @banco=BancoID AND @cuenta=CuentaID
return @totalcheque
end
go
alter view vTotalCheque as
select c.BancoID ,c.CuentaID,SUM(Valor)as valor,ck.Estado from Cuenta
c
left join Cheque ck on ck.CuentaID=c.CuentaID
group by c.BancoID,c.CuentaID, ck.Estado
SELECT dbo.fTotalEstado(BancoID,CuentaID,'P','C')
FROM Cuenta
--9
CREATE FUNCTION dbo.fSaldos() returns
@saldos table (bancoID INT, Nombrebanco varchar(50),cuentaID
VARCHAR(20),debito float, credito float, saldo float)
as
begin
insert into @saldos
select c.BancoID,b.Nombre,c.CuentaID,c.Debitos,c.Creditos,
c.Creditos-c.Debitos as saldo from Cuenta c
inner join Banco b on c.BancoID=b.BancoID
return
end
go
use db20142030491
--ejercicio 5
create trigger tgrModificarCheque on Cheque for update
as
declare @estado1 as varchar, @estado2 as varchar, @valor float, @cuentaid varchar(20)
select @estado1 = estado from deleted
select @estado2 = estado, @valor =valor, @cuenta = cuentaid from inserted
if @estado <> @estado2
update cuenta set debito = Debito + @valor * case when @estado2 ='p' then 1 else -1 end
where cuentaid= @cuenta
go
--ejercicio 3
create trigger trgInsertarCheque on Cheque for insert
as
declare @estado as varchar
select @estado = estado from inserted
if @estado <> 'G'
update cuenta set debito= 1000, credito = 0
go
-- ejercicio 7
create function dbo.funcionsiete (@banco int, @cuenta varchar(20), @fecha1 datetime, @fecha2
datetime)
returns @mov table (BancoID int, @nombreBanco varchar (50), CuentaID varchar(20),
DocumentoID int, Fecha datetime, Observacion varchar(200),
Debito float, Credito float, Estado varchar)
as
insert into @mov
select c.bancoid, b.nombre, c.cuentaid, c.chequeid, c.fecha, c.concepto, c.valor, 0, c.estado
from cheque as c
inner join Banco as b on c.bancoid = b.bancoid
union
select d.bancoid, b.nombre, d.cuentaid, d.depositoid, d.fecha, d.observacion, 0, d.valor, d.estado
from deposito as d
inner join Banco as c on d.bancoid = c.bancoid
return
go
