Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1663
  • Last Modified:

Empty History Table Every Six Months (Trigger) - PL/SQL

The trigger below should clear the empty table every six month. The trigger runs prior record gets inserted in the history tabel. I get the following error:

                                                                   
            := . ( @ % ;                                                  
         The symbol ":=" was substituted for "RECYCLEBIN" to continue.    
                                                                         

Any idea what is the cause of this error?
CREATE OR REPLACE TRIGGER clear_cushistory
BEFORE INSERT ON CUSTOMERHISTORY
DECLARE
 	 v_currentDate	 TIMESTAMP;
	 v_comingDate	 TIMESTAMP;
	 v_period		 NUMBER := 6;
	 v_xxx 			 NUMBER;
	 
BEGIN
	 v_currentDate := SYSDATE;
	 v_comingDate := SYSDATE + 6;

	 
	 v_monthspassed := TO_DATE(comingDate, 'MM') - TO_DATE(v_currentDate, 'MM');
	 
	 IF v_monthspassed = v_period  THEN
		DELETE FROM CUSTOMERHISTORY;
	 END IF;
	 PURGE RECYCLEBIN;
	 
END;
/

Open in new window

0
F-J-K
Asked:
F-J-K
  • 5
  • 5
  • 5
  • +1
8 Solutions
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
This command cannot be used in a trigger as this is a not a DML command.

PURGE RECYCLEBIN;  --> This command cannot be rolledback once it is done.

I guess you know that you cannot put "CREATE TABLE","ALTER TABLE","DROP TABLE" etc.. ( all DDL ) commands in triggers code. Similarly "PURGE" as well.

Can you comment that line and see whether your code works fine.

Also there are other mistakes/corrections required in your code...

1)  v_comingDate := SYSDATE + 6;  --> this will just add 6 days from current date and not six months. If you want to add months, you have to use ADD_MONTHS(..) function.
2) v_monthspassed := TO_DATE(comingDate, 'MM') - TO_DATE(v_currentDate, 'MM');  --> this statement will always have 0 or 1 value and it will go into the if condition "  IF v_monthspassed = v_period  THEN "...
3) i am not clear on what you are trying to do here... even if you use ADD_MONTHS(sysdate,6) and then later you are subtracting sysdate from this and it will be always equal to 6 right ? i think you are bit confused in what you are tryding to do.

First, say clearly what is your requirement, so that assistance can be given to you accordingly.

Thanks,
0
 
Shaju KumbalathCommented:

CREATE OR REPLACE TRIGGER clear_cushistory
BEFORE INSERT ON CUSTOMERHISTORY
DECLARE
v_currentDate TIMESTAMP;
v_comingDate TIMESTAMP;
v_period NUMBER := 6;
v_xxx NUMBER;

BEGIN
v_currentDate := SYSDATE;
v_comingDate := SYSDATE + 6;


v_monthspassed := TO_DATE(comingDate, 'MM') - TO_DATE(v_currentDate, 'MM');

IF v_monthspassed = v_period THEN
DELETE FROM CUSTOMERHISTORY ;
END IF;
execute immediate 'PURGE RECYCLEBIN';

END;
/
0
 
Shaju KumbalathCommented:
ignore the previuos post
 

CREATE OR REPLACE TRIGGER SHAJU.clear_cushistory
BEFORE INSERT ON lab1
DECLARE
pragma autonomous_transaction;
v_currentDate TIMESTAMP;
v_comingDate TIMESTAMP;
v_period NUMBER := 6;
v_xxx NUMBER;
v_monthspassed number;
BEGIN
v_currentDate := SYSDATE;
v_comingDate := SYSDATE + 6;


v_monthspassed := months_between(v_currentDate,v_comingDate);

IF v_monthspassed = v_period THEN
DELETE FROM lab1;
END IF;
execute immediate 'PURGE RECYCLEBIN';

END;
/
/
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
Shaju KumbalathCommented:
As navin mentioned what ur trying to achive thru this trigger is not clear. Please give more details so that somebody could help.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
Also i do not see any relation to purge recyclebin through a trigger that too when you are trying to delete records from a table which does not have any relation to recycle bin.

please note that deleted records will never go and sit in recyclebin instead dropped objects like if you drop some tables, indexes, functions , packages etc.. will go to recyclebin. If you want later those objects, you can get back those objects from recyclebin or if they are not required at all, you can purge the recyclebin itself to free the space allocated to the objects.
0
 
F-J-KAuthor Commented:
Ok, your responses helped me fix some of my misconceptions. Here is what i wanted to do:

I have a table called CustomerHistory. Any changes/deletion made to a record in customer table, this modified/deleted record will go to CustomerHistory table. This already has been coded successfully.
I thought this history table can get very large in the long run so i wanted to write a trigger that deletes all the records in the CustomerHistory table within every 6 months. Purge Recycle bin is not necessary to be there, i thought DELETE would send records to recycle bin, so is it better to use truncate in this case?

I will look at the code samples been posted above.

Thanks
0
 
F-J-KAuthor Commented:
DECLAR
         v_currentDate   TIMESTAMP;    This holds the current date that trigger is run on.

         v_comingDate    TIMESTAMP; This should hold the coming date which is the date of the next 6 months.

         v_period                NUMBER := 6;    This represents 6 months

         v_monthspassed                   NUMBER;   Holds number of months have passed so far
         
         v_currentDate := SYSDATE;    Get the date the trigger is fired off
         v_comingDate := SYSDATE + 6;   Add six months to it

       
         -- Every time the trigger runs, see how many months have passed till now
         v_monthspassed := TO_DATE(comingDate, 'MM') - TO_DATE(v_currentDate, 'MM');
         

As you see v_currentDate and v_comingDate should be run once every 6 months. In other words, they shouldn't be assigned again until CustomerHistory records gets deleted, then we have to re-assign  v_currentDate and v_comingDate. Otherwise, we will never reach that 6 months because every time trigger runs we assign it a new date and coming date. Obviously, i have an error here that i must fix. I will fix it and ask questions if i have some. Anyhow, feel free to advice and give suggestions...

Thanks
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
first aspect of trigger, ideally speaking, the trigger should be on the customer table with BEFORE DELETE to insert a record into CUSTOMER_HISTORY table.

second aspect is what should be done to cleanup CUSTOMER_HISTORY table. do not confuse this logic in that piece of trigger code for the first aspect.

0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
i assume that you are trying to say that you already have the trigger and it is working fine to insert records into customer_history table. so first part is already closed. If not, explain clearly as to where you need help.

regarding the cleanup for customer_history, i suggest that do not code a trigger to do six months cleanup etc because the trigger proves to be expensive to do such an activity. instead have a dbms_job or unix shell script or dos batch job to delete from this history table or truncate the history table for every given months if you think you do not need the data at all from this table.
0
 
Shaju KumbalathCommented:
I would like to recommend you to create a procedure for deleting the records which are older than 6 monts and create a job for calling the procedure, schedule it in the time u have less load.
 
0
 
Shaju KumbalathCommented:
The trigger will imapct the performance of operations on Cutomer table
0
 
shru_0409Commented:
create one table as parameter and update the as mention below

1. create table parameter (del_date date ) -- this table use for delete the data on del_Date

2. UPDATE parameter  SET del_date = ADD_MONTHS(SYSDATE, 6);
 
 
and as shajukg said create job and call this below procedure ... this is best option

CREATE OR REPLACE PROCEDURE clear_cushistory
IS
   l_del_process_dt DATE;
   l_next_process_dt DATE;
BEGIN
   BEGIN
      SELECT del_date
        INTO l_del_process_dt
        FROM parameter;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         l_del_process_dt := SYSDATE;
   END;

   IF TRUNC(l_del_process_dt) = TRUNC(SYSDATE) THEN
      EXECUTE IMMEDIATE 'truncate table CUSTOMERHISTORY';
   END IF;

   EXECUTE IMMEDIATE 'PURGE RECYCLEBIN';

   UPDATE parameter  SET del_date = ADD_MONTHS(SYSDATE, 6);
END;
0
 
F-J-KAuthor Commented:
"i assume that you are trying to say that you already have the trigger and it is working fine to insert records into customer_history table. so first part is already closed. If not, explain clearly as to where you need help."

Yes you are right. Its done.

-------------

"regarding the cleanup for customer_history, i suggest that do not code a trigger to do six months cleanup etc because the trigger proves to be expensive to do such an activity. instead have a dbms_job or unix shell script or dos batch job to delete from this history table or truncate the history table for every given months if you think you do not need the data at all from this table."

Good info. I will keep it.

------------

Regarding the Procedural code:

"IF TRUNC(l_del_process_dt) = TRUNC(SYSDATE) THEN
      EXECUTE IMMEDIATE 'truncate table CUSTOMERHISTORY';
   END IF;"

I changed the sign to:

IF TRUNC(l_del_process_dt) <= TRUNC(SYSDATE) THEN
      EXECUTE IMMEDIATE 'truncate table CUSTOMERHISTORY';
   END IF;

I put <= just in case something wrong has happened and the 6 months have passed already, i don't
want to wait another 6 months just to delete the records that have been missed to be deleted.

I took off " l_next_process_dt DATE;" since i don't see any need of it. If you got some comments, please feel free to post them
0
 
F-J-KAuthor Commented:
Just to clarify, i put these

   EXECUTE IMMEDIATE 'PURGE RECYCLEBIN';

   UPDATE parameter  SET del_date = ADD_MONTHS(SYSDATE, 6);

in the IF statement because if we haven't hit the six months yet (in case), i don't want to change the date until we hit the 6 months date we assigned previously. I hope my modifications has no glitches.
CREATE OR REPLACE PROCEDURE clear_cushistory
IS
   dateOfDeletion DATE;
BEGIN
   BEGIN
      SELECT deleteHistoryDate
        INTO dateOfDeletion
        FROM historyDeletionDate;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         dateOfDeletion := SYSDATE;
   END;

   IF TRUNC(dateOfDeletion) <= TRUNC(SYSDATE) THEN
      EXECUTE IMMEDIATE 'truncate table CUSTOMERHISTORY';
      EXECUTE IMMEDIATE 'PURGE RECYCLEBIN';


      UPDATE historyDeletionDate SET deleteHistoryDate = ADD_MONTHS(SYSDATE, 6);
   END IF;

END;
/

Open in new window

0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
few comments are :

1) add a commit at the end after the update.
2) test it well before u do this in production.
0
 
F-J-KAuthor Commented:
Thanks. Solved...Well Answered
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 5
  • 5
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now