how to schedule a trigger which will run automatically in sql developer that clean up 30 days older-CodePudding

i want to run a scheduler which run a trigger that it should be automatically wipe out the data which is more than 30days.so we already have a trigger as below and we need something to schedule that trigger automatically once it reaches 30 days. and here the days should be configurable

The below trigger i have created for this

CREATE TRIGGER sampleTrigger
    ON database1.dbo.table1
    FOR DELETE
AS
    DELETE FROM database1.dbo.table1
    WHERE date < DATEADD(day, -30, GETDATE())
GO

and also here something went wrong with the above trigger because now i am getting following error

ORA-04071 :missing BEFORE,AFTER or INSTEAD OF.

can some one please help me on this what wrong with the above trigger and how can i make this automatic using a scheduler that will clean up 30 days older data from a table

CodePudding user response：

As commented, trigger is not appropriate tool for that - convert it to a stored procedure (using Oracle's syntax; can't tell which database code you posted belongs to) and schedule a monthly job which will then run that procedure.

Also, note that deleting "30 days" back might cause problems because not all days have 30 days; perhaps you'd rather use "1 month".

Sample table:

SQL>   SELECT *
  2      FROM test
  3  ORDER BY datum DESC;
    
        ID DATUM
---------- ----------
         4 15.10.2022
         3 28.09.2022
         2 15.08.2022   --> should be deleted today (21st of October 2022)
         1 30.07.2022   --> should be deleted

Procedure which deletes rows older than 1 month; if you really need 30 days, modify where clause to < trunc(sysdate) - 30:

SQL> CREATE OR REPLACE PROCEDURE p_del
  2  IS
  3  BEGIN
  4     DELETE FROM test
  5           WHERE datum < ADD_MONTHS (TRUNC (SYSDATE), -1);
  6  END;
  7  /

Procedure created.

Let's schedule a job:

SQL> BEGIN
  2     DBMS_SCHEDULER.CREATE_JOB (
  3        job_name         => 'p_delete_test',
  4        job_type         => 'PLSQL_BLOCK',
  5        job_action       => 'BEGIN p_del; end;',
  6        start_date       =>
  7           TO_TIMESTAMP_TZ ('21.10.2022 03:00 Europe/Zagreb',
  8                            'dd.mm.yyyy hh24:mi TZR'),
  9        repeat_interval  => 'FREQ=MONTHLY; BYHOUR=3; BYMINUTE=0',
 10        enabled          => TRUE,
 11        comments         => 'Deleting rows older than 30 days');
 12  END;
 13  /

PL/SQL procedure successfully completed.

SQL>

Check job info by querying user_scheduler_jobs, e.g.

SQL> SELECT TO_CHAR(last_start_date, 'dd.mm.yyyy hh24:mi:ss') last_start,
  2         TO_CHAR(next_run_date  , 'dd.mm.yyyy hh24:mi:ss') next_run_date
  3  FROM user_scheduler_jobs
  4  where job_name = 'P_DELETE_TEST';

LAST_START          NEXT_RUN_DATE
------------------- -------------------
                    21.11.2022 03:00:00

SQL>

We won't wait until next month, so I'll run it manually:

SQL> BEGIN
  2     DBMS_SCHEDULER.run_job ('p_delete_test');
  3  END;
  4  /

PL/SQL procedure successfully completed.

Result:

SQL>   SELECT *
  2      FROM test
  3  ORDER BY datum DESC;

        ID DATUM
---------- ----------
         4 15.10.2022
         3 28.09.2022

SQL>

If you don't need the job any more, delete it:

SQL> BEGIN
  2     DBMS_SCHEDULER.drop_job ('P_DELETE_TEST');
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL>

CodePudding user response：

Triggers are not meant for scheduling. Use DBMS_SCHEDULER for your requirement.