If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Data Access, Manipulation & Batch Languages > ANSI SQL > about trigger ..

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 09-23-03, 10:44
trilly trilly is offline
Registered User
 
Join Date: Aug 2003
Location: Vicenza(I)
Posts: 21
Unhappy 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 Elisa
__________________
Elisa Pinton
Reply With Quote
  #2 (permalink)  
Old 09-23-03, 10:59
LKBrwn_DBA LKBrwn_DBA is offline
Registered User
 
Join Date: Jun 2003
Location: West Palm Beach, FL
Posts: 2,455
Exclamation

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 ld variables.

__________________
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
Reply With Quote
  #3 (permalink)  
Old 09-23-03, 11:06
trilly trilly is offline
Registered User
 
Join Date: Aug 2003
Location: Vicenza(I)
Posts: 21
Quote:
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 ld variables.

I understand but what I can need? It's very important
__________________
Elisa Pinton
Reply With Quote
  #4 (permalink)  
Old 09-23-03, 16:11
LKBrwn_DBA LKBrwn_DBA is offline
Registered User
 
Join Date: Jun 2003
Location: West Palm Beach, FL
Posts: 2,455
Lightbulb

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...
__________________
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
Reply With Quote
  #5 (permalink)  
Old 09-24-03, 11:48
trilly trilly is offline
Registered User
 
Join Date: Aug 2003
Location: Vicenza(I)
Posts: 21
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
__________________
Elisa Pinton
Reply With Quote
  #6 (permalink)  
Old 09-24-03, 18:21
LKBrwn_DBA LKBrwn_DBA is offline
Registered User
 
Join Date: Jun 2003
Location: West Palm Beach, FL
Posts: 2,455
Exclamation

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();


__________________
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
Reply With Quote
  #7 (permalink)  
Old 09-25-03, 06:04
trilly trilly is offline
Registered User
 
Join Date: Aug 2003
Location: Vicenza(I)
Posts: 21
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
__________________
Elisa Pinton
Reply With Quote
  #8 (permalink)  
Old 09-25-03, 07:01
tm_suren tm_suren is offline
Registered User
 
Join Date: Sep 2003
Location: Colombo, Sri Lanka
Posts: 63
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.
Reply With Quote
  #9 (permalink)  
Old 09-25-03, 09:25
trilly trilly is offline
Registered User
 
Join Date: Aug 2003
Location: Vicenza(I)
Posts: 21
I don't understand .. I'm afraid .. can you explain another time.
Thank you
__________________
Elisa Pinton
Reply With Quote
  #10 (permalink)  
Old 09-25-03, 12:01
LKBrwn_DBA LKBrwn_DBA is offline
Registered User
 
Join Date: Jun 2003
Location: West Palm Beach, FL
Posts: 2,455
Exclamation

Elisa,

Code:

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...
__________________
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
Reply With Quote
  #11 (permalink)  
Old 09-26-03, 05:59
trilly trilly is offline
Registered User
 
Join Date: Aug 2003
Location: Vicenza(I)
Posts: 21
Talking

Thank you very much, Elisa
__________________
Elisa Pinton
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On