Well, I'm not sure if the rest of the group will agree with me (I'm by no means an authoritative expert)
but I think you're going to have to treat each row as a separate entity. How do you have this entity mapped? What is used as the Id of the entity? -- It must be table_id, right? You couldn't have it be emp_id as it's not unique. -- But using table_id seems like an absolute waste; from one update to the next, the 'id' of the 'live' employee record changes. This sounds like a really messy situation. Your schema and model seems to break every concept of a primary key. Why isn't a separate archive table possible? ------ Joe Brockhaus [email protected] ------------ On Mon, Feb 6, 2012 at 9:19 PM, Ankita Kumar <[email protected]> wrote: > In this case, the Emp_id will not be the primary key. There is another > column say table_id which is an auto generated and is the primay key > for the table. So actually, the rows would look like > > 1, 1234567, ABC, IT Dept, 03-01-2011, 05-01-2011,UpdatedByUser > 2, 1234567, ABC, IT Dept, 05-01-2011, null ,UpdatedByUser2 > > Also, all this data needs to sit in the same table. > > On Feb 7, 12:19 am, Joe Brockhaus <[email protected]> wrote: > > I'm not sure how you are expecting this to work in only a single table. > > > > In your example, you left out the crucial PK (Emp_id) from the data. > > > > >> .. then the table will look like ... > > > > It looks like you are suggesting the table will have a duplicated row for > > each modification? > > This itself seems flawed. How do you distinguish the rows from versioning > > rows or actual other data? > > > > For this archive data, I would recommend a separate table, > > Employee_Archive. (This would be independent of NHibernate) > > -- Employee_Archive should have every field from the Employee table, and > > one additional: ArchiveID (the PK of the _Archive table) > > -- create a trigger on the Employee table. > > ---- 'before update' --> insert the existing row from Employee into the > > Employee_Archive table, generating a new ArchiveID each time. > > -- then allow the update to occur as normal. > > > > By doing this: > > -- your Employee table will have 1 row per employee, as expected. > > -- your archive table will have 1 row for every change to that employee. > > > > You don't need to do anything funky in NHibernate to have it do this. > > > > Then, you should create another entity to query this archive table that > is > > mapped explicitly to it. > > > > ------ > > Joe Brockhaus > > [email protected] > > ------------ > > > > > > > > > > > > > > > > On Mon, Feb 6, 2012 at 2:00 PM, Ankita Kumar <[email protected]> > wrote: > > > Hi, > > > > > I want to know can i customize the insert operation? I have a table > > > say Employee with columns Emp_id,Emp_Name,Dept, StartDate, EndDate and > > > UpdatedBy. We have the last three column to keep track of all the > > > chnages made on a record, since it insert in the table. > > > Like on 3rdJan2011, I inserted a record ABC, IT Dept, 03-01-2011, > > > null ,UpdatedByUser > > > > > If on 5th Jan, I updated the record , then the table will look like > > > ABC, IT Dept, 03-01-2011, 05-01-2011,UpdatedByUser > > > ABC, IT Dept, 05-01-2011, null ,UpdatedByUser > > > > > Note: That the latest record will always have null in the end date. > > > > > Well this is the logic that I need to implement while inserting the > > > record in the database. Can someone help me on how I can acheive it in > > > NHibernate? > > > > > -- > > > You received this message because you are subscribed to the Google > Groups > > > "nhusers" group. > > > To post to this group, send email to [email protected]. > > > To unsubscribe from this group, send email to > > > [email protected]. > > > For more options, visit this group at > > >http://groups.google.com/group/nhusers?hl=en. > > -- > You received this message because you are subscribed to the Google Groups > "nhusers" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/nhusers?hl=en. > > -- You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
