How to schedule a trigger which will run automatically in sql developer that clean up 30 days older data from a table

Question

I want to run a scheduler which runs a trigger such that it should automatically wipe out the data which is more than 30 days old. 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.

I have created the below trigger 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 is something which went wrong with the above trigger because now I am getting the following error:

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

What is 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?

Does this answer your question? [Scheduling a sql script from oracle sql developer](https://stackoverflow.com/questions/44428759/scheduling-a-sql-script-from-oracle-sql-developer) — Azhar Khan, Oct 21 '22 at 11:14
Triggers cannot be scheduled, they are fired when some database event occurs. You need to use [DBMS_SCHEDULER](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_SCHEDULER.html#GUID-7E744D62-13F6-40E9-91F0-1569E6C38BBC). — astentx, Oct 21 '22 at 11:44
I would say if you really want to delete the data on monthly/daily basis, it would be **much** better to use appropriate partitioning (month/day) and truncate/drop partitions. No one would care to cleanup 1-100k rows, but on a large dataset `delete` will be very slow. — astentx, Oct 21 '22 at 12:08

score 2 · Answer 1 · edited Jan 13 '23 at 03:16

As commented, a trigger is not an appropriate tool for that. Convert it to a stored procedure (using Oracle's syntax; I 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

A procedure which deletes rows older than one 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.

Check the 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

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

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.

so i am using oracle database and as you suggested i will use store procedure and that SP will run based on the Scheduler right and yeah i will use one months , so is this is sufficient to create this automated task as i just want to wipe out all the data from the specific table once its reaches 30 days i mean here no manual intervention is required right to call the SP again and Job scheduler would do the task for us automatically — Girija Dash, Oct 21 '22 at 13:29

score 0 · Answer 2 · answered Oct 21 '22 at 13:25

0

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

answered Oct 21 '22 at 13:25

sankar

189
1
10

score 0 · Answer 3 · edited Jan 13 '23 at 02:51

You are using T-SQL syntax (used by SQL Server) which will not work for Oracle.

If you are using an Oracle database then use the Oracle syntax for creating a trigger which is:

create trigger <trigername> [BEFORE/AFTER/INSTEAD OF]  [INSERT/UPDATE/DELETE] on <table_name>

Triggers in Oracle can be set up for INSERT, UPDATE , DELETE or on Database operations like LOGON, LOGOFF, etc. Having said that, triggers will not help you here as others pointed out.

In your case, you need to truncate a table based on some column value, so the best option for you would be to setup a DBMS scheduler job to run a SQL script/PL/SQL stored procedure and purge older data in your table.

How to schedule a trigger which will run automatically in sql developer that clean up 30 days older data from a table

3 Answers3