Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2003
    Location
    Vicenza(I)
    Posts
    21

    Unhappy Unanswered: 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

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    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

  3. #3
    Join Date
    Aug 2003
    Location
    Vicenza(I)
    Posts
    21
    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

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    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

  5. #5
    Join Date
    Aug 2003
    Location
    Vicenza(I)
    Posts
    21
    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

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    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

  7. #7
    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

  8. #8
    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.

  9. #9
    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

  10. #10
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    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

  11. #11
    Join Date
    Aug 2003
    Location
    Vicenza(I)
    Posts
    21

    Talking

    Thank you very much, Elisa
    Elisa Pinton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •