[caf78]

I have 2 tables: OrderHeaders and OrderLines which has a one-to-many relationship.
Depending on if the field CreateDate in OrderHeaders has expired som date, I want to delete the corresponding rows in OrderLines AND OrderHeaders... but how?

The tables share the fields CompanyID, CustomerID, OrderNO.

[LKBrwn_DBA]

If OrderLines table was created with a foreign key constraint on OrderHeader with 'ON DELETE CASCADE' option, alll you have to do is:

Delete From OrderHeader
Where CreateDate <= ExpireDate;

Else, you need first to remove the OrderLines:

Delete From OrderLines L
Where Exists (
Select 1 From OrderHeader H
Where H.CompanyID = L.CompanyID
And H.CustomerID = L.CustomerID
And H.OrderNO = L.OrderNO
And L.CreateDate <= ExpireDate);

And then execute the delete of OrderHeader.

[dbmadcap]

You can have a daily process (procedure/function) scheduled to run at night to check for any such orders and then delete from OrderLines first and then from OrderHeaders.

Or you could use DBMS_JOB to schedule the procedure/function to do this. Once you have written the correct procedure/function to do this, it is easy to schedule using DBMS_JOB.

Say if you have a procedure 'test_job', you can schedule it as below :

declare
  l_job number;
begin
  dbms_job.submit(l_job, 'test_job;',sysdate+1/200);
end;
/
commit
/

Hope this helps !!

Originally posted by caf78
I have 2 tables: OrderHeaders and OrderLines which has a one-to-many relationship.
Depending on if the field CreateDate in OrderHeaders has expired som date, I want to delete the corresponding rows in OrderLines AND OrderHeaders... but how?

The tables share the fields CompanyID, CustomerID, OrderNO.