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 > sequence in trigger

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 01-14-04, 14:39
incredible_ozzy incredible_ozzy is offline
Registered User
 
Join Date: Jan 2004
Posts: 8
Angry 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.
Reply With Quote
  #2 (permalink)  
Old 01-15-04, 06:02
andrewst andrewst is offline
Moderator.
 
Join Date: Sep 2002
Location: UK
Posts: 5,171
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?
__________________
Tony Andrews
http://tinyurl.com/tonyandrews
Reply With Quote
  #3 (permalink)  
Old 01-15-04, 06:16
incredible_ozzy incredible_ozzy is offline
Registered User
 
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.
Reply With Quote
  #4 (permalink)  
Old 01-15-04, 06:23
andrewst andrewst is offline
Moderator.
 
Join Date: Sep 2002
Location: UK
Posts: 5,171
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;
__________________
Tony Andrews
http://tinyurl.com/tonyandrews
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