trigger problem [message #405110] |
Tue, 26 May 2009 07:07  |
Roger22
Messages: 98 Registered: April 2009 Location: Brasov, ROMANIA
|
Member |
|
|
Hello,
i have a trigger
create or replace trigger bef_insert_clasaprofil_inv
before insert or update on clasa_profil
for each row
declare
clasa_id clasa.id%type;
diriginte_id angajati.id%type;
sub_result integer;
begin
select id
into clasa_id
from clasa
where id = :new.id_clasa
and to_number (substr (cls, 1, instr (cls, ' ') - 1)) < 5;
declare cursor crInv is
select id from angajati where id in(select id_angajat from angajati_functii where id_functie in (select id from functii where denumire='Invatator' and data_stop is null));
begin
open crInv;
fetch crInv into diriginte_id;
close crInv;
if (:new.id_clasa=clasa_id) and (:new.id_diriginte=diriginte_id) then
raise_application_error(-20105, 'Nu puteti asigna decat invatatori claselor primare!');
end if;
end;
end;
that select returns 2 records (2 id-s, 6 and 8). how can i raise an error if :new.id_diriginte <> with one of the returned items?
Something goes wrong on my trigger, it works only for the first returned id. (6)
Regards,
[EDITED by LF: disabled smilies in this message]
[Updated on: Tue, 26 May 2009 07:42] by Moderator Report message to a moderator
|
|
|
Re: trigger problem [message #405121 is a reply to message #405110] |
Tue, 26 May 2009 07:29   |
cookiemonster
Messages: 13963 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Fetch only fetches one row at a time.
So to check multiple rows you'd have to fetch multiple times.
Suggest you look up FOR LOOPs
Alternatively you could just move the checks from your IF statement into the where clause of the cursor. That way, if it finds anything you know you need to raise an error.
|
|
|
|
|
Re: trigger problem [message #405131 is a reply to message #405110] |
Tue, 26 May 2009 08:10   |
ora_2007
Messages: 430 Registered: July 2007 Location: Mumbai
|
Senior Member |
|
|
below code will help you.
***not tested***
CREATE OR REPLACE TRIGGER bef_insert_clasaprofil_inv
BEFORE INSERT OR UPDATE
ON clasa_profil
FOR EACH ROW
DECLARE
clasa_id clasa.ID%TYPE;
diriginte_id angajati.ID%TYPE;
sub_result INTEGER;
CURSOR crinv
IS
SELECT ID
FROM angajati
WHERE ID IN (SELECT id_angajat
FROM angajati_functii
WHERE id_functie IN (SELECT ID
FROM functii
WHERE denumire = 'Invatator'
AND data_stop IS NULL));
BEGIN
SELECT ID
INTO clasa_id
FROM clasa
WHERE ID = :NEW.id_clasa
AND TO_NUMBER (SUBSTR (cls, 1, INSTR (cls, ' ') - 1)) < 5;
FOR i IN crinv
LOOP
IF (:NEW.id_clasa = clasa_id) AND (:NEW.id_diriginte = i.angajati)
THEN
RAISE_APPLICATION_ERROR(-20105,'Nu puteti asigna decat invatatori claselor primare!');
END IF;
END LOOP;
END;
|
|
|
|