CREATE TABLE Conta (

Id int IDENTITY (1, 1) NOT NULL ,
Agencia char (2) NOT NULL ,
Conta char (3) NOT NULL ,
Nome char (20) NOT NULL ,
Saldo money NOT NULL

) ON [PRIMARY]
GO

ALTER TABLE Conta WITH NOCHECK ADD CONSTRAINT PK_Conta PRIMARY KEY CLUSTERED (Id) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Conta] ADD CONSTRAINT IX_Conta UNIQUE NONCLUSTERED (Agencia,Conta) ON [PRIMARY]
GO

CREATE TABLE ContaEspecial (

Id int NOT NULL ,
Limite money NOT NULL

) ON [PRIMARY]
GO

ALTER TABLE ContaEspecial WITH NOCHECK ADD

CONSTRAINT PK_ContaEspecial PRIMARY KEY CLUSTERED (Id) ON [PRIMARY]

GO

CREATE TABLE Movimento (

Id int IDENTITY (1, 1) NOT NULL ,
IdConta int NOT NULL ,
Tipo char (1) NOT NULL ,
Data datetime NOT NULL ,
valor money NOT NULL ,

) ON [PRIMARY]
GO

CREATE TRIGGER Movimento_Insert On Movimento For Insert As
Begin

Declare @qt_rows int,

@Msg char(255),
@Saldo money,
@Limite money,
@Tipo char(1),
@Valor money

Select @Saldo = 0
Select @Limite = 0

Select @qt_rows = @@rowcount
If @qt_rows = 0

Return

Select @Tipo = Tipo from inserted
Select @Valor = Valor from inserted

If Not Exists (Select 1 from Conta C, inserted I where C.Id = I.IdConta)
Begin

Select @Msg = ‘AAAAA’
Raiserror 23000 @Msg
Rollback Transaction
Return

End
Else

Select @Saldo = Saldo from Conta C, inserted I where C.Id = I.IdConta

If Exists (Select 1 from ContaEspecial E, inserted I where E.Id = I.IdConta)

Select @Limite = Limite from ContaEspecial E, inserted I where E.Id = I.IdConta

If (@Tipo = ‘D’)
Begin

If ((@Saldo + @Limite) < @Valor)
Begin

Select @Msg = ‘BBBBB’
Raiserror 23000 @Msg

Rollback Transaction
Return

End
Select @Saldo = @Saldo - @Valor

End
Else

Select @Saldo = @Saldo + @Valor

Update Conta Set Saldo = @Saldo From inserted I where Conta.Id = I.IdConta

End
GO

Insert Into Conta (Agencia,Conta,Nome,Saldo) Values (‘01’,’001',’Carlos Almeida’,23.78) GO
Insert Into Conta (Agencia,Conta,Nome,Saldo) Values (‘01’,’007',’Ana Seixas’,657.90) GO
Insert Into Conta (Agencia,Conta,Nome,Saldo) Values (‘01’,’008',’Rita Pinto’,6.00) GO
Insert Into Conta (Agencia,Conta,Nome,Saldo) Values (‘01’,’011',’Roger Santos’,144.97) GO
Insert Into Conta (Agencia,Conta,Nome,Saldo) Values (‘01’,’014',’Gil Santos’,77.11) GO
Insert Into Conta (Agencia,Conta,Nome,Saldo) Values (‘02’,’001',’Mina Rocha’,1234.22) GO

Insert Into Conta (Agencia,Conta,Nome,Saldo) Values (‘02’,’003',’Carla Brito’,7887.43) GO
Insert Into Conta (Agencia,Conta,Nome,Saldo) Values (‘02’,’004',’Rosa Silva’,0.12) GO
Insert Into ContaEspecial Select Id,100.00 From Conta Where Agencia = ‘01’ And Conta = ‘007’ GO
Insert Into ContaEspecial Select Id,200.00 From Conta Where Agencia = ‘01’ And Conta = ‘011’ GO
Insert Into ContaEspecial Select Id,150.00 From Conta Where Agencia = ‘02’ And Conta = ‘002’ GO
Insert Into ContaEspecial Select Id,150.00 From Conta Where Agencia = ‘02’ And Conta = ‘003’ GO
Insert Into Movimento (IdConta,Tipo,Data,valor) Values (1, ‘C’,’2011-01-03 10:03:03',24.12) GO
Insert Into Movimento (IdConta,Tipo,Data,valor) Values (10,’C’,’2011-01-05 17:11:34',300.00) GO
Insert Into Movimento (IdConta,Tipo,Data,valor) Values (3, ‘D’,’2011-01-05 18:40:11',15.66) GO
Insert Into Movimento (IdConta,Tipo,Data,valor) Values (7, ‘D’,’2011-01-10 08:22:01',1200.00) GO
Insert Into Movimento (IdConta,Tipo,Data,valor) Values (4, ‘D’,’2011-01-12 19:18:49',320.10) GO
Insert Into Movimento (IdConta,Tipo,Data,valor) Values (4, ‘D’,’2011-01-16 07:11:20',45.89) GO