Trigger: Only raise error if related record was NOT created between now and last Sunday I have a trigger that prevents inserts into a DELETES table under certain conditions:
CREATE OR REPLACE TRIGGER D202574_custom
BEFORE INSERT ON D202574
FOR EACH ROW
DECLARE 
    v_upd_row NUMBER;
BEGIN
    SELECT COUNT(1) INTO v_upd_row 
    FROM A202574
    WHERE objectid = :new.sde_deletes_row_id
    AND sde_state_id = :new.deleted_at;

    IF v_upd_row = 0 THEN
         raise_application_error(-20001, 'Deleting has been disabled via a custom trigger.');
    END IF;
END ;


I want to enhance the trigger so that it only raises an error if the related record in the base table was NOT created between now and last Sunday at 11:59 PM.

The relationship would be based on the OBJECTID column.

This is what the base table looks like:
CREATE TABLE A_TEST_BASE_TABLE
(
  OBJECTID          INTEGER NOT NULL,
  SHAPE             SDE.ST_GEOMETRY,
  CREATED_USER      NVARCHAR2(255),
  CREATED_DATE      TIMESTAMP(6),   --&lt;&lt;-- the relevant field
  LAST_EDITED_USER  NVARCHAR2(255),
  LAST_EDITED_DATE  TIMESTAMP(6)
)

What would it take to do this?
If I understood you correctly, then see if this helps:

select created_date from that table
check if it is lower than previous Sunday's 23:59

why? Because I presume that it can't be created in the future



How to find that moment in time? One option is to use NEXT_DAY function which returns the &quot;next&quot; Sunday, but - if you subtract 7 (days) from it, you ge previous Sunday which is what you want. Then add 23:59 to the truncated date value and that's it.
Words into code:
SQL&gt; select to_char(sysdate, 'dd.mm.yyyy hh24:mi, day') right_now,
  2    trunc(next_day(sysdate, 'sunday') - 7) + 23/24 + 59/(24*60) previous_sunday_2359
  3  from dual;

RIGHT_NOW                                              PREVIOUS_SUNDAY_235
------------------------------------------------------ -------------------
29.08.2020 22:39, saturday                             23.08.2020 23:59:00

SQL&gt;

Put into the trigger:
CREATE OR REPLACE TRIGGER D202574_custom
BEFORE INSERT ON D202574
FOR EACH ROW
DECLARE 
    v_upd_row  NUMBER;
    v_cre_date date;
BEGIN
    SELECT COUNT(1) INTO v_upd_row 
    FROM A202574
    WHERE objectid = :new.sde_deletes_row_id
      AND sde_state_id = :new.deleted_at;
      
    SELECT MIN(a.created_date) INTO v_cre_date
    FROM a_test_base_table a
    WHERE a.objectid = :new.sde_deletes_row_id;
      
    IF v_upd_row = 0 AND
       v_cre_date &lt; trunc(next_day(sysdate, 'sunday') - 7) + 23/24 + 59/(24*60)
    THEN
       raise_application_error(-20001, 'Deleting has been disabled via a custom trigger.');
    END IF;
END;