Quote:
Originally posted by LKBrwn_DBA
You will need to create one package and THREE triggers, something like this:
Code:
Create Package Credito_Pkg
As
Type Codcliente_Typ Is Table Of Movimento.Codcliente%Type;
Type Codfilm_Typ Is Table Of Movimento.Codfilm%Type;
Type Codnegozio_Typ Is Table Of Movimento.Codnegozio%Type;
PV_Codcliente Codcliente_Typ;
PV_Codfilm Codfilm_Typ;
PV_Codnegozio Codnegozio_Typ;
Cnt_Cte Pls_Integer;
End;
/
Create Trigger Tb_Credito
Before Insert On Movimento
Begin
Credito_Pkg.Cnt_Cte := 0;
Credito_Pkg.PV_Codcliente.Delete;
Credito_Pkg.PV_Codfilm.Delete;
Credito_Pkg.PV_Codnegozio.Delete;
End;
/
Create Trigger Tar_Credito
After Insert On Movimento For Each Row
Begin
Credito_Pkg.Cnt_Cte := Credito_Pkg.Cnt_Cte + 1;
Credito_Pkg.PV_Codcliente(Credito_Pkg.Cnt_Cte) := :New.Codcliente;
Credito_Pkg.PV_Codfilm(Credito_Pkg.Cnt_Cte) := :New.Codfilm;
Credito_Pkg.PV_Codnegozio(Credito_Pkg.Cnt_Cte) := :New.Codnegozio;
End;
/
Create Trigger Ta_Credito
After Insert On Movimento
Declare
Periodo Number;
P Pls_Integer;
Begin
For P In 1..Credito_Pkg.Cnt_Cte Loop
Select Durata
Into Periodo
From Movimento
Where Codcliente = Credito_Pkg.PV_Codcliente(P)
And Codfilm = Credito_Pkg.PV_Codfilm(P)
And Codnegozio = Credito_Pkg.PV_Codnegozio(P);
If Periodo = 0
Then
Update Cliente
Set Credito = Credito - 2
Where User_Id=Credito_Pkg.PV_Codcliente(P);
Else
Update Cliente
Set Credito = Credito - (Periodo *3)
Where User_Id=Credito_Pkg.PV_Codcliente(P);
End If;
End Loop;
End;
/

NOTE: Is not optimized...
|
This line of code isn't exactly because I have an error and I don't know what I can do.
The error is:
Error: ORA-06531: Reference to uninitialized collection
ORA-06512: at "GL15.TB_CREDITO", line 3
ORA-04088: error during execution of trigger 'GL15.TB_CREDITO'
Thank you for your help. Elisa