November 1, 2017 at 11:30 am
I have one main table tbl_con and one more Audit tbl_Aud_con. tbl_con has trigger(AFTER) when it gets updated.previous record(deleted) record will get inserted into the AUDIT table.
eg.
tbl_con
ID updated_date Fname Lname status
1001 10-02-2017 ajay chain blocked
tbl_Aud_con
ID updated_date Fname Lname status
1001 10-01-2017 ajay chain Allow
In this example Allowed record gets updated to blocked in main tbl_con.
I want that date when the record get updated. In this case its 10-02-2017 from tbl_con.
There are multiple cases. I want blocked record updated date only
1. when record is in main table and it is already blocked then its updated get will be shows.
2.Record is in main table in blocked status and its NULL status in audit then also main con tables updated date will be show.because this record is NULL first and its updated to blocked.
3.when records get multiple time allowed and blocked and its current status is blocked in main table.Then when its last changed to blocked will be shown. its difficult one because when other fields gets updated then also it has audit entries.
eg
tbl_con
ID updated_date Fname Lname status
1001 10-05-2017 ajay chain blocked
tbl_Aud_con
ID updated_date Fname Lname status
1001 10-01-2017 ajay chavan NULL
1001 10-02-2017 ajay chain Allow
1001 10-03-2017 ajay raj Blocked
1001 10-04-2017 ajay raj Allow
1001 10-05-2017 ajay chain blocked
1001 10-05-2017 ajay rock blocked
in this case record blocked at 10-04-2017 when the status is changed allow to blocked.
please help.
November 1, 2017 at 11:51 am
Gaja - Wednesday, November 1, 2017 11:30 AMI have one main table tbl_con and one more Audit tbl_Aud_con. tbl_con has trigger(AFTER) when it gets updated.previous record(deleted) record will get inserted into the AUDIT table.eg.
tbl_con
ID updated_date Fname Lname status
1001 10-02-2017 ajay chain blockedtbl_Aud_con
ID updated_date Fname Lname status
1001 10-01-2017 ajay chain AllowIn this example Allowed record gets updated to blocked in main tbl_con.
I want that date when the record get updated. In this case its 10-02-2017 from tbl_con.There are multiple cases. I want blocked record updated date only
1. when record is in main table and it is already blocked then its updated get will be shows.
2.Record is in main table in blocked status and its NULL status in audit then also main con tables updated date will be show.because this record is NULL first and its updated to blocked.
3.when records get multiple time allowed and blocked and its current status is blocked in main table.Then when its last changed to blocked will be shown. its difficult one because when other fields gets updated then also it has audit entries.eg
tbl_con
ID updated_date Fname Lname status
1001 10-05-2017 ajay chain blockedtbl_Aud_con
ID updated_date Fname Lname status
1001 10-01-2017 ajay chavan NULL
1001 10-02-2017 ajay chain Allow
1001 10-03-2017 ajay raj Blocked
1001 10-04-2017 ajay raj Allow
1001 10-05-2017 ajay chain blocked
1001 10-05-2017 ajay rock blockedin this case record blocked at 10-04-2017 when the status is changed allow to blocked.
please help.
You've been here long enough to know that you need to provide sample DDL, sample data in the form of INSERT statements and then desired output, based on the DDL and sample data provided.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply