Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    8

    Angry Unanswered: sequence in trigger

    At the moment I've got this trigger:

    CREATE OR REPLACE TRIGGER v_med_trig
    INSTEAD OF INSERT OR UPDATE ON V_MEDEWERKER
    BEGIN
    INSERT INTO MEDEWERKER
    (medewerkernummer, functie, voornaammedewerker,
    achternaammedewerker)
    VALUES
    (:new.medewerkernummer, :new.functie, :new.voornaammedewerker,
    :new.achternaammedewerker);
    INSERT INTO MEDEWERKERTELNUMMER
    (medewerker, telefoonnummermedewerker)
    VALUES
    (:new.medewerkernummer, :new.telefoonnummermedewerker);
    END v_med_trig;

    Now it's my intention to build in some kind of sequence. Does anyone know how to do this. When I try myself oracle says it can't update this view.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: sequence in trigger

    I'm unable to understand what you are trying and what goes wrong exactly. Can you post the modified trigger code that is failing, and the error message?

  3. #3
    Join Date
    Jan 2004
    Posts
    8
    Oke, I'll try to explain my problem a little better. The trigger you see above is a trigger that makes it possible to update a view. If you look at the columnnames you see the name medewerkernummer.
    At the moment I have to give a number to every new record I make (I have to check if a number is used everytime). So, to solve this I made a new trigger:

    CREATE OR REPLACE TRIGGER NIEUWEMEDEWERKER
    BEFORE INSERT OR UPDATE ON MEDEWERKER
    FOR EACH ROW
    BEGIN
    SELECT sID.NEXTVAL INTO :NEW.MEDEWERKERNUMMER FROM DUAL;
    END NIEUWEMEDEWERKER;
    /

    But when I use this trigger the view doesn't seem to be updateble anymore. I've got the feeling that this sequence has to be build in the trigger I posted first in this thread but I don't have clue how to do this.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I'm not aware of such a restriction, but that could be the problem. Anyway, you can do the assignment in the original trigger like this:

    CREATE OR REPLACE TRIGGER v_med_trig
    INSTEAD OF INSERT OR UPDATE ON V_MEDEWERKER
    BEGIN
    INSERT INTO MEDEWERKER
    (medewerkernummer, functie, voornaammedewerker,
    achternaammedewerker)
    VALUES
    (sid.NEXTVAL, :new.functie, :new.voornaammedewerker,
    :new.achternaammedewerker);
    INSERT INTO MEDEWERKERTELNUMMER
    (medewerker, telefoonnummermedewerker)
    VALUES
    (:new.medewerkernummer, :new.telefoonnummermedewerker);
    END v_med_trig;

Posting Permissions

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