Hi all you SQL Gurus,
My history has been pretty much all TTable based.  Very little to no SQL.  I have some awareness of simple stuff but lose my way once it gets complex.
Given that EDB seems to be very much SQL orientated I figure I gotta get my skill level up.
Anyway, I have a short term issue I need help with.
Scenario:
Two sets of tables - Live & Archve.
1.  JobTickets & AJobTickets.
2.  Lineitems & ALineitems.
3.  TechData & ATechData.
4.  UsersLog & AUsersLog
..... etc.
JobTickets, LineItems & TechData have a common field 'Job#'.  Yes I know 'Job#' doesn't work att.  Doesn't recognise the '#'. Tim is working on it.  Job# currently appears as 'Job_'.
UsersLog to AUsersLog is easy.
The first task is to set the 'filter' for JobTickets then transfer those records to AJobTickets.
{sql}
INSERT INTO AJobTickets
SELECT * FROM JobTickets
WHERE  JobStatus = 'CA' or ((JobStatus = 'CO') and (Paid = True) and (date_out < DATE '2018-05-01'))
{sql}
This works fine.  The date_out value to be tested will be a Parameter.
I can then easily do..
{sql}
UPDATE JobTickets
SET Archive = True
WHERE JobStatus = 'CA' or ((JobStatus = 'CO') and (Paid = True) and (date_out < DATE '2018-05-01'))
{sql}

What I now need to do, which I can at the TTable level, but prefer to do with SQL:

1.  Copy all records from LineItems to ALineItems where JobTickets.Archive = True.  Job# is the common field.  Multiple LineItem records per Job#
2.  Delete those LineItem records copied.
2.  Copy all records from TechData to ATechData where JobTickets.Archive = True.  Job# is the common field.  One record per Job #.
3.  Delete those TechData records copied.
4.  Delete all those records in JobTickets with the Archive flag set.

Appreciate any proposals.

Regards & TIA,
Ian

Ian

Quick comment first
>JobTickets, LineItems & TechData have a common field 'Job#'. Yes I know 'Job#' doesn't work att. Doesn't recognise the '#'. Tim is working on it. Job# currently appears as 'Job_'.

why not use JobNo since # generally translates to number - its a bit more descriptive

Right onwards: You now need to start investigating the wonderful world of triggers and transactions.

I still don't make enough use of them but I also was more into navigational (table) methods of doing things.

Triggers are essentially SQL scripts that are executed by the system in response to a specific event eg BEFORE UPDATE, AFTER DELETE etc If you think of them as the equivalent to the events on a TEDBTable you can't go far wrong.

You can make things as simple or as complex as you want. I'd guess that whatever you do with JobTickets / AJobTickets also affects Lineitems & ALineitems. so here goes

1. start a transaction
2. copy ONE job ticket to archive
3. complete transaction or if error rollback

I'm suggesting one ticket at a time because it keeps the transaction size down and hence the amount of time the tables are locked to a minimum. It also means that you won't run out of RAM.

Step 2 is obviously the important bit and I'd write an AFTER INSERT trigger for AJobTickets (example below - it might even work) which will

a) use the NEW id value (Job# ?) to copy the line items from LineItems to ALineItems
b) Delete the JobTicket


I'd also write an AFTER INSERT trigger for ALineItems which would

a) delete the LintItem

Roy Lambert


TRIGGER "DeleteOldJob"
BEGIN
 EXECUTE IMMEDIATE 'INSERT INTO ALineItems SELECT * FROM LineItems WHERE Job_ = ' + CAST(NEWROW.Job_ AS VARCHAR(10));
 EXECUTE IMMEDIATE  'DELETE FROM JobTicket WHERE Job_ = ' + CAST(NEWROW.Job_ AS VARCHAR(10));
END

Hi Roy,
Hmmm.  Lots of food for thought there.
Let me digest and ruminate on it.
Regards,
Ian