Home » SQL & PL/SQL » SQL & PL/SQL » trigger problem
trigger problem [message #405110] Tue, 26 May 2009 07:07 Go to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12403
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 #405126 is a reply to message #405121] Tue, 26 May 2009 07:39 Go to previous messageGo to next message
Roger22
Messages: 98
Registered: April 2009
Location: Brasov, ROMANIA
Member
please help me, i don't know how to rewrite using for loop..

Regards,
Re: trigger problem [message #405127 is a reply to message #405126] Tue, 26 May 2009 07:43 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Search....

By
Vamsi
Re: trigger problem [message #405131 is a reply to message #405110] Tue, 26 May 2009 08:10 Go to previous messageGo to next message
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;
Re: trigger problem [message #405302 is a reply to message #405131] Wed, 27 May 2009 05:58 Go to previous message
Roger22
Messages: 98
Registered: April 2009
Location: Brasov, ROMANIA
Member
Thank you, it helped me Smile
Previous Topic: begin end under exception
Next Topic: Can't qrouping or sorting in monthwise (merged 2)
Goto Forum:
  


Current Time: Sun Dec 04 00:03:14 CST 2016

Total time taken to generate the page: 0.08639 seconds