SQL Trigger Statement ignored

Question

It should ensure that you can set only predefined values else it shows error. I get error

Error at line 6: PL/SQL: Statement ignored
4. FOR EACH ROW
5. DECLARE
6. v_stru VARCHAR2(50);

CREATE OR REPLACE TRIGGER radnici_strucna_sprema
 BEFORE INSERT OR UPDATE OF STRUCNA_SPREMA ON radnici
FOR EACH ROW
DECLARE
  v_stru VARCHAR2(50);
BEGIN
  v_stru := :NEW.strucna_sprema;
  IF v_ss = 'osnovno' THEN
    :NEW.strucna_sprema := v_stru;
  ELSIF v_ss = 'srednje' THEN
    :NEW.strucna_sprema := v_stru;
  ELSIF v_ss = 'vise' THEN
    :NEW.strucna_sprema := v_stru;
  ELSIF grade = 'visoko' THEN
    :NEW.strucna_sprema := v_stru;
  ELSE
    RAISE_APPLICATION_ERROR(NUM => -20002, 
            MSG => 'Forma strucne spreme nije odgovarajuca!');
  END IF;
END;

Which client are you seeing that error in? It looks like [this sort of issue with your client not handling PL/SQL properly](https://stackoverflow.com/a/37684613/266304), but maybe a different client... Also, not directly relevant, but where are `v_ss` and `grade` supposed to be coming from? — Alex Poole, May 27 '17 at 09:36

score 0 · Answer 1 · answered May 27 '17 at 17:55

try like this:

CREATE OR REPLACE TRIGGER radnici_strucna_sprema
 BEFORE INSERT OR UPDATE ON radnici
FOR EACH ROW
DECLARE
  v_stru VARCHAR2(50);
BEGIN
  v_stru := :NEW.strucna_sprema;
  IF v_ss = 'osnovno' THEN
    :NEW.strucna_sprema := v_stru;
  ELSIF v_ss = 'srednje' THEN
    :NEW.strucna_sprema := v_stru;
  ELSIF v_ss = 'vise' THEN
    :NEW.strucna_sprema := v_stru;
  ELSIF grade = 'visoko' THEN
    :NEW.strucna_sprema := v_stru;
  ELSE
    RAISE_APPLICATION_ERROR(NUM => -20002, 
            MSG => 'Forma strucne spreme nije odgovarajuca!');
  END IF;
END;

The problem is

OF STRUCNA_SPREMA.

This variable, STRUCNA_SPREMA, is a column in your table, radnici, or what is representing ? But to be honest, I do not understand your logic. You assign the value of the column in a variable and then assign the value of that variable in your column. Why ?! Moreover, be sure that STRUCNA_SPREMA is a varchar.

Kind regards, Stefan

The `OF STRUCNA_SPREMA` means the trigger only fires if that column is updated, so [that is valid](https://docs.oracle.com/database/121/LNPLS/create_trigger.htm#BABGDFBI) (even if the logic doesn't make much sense). This doesn't explain or avoid the error the OP is gettting. — Alex Poole, May 27 '17 at 22:19
The logic is: I have predefined values for strucna_sprema and it needs to check is the value one of the predefined and if so to set that column if not to send and error — Glusac Andjelika, May 28 '17 at 09:54

SQL Trigger Statement ignored

1 Answers1