Create Trigger in Oracle SQL Developer-CodePudding

I want to create a Trigger on the Oracle SQLDeveloper.

The trigger should only allow the 'grade' column within the 'exams' table to get altered afterwards, IF the data in the grade column is below 4.0 aka 5.0/6.0 (German Grading System).

IF the data in the grade column is at 1.0/ 2.0/ 3.0/4.0, the Trigger should deny an alteration.

So the Trigger should check if the grade is below or above 4.0 and based on that, either allow or deny an alteration to the existent table data.

My main problem is that I don't quite understand the Syntax of a Trigger Creation for Oracle SQL Developer. Creating Constraints seems easy but Triggers have me confused.

Solutions I am trying to get to work :

CREATE OR REPLACE TRIGGER KlausurWiederholung
BEFORE UPDATE OF Note ON Prüfen
FOR EACH ROW
BEGIN 
IF NEW.Note > OLD.Note THEN UPDATE Prüfen
END IF;
END

So if anyone could explain or link an useful example of how to correctly structure my Trigger, it would be much appreciated.

CodePudding user response：

You want to use :OLD and :NEW to refer to the row's records before and after the update and if the update is invalid then you want to raise an exception (and you need ; as a statement terminator for the final END and to terminate the PL/SQL block with / on a new line):

CREATE OR REPLACE TRIGGER KlausurWiederholung
BEFORE UPDATE OF Note ON Prüfen
FOR EACH ROW
BEGIN 
  IF :OLD.Note <= 4.0 THEN
    RAISE_APPLICATION_ERROR(-20000, 'Grade too high to update.');
  END IF;
END;
/

Then, for the sample data:

CREATE TABLE Prüfen (id, note) AS
SELECT  1, 1   FROM DUAL UNION ALL
SELECT  2, 1.2 FROM DUAL UNION ALL
SELECT  3, 2   FROM DUAL UNION ALL
SELECT  4, 2.5 FROM DUAL UNION ALL
SELECT  5, 3   FROM DUAL UNION ALL
SELECT  6, 4   FROM DUAL UNION ALL
SELECT  7, 4.4 FROM DUAL UNION ALL
SELECT  8, 5   FROM DUAL UNION ALL
SELECT  9, 5.7 FROM DUAL UNION ALL
SELECT 10, 6   FROM DUAL UNION ALL
SELECT 11, 6.3 FROM DUAL;

Then:

UPDATE Prüfen
SET   note = 1
WHERE id = 2;

Gives the exception:

ORA-20000: Grade too high to update.
ORA-06512: at "FIDDLE_DPBZKTZLAVYIZCPLKBSE.KLAUSURWIEDERHOLUNG", line 3
ORA-04088: error during execution of trigger 'FIDDLE_DPBZKTZLAVYIZCPLKBSE.KLAUSURWIEDERHOLUNG'

But:

UPDATE Prüfen
SET   note = note   4
WHERE note > 4;

Works.

fiddle