INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Deleting records

Deleting records

(OP)
We need to define a process where we have to use a log table to update our target table.

Here's the scenario:

Source Table: CUSTLOG (Customer Log table)
Target Table: CUST(Customer)

Every day the CUSTLOG is populated from a Production Database to another database where a similar table CUSTLOG (This is the database where both the CUSTLOG and CUST reside) exists with a similar structure. This table has 4 kinds of log actions :
I - Insert
D - Delete
B - Before Image
A - After Image

A & B are logged when an update occurs

The custlog table also has a log id for every log that's captured.

The target table Cust needs to be populated with the latest customer information from the custlog table. So, for ex: if a customer has been updated for like 15 times, we need to take the last and the latest log id and it's corresponding log action and update the cust table.

We also have soft-deletes wherein the if the customer is sof-deleted there's a date column that's populated and the log action is 'D' and if the customer is added back then the action is 'I' .

It's the hard deletes that is not being handled very well.

When a customer is soft deleted then the log action 'D' with a date.
When a customer is hard deleted then the log action is also 'D' without a datdel.

But if a soft deleted customer is hard deleted then also the log action is 'D' and as this record was first sof-deleted it has a date. And if the soft deleted customer is not hard deleted that record should still be in the cust table

Ex:


1  D XYZ 5/9/2000
2  D XYZ 5/9/2000

Any thoughts on this..


Thanks,
diuser

RE: Deleting records

(OP)
Quick update:

When a customer is soft deleted then there are 3 entries in the log table (B, A & D) with a date.

When a customer is hard deleted then there is only one log action which is also 'D' without a date.

But if a soft deleted customer is hard deleted then also the log action is 'D' and as this record was first sof-deleted it has a date.
Ex:

soft deletes:
B   XYZ
A   XYZ  5/9/2000
D   XYZ  5/9/2000

HARD DELETE
D   XYZ  NULL

HARD DELETE OF A SOFT-DELETED RECORD
D   XYZ  5/9/2000

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close