TRIGER

--  SELECT DISTINCT
--    SKUNDENGRP.ID AS GroupID,
--    SKUNDENGRP.NR AS GroupNR,
--    SKUNDENGRP.BEZEICHNUNG AS GroupName,
--    ABPGRPCUSTOMFIELDV.CFBOOLEAN4 AS Web_AccessToSite,
--    ABPGRPCUSTOMFIELDV.CFBOOLEAN5 AS Web_Customers,
--    ABPGRPCUSTOMFIELDV.CFBOOLEAN6 AS Web_Carriers,
--    ABPGRPCUSTOMFIELDV.CFBOOLEAN7 AS Web_Carriers_Transports_View,
--    ABPGRPCUSTOMFIELDV.CFBOOLEAN8 AS Web_Carriers_Invoices_View,
--    ABPGRPCUSTOMFIELDV.CFSTRING1  AS Web_AccKey,
--    ABPGRPCUSTOMFIELDV.CFBOOLEAN9 AS Web_Carriers_Stock_Access,
--    (
--        SELECT LISTAGG(t.n, ',') WITHIN GROUP (ORDER BY t.n)
--        FROM (
--            SELECT SKUNDEN.NR AS n
--            FROM SKUNDEN
--            LEFT JOIN SKUNDENGRPZUORD b ON b.KUNDENNR = SKUNDEN.NR
--            WHERE b.GRUPPENID = SKUNDENGRP.ID
--        ) t
--    ) AS bplist,
--    SUBSTR(STANDARD_HASH(ABPGRPCUSTOMFIELDV.CFSTRING1, 'SHA256'), 1, 8) AS Generated_Password
--FROM SKUNDENGRP
--LEFT JOIN SKUNDENGRPZUORD ON SKUNDENGRP.ID = SKUNDENGRPZUORD.GRUPPENID
--LEFT JOIN ABPGRPCUSTOMFIELDV ON SKUNDENGRP.ID = ABPGRPCUSTOMFIELDV.ID
--WHERE
--    ABPGRPCUSTOMFIELDV.CFSTRING1 IS NOT NULL;
--
--
--  GRANT SELECT ON "SOL"."UDX_VIEW_WEB_USERS_ACCESS" TO "ALEXORAWEB";
--
--
--BEGIN
--    UPDATE ABPGRPCUSTOMFIELDV
--    SET CFSTRING3 = USUBSTR(STANDARD_HASH(ABPGRPCUSTOMFIELDV.CFSTRING1, 'SHA256'), 1, 8)
--    WHERE CFSTRING1 IS NOT NULL
--      AND CFSTRING3 IS NULL;
--
--    COMMIT;
--END;
--
--
--UPDATE ABPGRPCUSTOMFIELDV
--SET CFSTRING3 = SUBSTR(STANDARD_HASH(ABPGRPCUSTOMFIELDV.CFSTRING1, 'SHA256'), 1, 8)
--WHERE CFSTRING1 IS NOT NULL
--AND CFSTRING3 IS NULL;
--
--SELECT * FROM ABPGRPCUSTOMFIELDV;
--
--
--
--BEGIN
--   DBMS_SCHEDULER.create_job (
--      job_name        => 'update_cfstring3_job',
--      job_type        => 'PLSQL_BLOCK',
--      job_action      => 'UPDATE ABPGRPCUSTOMFIELDV
--                            SET CFSTRING3 = SUBSTR(STANDARD_HASH(CFSTRING1, ''SHA256''), 1, 8)
--                            WHERE CFSTRING1 IS NOT NULL
--                            AND CFSTRING3 IS NULL;',
--      repeat_interval => 'FREQ=HOURLY; INTERVAL=2',
--      enabled         => TRUE
--   );
--END;
--
--UPDATE ABPGRPCUSTOMFIELDV
--SET CFSTRING3 = null
--where CFSTRING1 = 'cones826@gmail.com';
--
--BEGIN
--   DBMS_SCHEDULER.create_job (
--      job_name        => 'update_cfstring3_job',
--      job_type        => 'PLSQL_BLOCK',
--      job_action      => 'UPDATE ABPGRPCUSTOMFIELDV
--                            SET CFSTRING3 = SUBSTR(STANDARD_HASH(CFSTRING1, ''SHA256''), 1, 8)
--                            WHERE CFSTRING1 IS NOT NULL
--                            AND CFSTRING3 IS NULL;',
--      repeat_interval => 'FREQ=HOURLY; INTERVAL=2',
--      enabled         => TRUE
--   );
--END;
--/

CREATE OR REPLACE TRIGGER UDX_PASSWORD_INSERT
AFTER INSERT OR UPDATE ON ABPGRPCUSTOMFIELDV
FOR EACH ROW
DECLARE
   v_upd_done BOOLEAN := FALSE;  -- Флаг за проверка дали актуализацията е извършена
BEGIN
   -- Ако CFSTRING3 е NULL и още не е актуализиран
   IF :NEW.CFSTRING3 IS NULL AND v_upd_done = FALSE THEN
      -- Актуализираме само ако CFSTRING3 все още е NULL
      UPDATE ABPGRPCUSTOMFIELDV
      SET CFSTRING3 = SUBSTR(STANDARD_HASH(:NEW.CFSTRING1, 'SHA256'), 1, 8)
      WHERE CFSTRING1 = :NEW.CFSTRING1
      AND CFSTRING3 IS NULL;

      v_upd_done := TRUE;  -- Задаваме флага на TRUE, за да предотвратим рекурсия
   END IF;
END;
/

CREATE OR REPLACE TRIGGER UDX_PASSWORD_INSERT
AFTER INSERT OR UPDATE ON ABPGRPCUSTOMFIELDV
FOR EACH ROW
DECLARE
   v_upd_done BOOLEAN := FALSE;
BEGIN

   IF :NEW.CFSTRING3 IS NULL AND v_upd_done = FALSE THEN
      UPDATE ABPGRPCUSTOMFIELDV
      SET CFSTRING3 = SUBSTR(STANDARD_HASH(:NEW.CFSTRING1, 'SHA256'), 1, 8)
      WHERE CFSTRING1 = :NEW.CFSTRING1
      AND CFSTRING3 IS NULL;

      v_upd_done := TRUE;
   END IF;
END;
/