Results 1 to 6 of 6

Thread: trigger

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

    Red face Unanswered: trigger

    This is the trigger:

    create trigger T_Noleggio
    after insert on Noleggio
    for each row
    begin
    IF((:new.codcliente=prenotazione.codcliente)&&(:ne w.codfilm=prenotazione.codfilm)
    &&(:new.codnegozio=prenotazione.codnegozio)
    delete prenotazione
    end

    what's the problem??
    Thank, you Elisa

    The table Prenotazione is:
    CREATE TABLE Prenotazione
    (CodFilm NUMBER(10) NOT NULL,
    CodNegozio NUMBER(10) NOT NULL,
    CodCliente VARCHAR(10) NOT NULL,
    Data_Prenotazione date NOT NULL,
    Supporto VARCHAR2(3) NOT NULL,
    PRIMARY KEY(CodFilm,CodNegozio,CodCliente,Data_Prenotazion e),
    FOREIGN KEY(CodNegozio)
    REFERENCES Negozio(IdNegozio)
    ON DELETE CASCADE,
    FOREIGN KEY(CodFilm)
    REFERENCES Film(IdFilm)
    ON DELETE CASCADE,
    FOREIGN KEY(CodCliente)
    REFERENCES Cliente(User_id)
    ON DELETE CASCADE)

    and the table noleggio is:

    CREATE TABLE Noleggio
    (CodFilm NUMBER(10) NOT NULL,
    CodNegozio NUMBER(10) NOT NULL,
    CodCliente VARCHAR(10) NOT NULL,
    Data_Noleggio date NOT NULL,
    Supporto VARCHAR2(3) NOT NULL,
    PRIMARY KEY(CodFilm,CodNegozio,CodCliente,Data_Noleggio),
    FOREIGN KEY(CodNegozio)
    REFERENCES Negozio(IdNegozio)
    ON DELETE CASCADE,
    FOREIGN KEY(CodFilm)
    REFERENCES Film(IdFilm)
    ON DELETE CASCADE,
    FOREIGN KEY(CodCliente)
    REFERENCES Cliente(User_id)
    ON DELETE CASCADE)

  2. #2
    Join Date
    May 2003
    Location
    Dublin, Ireland
    Posts
    44

    Re: trigger

    Elisa,

    You are trying to compare values passed to the trigger by insert statement with the values from anothe table without retrieving those values from the table.
    How this poor trigger should know what is the value of for example prenotazione.codcliente ?

    Hope it explains it,

    Jacek
    Originally posted by trilly
    This is the trigger:

    create trigger T_Noleggio
    after insert on Noleggio
    for each row
    begin
    IF((:new.codcliente=prenotazione.codcliente)&&(:ne w.codfilm=prenotazione.codfilm)
    &&(:new.codnegozio=prenotazione.codnegozio)
    delete prenotazione
    end

    what's the problem??
    Thank, you Elisa


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

    Re: trigger

    Originally posted by sjacek
    Elisa,

    You are trying to compare values passed to the trigger by insert statement with the values from anothe table without retrieving those values from the table.
    How this poor trigger should know what is the value of for example prenotazione.codcliente ?

    Hope it explains it,

    Jacek
    The value of prenotazione.codcliente is carchar2, while the value of prenotazione.idfilm nd prenotazione.ifnegozio is number.
    Thank you Elisa

  4. #4
    Join Date
    May 2003
    Location
    Dublin, Ireland
    Posts
    44

    Re: trigger

    As far as I understand what you are trying to do trigger should look something like that:

    create trigger T_Noleggio
    after insert on Noleggio
    for each row
    begin
    delete from prenotazione where
    codcliente =:new.codcliente and
    codfilm=:new.codfilm and
    codnegozio=:new.codnegozio;
    end;

    It will delete from prenotazione row with matching new values.

    Hope it helps,

    Jacek



    Originally posted by trilly
    The value of prenotazione.codcliente is carchar2, while the value of prenotazione.idfilm nd prenotazione.ifnegozio is number.
    Thank you Elisa

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

    Re: trigger

    thank you for your help but when I do an insert on noleggio the result is:
    Error: ORA-04098: trigger 'T_NOLEGGIO' is invalid and failed re-validation

    The problem is this I've two table prenotazione and noleggio with the same field but when I insert a new value on noleggio I check if the value is on the table prenotazione and if there is I want to delete.
    What's the trigger that resolv this problem?
    Thank you
    Elisa

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

    Re: trigger

    I've just resolv the problem ... I bad write the trigger.
    Elisa

Posting Permissions

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