SQL Trigger in Oracle before update or insert

Question

I created a compound trigger on before or insert.

There are many requirements and in one of them I am facing an issue: the part that there should be just one valid row for FEHLERMELDUNG at a time for the 5 columns A_MAT, P_MAT , P_SRC , BLUECAP and GRAYAWAY. Valid rows means, GUELTIG_BIS > SYSDATE and 1 in the 5 mentioned columns. the 1 could be in 1 or in all of them, doesn't matter. But it couldn't be 2 rows where one of the 5 columns has 1.

Here is the code:

CREATE OR REPLACE TRIGGER WARNMELDUNG_TR
FOR INSERT OR UPDATE ON patrik_warnmeldung
COMPOUND TRIGGER

  -- Variable to keep track of the highest sort value
  highest_sort NUMBER := 0;
  
  active_row_warnmeldung_a_mat NUMBER := 0;
  active_row_fehlermeldung_a_mat NUMBER := 0;
  
  active_row_warnmeldung_p_mat NUMBER := 0;
  active_row_fehlermeldung_p_mat NUMBER := 0;
  
  active_row_warnmeldung_p_src NUMBER := 0;
  active_row_fehlermeldung_p_src NUMBER := 0;
  
  active_row_warnmeldung_bluecap NUMBER := 0;
  active_row_fehlermeldung_bluecap NUMBER := 0;
  
  active_row_warnmeldung_grayway NUMBER := 0;
  active_row_fehlermeldung_grayaway NUMBER := 0;

  BEFORE STATEMENT IS
  BEGIN
    -- Initialize the highest sort value by querying the table
    SELECT NVL(MAX(SORT), 0) INTO highest_sort FROM kbs2.patrik_warnmeldung;
    
    
    -- Initialize the highest sort value by querying the table
    SELECT count(*) INTO active_row_warnmeldung_a_mat  FROM kbs2.patrik_warnmeldung WHERE GUELTIG_BIS > SYSDATE AND FEHLERTYP = 'WARNMELDUNG'  AND A_MAT = 1;
    SELECT count(*) INTO active_row_fehlermeldung_a_mat  FROM kbs2.patrik_warnmeldung WHERE GUELTIG_BIS > SYSDATE AND FEHLERTYP = 'FEHLERMELDUNG'  AND A_MAT = 1;
    
    
    
    SELECT count(*) INTO active_row_warnmeldung_p_mat  FROM kbs2.patrik_warnmeldung WHERE GUELTIG_BIS > SYSDATE AND FEHLERTYP = 'WARNMELDUNG'  AND P_MAT = 1;
    SELECT count(*) INTO active_row_fehlermeldung_p_mat  FROM kbs2.patrik_warnmeldung WHERE GUELTIG_BIS > SYSDATE AND FEHLERTYP = 'FEHLERMELDUNG'  AND P_MAT = 1;
    
    
    SELECT count(*) INTO active_row_warnmeldung_p_src  FROM kbs2.patrik_warnmeldung WHERE GUELTIG_BIS > SYSDATE AND FEHLERTYP = 'WARNMELDUNG'  AND P_SRC = 1;
    SELECT count(*) INTO active_row_fehlermeldung_p_src  FROM kbs2.patrik_warnmeldung WHERE GUELTIG_BIS > SYSDATE AND FEHLERTYP = 'FEHLERMELDUNG'  AND P_SRC = 1;
    
    
    SELECT count(*) INTO active_row_warnmeldung_bluecap  FROM kbs2.patrik_warnmeldung WHERE GUELTIG_BIS > SYSDATE AND FEHLERTYP = 'WARNMELDUNG'  AND BLUECAP = 1;
    SELECT count(*) INTO active_row_fehlermeldung_bluecap  FROM kbs2.patrik_warnmeldung WHERE GUELTIG_BIS > SYSDATE AND FEHLERTYP = 'FEHLERMELDUNG'  AND BLUECAP = 1;
    
    
    SELECT count(*) INTO active_row_warnmeldung_grayway  FROM kbs2.patrik_warnmeldung WHERE GUELTIG_BIS > SYSDATE AND FEHLERTYP = 'WARNMELDUNG'  AND GRAYAWAY = 1;
    SELECT count(*) INTO active_row_fehlermeldung_grayaway  FROM kbs2.patrik_warnmeldung WHERE GUELTIG_BIS > SYSDATE AND FEHLERTYP = 'FEHLERMELDUNG'  AND GRAYAWAY = 1;
    
  END BEFORE STATEMENT;

  BEFORE EACH ROW IS
  BEGIN
    -- Check if the user is provided; if not, raise an exception
    IF :NEW.user IS NULL THEN
      RAISE_APPLICATION_ERROR(-20001, 'Insert your user id');
    END IF;

    -- Check if active_till is before activ_since; if so, raise an exception
    IF :NEW.GUELTIG_VON IS NOT NULL AND :NEW.GUELTIG_BIS IS NOT NULL AND :NEW.GUELTIG_VON > :NEW.GUELTIG_BIS THEN
      RAISE_APPLICATION_ERROR(-20002, 'active_since cannot be after active_till');
    END IF;

    -- Set default values for activ_since and active_till if not provided
    IF :NEW.GUELTIG_VON IS NULL THEN
      :NEW.GUELTIG_VON := SYSDATE;
    END IF;

    IF :NEW.GUELTIG_BIS IS NULL THEN
      :NEW.GUELTIG_BIS := TO_DATE('31-12-9999', 'DD-MM-YYYY');
    END IF;

    -- Check for active row with active_till > SYSDATE, type 'WARNMELDUNG' or 'FEHLERMELDUNG', and a_mat = 1
 
    IF :NEW.A_MAT = 1 AND :NEW.FEHLERTYP = 'WARNMELDUNG' AND active_row_warnmeldung_a_mat <> 0 THEN

        RAISE_APPLICATION_ERROR(-20003, 'There is already an active row for a_mat and WARNMELDUNG, please either set active_till as today-1 or a_mat as 0');
    END IF;
    IF :NEW.A_MAT = 1 AND :NEW.FEHLERTYP = 'FEHLERMELDUNG' AND active_row_fehlermeldung_a_mat <> 0 THEN

        RAISE_APPLICATION_ERROR(-20004, 'There is already an active row for a_mat and FEHLERMELDUNG, please either set active_till as today-1 or a_mat as 0');
    END IF;
    
    
        -- Check for active row with active_till > SYSDATE, type 'WARNMELDUNG' or 'FEHLERMELDUNG', and p_mat = 1
    IF :NEW.P_MAT = 1 AND :NEW.FEHLERTYP = 'WARNMELDUNG' AND active_row_warnmeldung_p_mat <> 0 THEN

        RAISE_APPLICATION_ERROR(-20005, 'There is already an active row for p_mat and WARNMELDUNG, please either set active_till as today-1 or p_mat as 0');
    END IF;
    IF :NEW.P_MAT = 1 AND :NEW.FEHLERTYP = 'FEHLERMELDUNG' AND active_row_fehlermeldung_p_mat <> 0 THEN

        RAISE_APPLICATION_ERROR(-20006, 'There is already an active row for p_mat and FEHLERMELDUNG, please either set active_till as today-1 or p_mat as 0');
    END IF;
    
    

    -- Set sort value to the highest sort value + 1 if not provided
    IF :NEW.SORT IS NULL THEN
      highest_sort := highest_sort + 1;
      :NEW.SORT := highest_sort;
    END IF;

  END BEFORE EACH ROW;

END WARNMELDUNG_TR;
/

There was just one row but 0 or nulls in the columns, so I was able to insert a row for A_MAT = 1 cause it doesn't violate the condition one row for FEHLERMELDUNG for A_MAT = 1 if I try to update the first row as A_MAT = 1 then trigger fires the error which is fine. The issue is if I try to update the second row with P_MAT = 1 then I also get an error.

Why is that? My expectation is, that the :NEW.A_MAT bind variable is not holding an 1 if I am updating the column P_MAT = 1 . The ORA code is -20004, meaning, there is an active row for a_mat.

here DDL and DML with comments

 CREATE TABLE PATRIK_WARNMELDUNG 
   (    "FEHLERTYP" VARCHAR2(4000 BYTE), 
   "TEXT_DE" VARCHAR2(4000 BYTE), 
    "USER" VARCHAR2(4000 BYTE), 
    "GUELTIG_VON" DATE, 
    "GUELTIG_BIS" DATE, 
    "A_MAT" VARCHAR2(5 BYTE), 
    "P_MAT" VARCHAR2(5 BYTE), 
    "P_SRC" VARCHAR2(5 BYTE), 
    "BLUECAP" VARCHAR2(5 BYTE), 
    "GRAYAWAY" VARCHAR2(5 BYTE), 
    "SORT" NUMBER(*,0)
   )
   ;
   
insert into   PATRIK_WARNMELDUNG ("FEHLERTYP","TEXT_DE","USER","A_MAT") values ('FEHLERMELDUNG','text1','user1','1') ; -- will be inserted

insert into   PATRIK_WARNMELDUNG ("FEHLERTYP","TEXT_DE","USER","A_MAT") values ('FEHLERMELDUNG','text2','user2','1') ; -- will not be insertet because there is a row with values FEHLERMELDUNG and A_MAT = 1

UPDATE PATRIK_WARNMELDUNG SET P_MAT = '1' WHERE  TEXT_DE = 'text1'; -- there is no row with values FEHLERMELDUNG and P_MAT = 1 but the trigger doesn;t allow it cause it sees that A_MAT = 1 has already 1 row with value FEHLERMELDUNG. The expectation is that the columns A_MAT or P_MAT etc could have max 1 row, doesn't matter if the same one or different one.

insert into   PATRIK_WARNMELDUNG ("FEHLERTYP","TEXT_DE","USER","P_MAT") values ('FEHLERMELDUNG','text2','user3','1') ; -- this insert is working for example cause it create a new row but I need to be able update the same with 1 in other columns A_MAT,P_SRC,BLUECAP or GRAYAWAY if it doesn't violate the requiremtns that max 1 rows with FEHLERMELDUNG and 1 in the mentioned columns

Answer 1

The logic in your current trigger won't work. To update column p_mat to 1, a check needs to be done to see if that value doesn't already exist in another row and that info is not available in any of the variables initialized in the BEFORE STATEMENT section. Much simpler to move the checks from the BEFORE EACH ROW to an AFTER STATEMENT section and not use the BEFORE STATEMENT at all:

create or replace TRIGGER WARNMELDUNG_TR
FOR INSERT OR UPDATE ON patrik_warnmeldung
COMPOUND TRIGGER

  -- Variable to keep track of the highest sort value
  highest_sort NUMBER := 0;

  active_row_warnmeldung_a_mat NUMBER := 0;
  active_row_fehlermeldung_a_mat NUMBER := 0;

  active_row_warnmeldung_p_mat NUMBER := 0;
  active_row_fehlermeldung_p_mat NUMBER := 0;

  active_row_warnmeldung_p_src NUMBER := 0;
  active_row_fehlermeldung_p_src NUMBER := 0;

  active_row_warnmeldung_bluecap NUMBER := 0;
  active_row_fehlermeldung_bluecap NUMBER := 0;

  active_row_warnmeldung_grayway NUMBER := 0;
  active_row_fehlermeldung_grayaway NUMBER := 0;

  BEFORE EACH ROW IS
  BEGIN
    -- Check if the user is provided; if not, raise an exception
    IF :NEW.user IS NULL THEN
      RAISE_APPLICATION_ERROR(-20001, 'Insert your user id');
    END IF;

    -- Check if active_till is before activ_since; if so, raise an exception
    IF :NEW.GUELTIG_VON IS NOT NULL AND :NEW.GUELTIG_BIS IS NOT NULL AND :NEW.GUELTIG_VON > :NEW.GUELTIG_BIS THEN
      RAISE_APPLICATION_ERROR(-20002, 'active_since cannot be after active_till');
    END IF;

    -- Set default values for activ_since and active_till if not provided
    IF :NEW.GUELTIG_VON IS NULL THEN
      :NEW.GUELTIG_VON := SYSDATE;
    END IF;

    IF :NEW.GUELTIG_BIS IS NULL THEN
      :NEW.GUELTIG_BIS := TO_DATE('31-12-9999', 'DD-MM-YYYY');
    END IF;

    -- Set sort value to the highest sort value + 1 if not provided
    IF :NEW.SORT IS NULL THEN
      highest_sort := highest_sort + 1;
      :NEW.SORT := highest_sort;
    END IF;

  END BEFORE EACH ROW;

  AFTER STATEMENT IS
  BEGIN
    -- Initialize the highest sort value by querying the table
    SELECT NVL(MAX(SORT), 0) INTO highest_sort FROM patrik_warnmeldung;


    -- Initialize the highest sort value by querying the table
    SELECT count(*) INTO active_row_warnmeldung_a_mat  FROM patrik_warnmeldung WHERE GUELTIG_BIS > SYSDATE AND FEHLERTYP = 'WARNMELDUNG'  AND A_MAT = 1;
    SELECT count(*) INTO active_row_fehlermeldung_a_mat  FROM patrik_warnmeldung WHERE GUELTIG_BIS > SYSDATE AND FEHLERTYP = 'FEHLERMELDUNG'  AND A_MAT = 1;

    SELECT count(*) INTO active_row_warnmeldung_p_mat  FROM patrik_warnmeldung WHERE GUELTIG_BIS > SYSDATE AND FEHLERTYP = 'WARNMELDUNG'  AND P_MAT = 1;
    SELECT count(*) INTO active_row_fehlermeldung_p_mat  FROM patrik_warnmeldung WHERE GUELTIG_BIS > SYSDATE AND FEHLERTYP = 'FEHLERMELDUNG'  AND P_MAT = 1;

    SELECT count(*) INTO active_row_warnmeldung_p_src  FROM patrik_warnmeldung WHERE GUELTIG_BIS > SYSDATE AND FEHLERTYP = 'WARNMELDUNG'  AND P_SRC = 1;
    SELECT count(*) INTO active_row_fehlermeldung_p_src  FROM patrik_warnmeldung WHERE GUELTIG_BIS > SYSDATE AND FEHLERTYP = 'FEHLERMELDUNG'  AND P_SRC = 1;

    SELECT count(*) INTO active_row_warnmeldung_bluecap  FROM patrik_warnmeldung WHERE GUELTIG_BIS > SYSDATE AND FEHLERTYP = 'WARNMELDUNG'  AND BLUECAP = 1;
    SELECT count(*) INTO active_row_fehlermeldung_bluecap  FROM patrik_warnmeldung WHERE GUELTIG_BIS > SYSDATE AND FEHLERTYP = 'FEHLERMELDUNG'  AND BLUECAP = 1;

    SELECT count(*) INTO active_row_warnmeldung_grayway  FROM patrik_warnmeldung WHERE GUELTIG_BIS > SYSDATE AND FEHLERTYP = 'WARNMELDUNG'  AND GRAYAWAY = 1;
    SELECT count(*) INTO active_row_fehlermeldung_grayaway  FROM patrik_warnmeldung WHERE GUELTIG_BIS > SYSDATE AND FEHLERTYP = 'FEHLERMELDUNG'  AND GRAYAWAY = 1;

    IF  active_row_warnmeldung_a_mat > 1 THEN
        RAISE_APPLICATION_ERROR(-20003, 'There is already an active row for a_mat and WARNMELDUNG, please either set active_till as today-1 or a_mat as 0');
    END IF;
    IF  active_row_fehlermeldung_a_mat > 1 THEN
        RAISE_APPLICATION_ERROR(-20004, 'There is already an active row for a_mat and FEHLERMELDUNG, please either set active_till as today-1 or a_mat as 0');
    END IF;
    IF  active_row_warnmeldung_p_mat > 1 THEN
        RAISE_APPLICATION_ERROR(-20005, 'There is already an active row for p_mat and WARNMELDUNG, please either set active_till as today-1 or p_mat as 0');
    END IF;
    IF  active_row_fehlermeldung_p_mat > 1 THEN
        RAISE_APPLICATION_ERROR(-20006, 'There is already an active row for p_mat and FEHLERMELDUNG, please either set active_till as today-1 or p_mat as 0');
    END IF;

  END AFTER STATEMENT;


END WARNMELDUNG_TR;