about trigger ..

what's the problem?
create trigger T_credito
after insert on movimento
declare
periodo number;
begin
select durata into periodo from movimento
where codcliente=:new.codcliente and codfilm=:new.codfilm and codnegozio=new.codnegozio;
if periodo = 0
then
update CLIENTE
set credito=credito - 2
where user_id= :new.codcliente;
else
update CLIENTE
set credito=credito - (periodo *3)
where user_id= :new.codcliente;
end;
thank you ElisaHi,

This is the problem:

where codcliente=:new.codcliente and codfilm=:new.codfilm
and codnegozio=new.codnegozio;

In an after insert trigger you no longer have access to the :new and :old variables.

;)|||Originally posted by LKBrwn_DBA
Hi,

This is the problem:

where codcliente=:new.codcliente and codfilm=:new.codfilm
and codnegozio=new.codnegozio;

In an after insert trigger you no longer have access to the :new and :old variables.

;)

I understand but what I can need? It's very important|||You will need to create one package and THREE triggers, something like this:

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;
/

:cool:

NOTE: Is not optimized...|||Originally posted by LKBrwn_DBA
You will need to create one package and THREE triggers, something like this:

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;
/

:cool:

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|||True,

Change these lines:

Credito_Pkg.PV_Codcliente.Delete;
Credito_Pkg.PV_Codfilm.Delete;
Credito_Pkg.PV_Codnegozio.Delete;

To this:

Credito_Pkg.PV_Codcliente := Credito_Pkg.Codcliente_Typ();
Credito_Pkg.PV_Codfilm := Credito_Pkg.Codfilm_Typ();
Credito_Pkg.PV_Codnegozio := Credito_Pkg.Codnegozio_Typ();

;)|||I know I'm boring you but with the new line there is a new error:

Error: ORA-06533: Subscript beyond count
ORA-06512: at "GL15.TAR_CREDITO", line 3
ORA-04088: error during execution of trigger 'GL15.TAR_CREDITO'

and I don't understand what's the problem.
If you can help me I'm very happy.
Thank you,
Elisa|||Since the trigger does update operations you will be able to make the trigger a before insert. If the insert fails by any chance there is no problem since the effect of the trigger is also reversed.|||I don't understand .. I'm afraid .. can you explain another time.
Thank you|||Elisa,

Pls change this:

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;
...etc...

To This:

Create Package Credito_Pkg
As
Type Codcliente_Typ Is Table Of Movimento.Codcliente%Type Index By Binary_Integer;
Type Codfilm_Typ Is Table Of Movimento.Codfilm%Type Index By Binary_Integer;
Type Codnegozio_Typ Is Table Of
Movimento.Codnegozio%Type Index By Binary_Integer;
...etc...

:D|||Thank you very much, Elisa