CREATE OR REPLACE TRIGGER "CREATE_DT472" INSTEAD OF
UPDATE ON "V_DT430_DT472" FOR EACH ROW

BEGIN
-- update:
  UPDATE DT472 SET
    DI001510 = :NEW.BEL_IST,
    DI001501 = :NEW.DATE_MODIFY,
    di001504 = :NEW.user_modify
  WHERE DI001517 = :NEW.RESS
    AND DI001395 = :NEW.PR
    AND DI001516 = :NEW.VG
    AND DI001519 = :NEW.DATUM_KAL
    AND (
    -- if there is exactly one row with a load for that day;
    (ABS(COALESCE(DI001510,0)) > 0.0009
    AND (
      SELECT COUNT(*) FROM DT472
      WHERE DI001395 = :NEW.PR
        AND DI001516   = :NEW.VG
        AND DI001517   = :NEW.RESS
        AND DI001519   = :NEW.DATUM_KAL
        AND ABS(COALESCE(DI001510,0)) > 0.0009
      ) = 1)
    OR (
    -- or if there is no row with a load for that day,
      SELECT COUNT(*) FROM DT472 WHERE DI001395 = :NEW.PR
        AND DI001516   = :NEW.VG
        AND DI001517   = :NEW.RESS
        AND DI001519   = :NEW.DATUM_KAL
        AND COALESCE(di001510,0) > 0.0009
      ) = 0 AND (
    -- and there is at least one row with no load for that day,
    -- take the row with the highest pos. no.
      SELECT COALESCE(MAX(DI001400),0) FROM DT472 WHERE DI001395 = :NEW.PR
        AND DI001516   = :NEW.VG
        AND DI001517   = :NEW.RESS
        AND DI001519   = :NEW.DATUM_KAL
        AND COALESCE(di001510,0) = 0
      ) = DI001400)
  ;
  -- if there exists more than one row with a load, do nothing;
  -- user has to distribute load to the data sets on his/her own.

-- finally, if there are no rows with a load yet, insert one, if BEL_IST <> 0:
  INSERT INTO DT472
    (
      DI001395,
      DI001516,
      DI001517,
      DI001518,
      DI001400,
      DI001519,
      DI001510,
      DI001438,
      DI001445,
      DI007783,
      DI001500,
      DI001501,
      DI001502,
      DI001503,
      DI001504,
      DI001505,
      DI000602,
      DI001506,
      DI001507,
      DI001508
    )
  SELECT
    :NEW.PR,
    :NEW.VG,
    :NEW.RESS,
    :NEW.KoA,
    get_new_auto_id('001400'),
    :NEW.DATUM_KAL,
    :NEW.BEL_Ist,
    '',
    :NEW.OE_Ress,
    :NEW.OE_Ress,
    :NEW.date_modify, -- modify date is create date for load record:
    :NEW.date_modify,
    :NEW.rwdu,
    :NEW.user_modify, -- modify user is create user for load record:
    :NEW.user_modify,
    :NEW.owner_license,
    :NEW.osc,
    :NEW.owner,
    :NEW."group",
    :NEW."any"
  FROM dual
  -- only if load > 0
  WHERE COALESCE(:NEW.BEL_IST,0) != 0 
    AND (
    -- and only if no row with a load for that day already exists:
      SELECT COUNT(*) FROM DT472 WHERE DI001395 = :NEW.PR
        AND DI001516   = :NEW.VG
        AND DI001517   = :NEW.RESS
        AND DI001519   = :NEW.DATUM_KAL
        AND ABS(COALESCE(DI001510,0)) > 0.0009
      ) = 0
  ;
END;
;/
ALTER TRIGGER "CREATE_DT472" ENABLE;/
