locked
How to update one table with last inserted ID from another table? RRS feed

  • Question

  • User1216627406 posted

    Greetings experts,

    This is a bit too complicated, at least for me.

     I have four tables: Sermons, Speakers, SermonsHistory and SpeakerHistory

    The relevant field names are:

    SERMONS
    SermonID PK
    
    SPEAKERS
    SpeakerID
    
    SPEAKERHISTORY
    SpeakerHistoryID PK
    SpeakerID fk from SPEAKERS table
    
    SERMONSHISTORY
    SermonHistoryID PK
    SpeakerID fk from SPEAKERS table
    SpeakerHistoryID FK from SpeakerHistory table
    

    When records in SERMONS table are deleted, the records are inserted into SERMONSHISTORY and when records in SPEAKERS table are deleted, they are inserted into SPEAKERHISTORY table.

    We use Trigger to accomplish this. 

    Below is the trigger that inserts deleted records from SERMONS table into SERMONSHISTORY table.

    ALTER TRIGGER [dbo].[SermonsRecsAfterDelete]
        ON [dbo].[Sermons]
        FOR DELETE
        AS
         INSERT INTO SermonsHistory
             (
        	sermonID,
        	SpeakerID,
        	dateFilmed,
        	sermonTitle,
        	Scripture,
        	VideoURL,
        	SermonNotes,
        	ServerInstanceName,
        	ServerName,
        	dateDeleted
              )
            SELECT 
        	sermonID,
        	SpeakerID,
        	dateFilmed,
        	sermonTitle,
        	Scripture,
        	VideoURL,
        	SermonNotes,
              CAST(SERVERPROPERTY('MachineName') AS VARCHAR(50)) MachineName,
              CAST(SERVERPROPERTY('ServerName') AS VARCHAR(50)) ServerName,
              GETDATE()
        FROM DELETED;

    So far, so good.

    The issue we are having is that there is no way to grab SpeakerHistoryID from SPEAKERHISTORY table and insert it into SERMONSHISTORY table.

    Our decision is to perform an update operation to grab the last inserted SpeakerHistoryID from SPEAKERHISTORY table and update the SpeakerHistoryID on SERMONSHISTORY table because at this time, it is NULL.

    Here is the update code I am using and so far, it is not updating the SERMONSHISTORY table.

    UPDATE SermonsHistory
            SET SpeakerHistoryID = (SELECT Max(SpeakerHistoryID) SpeakerHistoryID FROM SpeakerHistory WHERE SpeakerHistory.SpeakerID != SermonsHistory.SpeakerID)
    WHERE SpeakerHistoryID  IN (SELECT SpeakerHistoryID FROM SpeakerHistory)

    Any ideas how to handle this?

    Thanks in advance for your assistance.

    Saturday, March 20, 2021 3:24 AM

All replies

  • User753101303 posted

    Hi,

    Do you really need SpeakerHistoryID and SermonHistoryD ? It seems the ID column found in your history tables could NOT use identity and be just a copy of the ID value you had in your main tables ?

    For now my understanding is that the main issue is that you assign a new id to each row copied in your history tables which doesn't seems to be really needed for now.

    Saturday, March 20, 2021 1:50 PM
  • User1216627406 posted

    Do you really need SpeakerHistoryID and SermonHistoryD ? 

    Yes, because of the parent/child relationship between the tables, I needed to perform cascade deletes in those tables:

    ALTER TABLE [dbo].[SermonsHistory]  WITH CHECK ADD  CONSTRAINT [FK_Speakers_SermonsHistory] FOREIGN KEY([SpeakerHistoryID])
    REFERENCES [dbo].[SpeakerHistory] ([SpeakerHistoryID])
    ON UPDATE CASCADE
    ON DELETE CASCADE
    GO

    To do so, I have to have PK in parent table as FK in child table unless of course someone can convince me that you can set up the delete constrains with non primary keys.
     

    Saturday, March 20, 2021 2:48 PM
  • User475983607 posted

    Yes, because of the parent/child relationship between the tables, I needed to perform cascade deletes in those tables:

    The suggestion is changing the current history table design by taking advantage of relationship you already have between Sermons and Speakers.  In other words, use the same foreign key constraint between fields in the history tables.  I agree with this suggestion as it solves the problem and simplifies the design. 

    Saturday, March 20, 2021 3:23 PM
  • User1216627406 posted

    There may a bit of misunderstanding here between what I have and what you think I have. It is also possible that I am the one misunderstanding you.

    If I may, just a little history of what we are doing.

    Initially, we have two tables, Speakers table and Sermons table. Here is the ddl:

    Speakers - table

    SpeakerID, int PK

    Additional fieldnames.

    Sermons - table

    SermonID int PK

    SpeakerID FK to Speakers table.

    Referential integrity was set.

    This is where it got a bit tricky.

    User wants a recycle bin type history table where any records deleted from any or both tables above should be stored in corresponding history table.

    For instance, records deleted in Speakers table to be stored in SpeakerHistory table.

    Records deleted in Sermons table to be stored in SermonsHistory table.

    They also want the ability to restored these records back to original tables if they determine that the delete was a mistake.

    Finally, they want to be able to permanently delete some or all records from the history table.

    All of this is done through our asp.net web app.

    There are two issues which brought me to post it here.

    1, If you delete a record from one of the history table but not both, you create data anormally because now, one record exists on the db but the dependency is gone.

    2, I have got to find a way to delete both records when needed and I am trying to understand why you guys think I can use the same IDs from Speakers and Sermons tables as IDs for history tables.

    I will like to add here as far as Speakers table and Sermons table are concerned, because I am inserting records into both tables from my asp.net app, once record is inserted into Speakers, table, I grab the last inserted ID and insert that into Sermons table thereby making easier to generate queries based on PK/FK relationships as well as set update/delete cascades with trigger.

    Even if I were to use same PK/FK for our history tables, how do I manage those?

    Saturday, March 20, 2021 4:02 PM
  • User753101303 posted

    My personal preference here would be to just have on each table a deletion flag (I often prefer to use a date/time value telling when the row was deleted rather than a boolean).

    Your approach seems to assign a deleted sermon to the latest deleted speaker which is likely not acceptable as you'll lose the actual speaker.

    You can copy the sermon with the same pk and fk values but to ensure integrity you have to copy the spearker as well even if not deleted yet. So you'll need to join that with the Speaker table to see if a speaker is really deleted or not.

    Whatever you chose it doesn't have to be exclusive. If using a lflag you can still create a SpeakerHistory and SermonHistory view to show only deleted rows (and without integrert concerns) and if using history tables you can still create views to show all rows deleted or not.

    Edit: in all cases the fact that a row is deleted or not doesn't have to change its pk. You coudl also perhaps combine both ie having a logical flag in the sermon table so that it can be marked as deleted without integriry concern and move a speaker and its sermon to history tables when the speaker is really deleted ????

    Saturday, March 20, 2021 4:29 PM
  • User1216627406 posted

    Your approach seems to assign a deleted sermon to the latest deleted speaker which is likely not acceptable as you'll lose the actual speaker.

    No, I don't think that's what I am doing.

    In a parent/child relationship, in this case, Speaker being parent and Sermon being child.

    You can delete child without deleting Speaker because you can go back later and add the child again based on Speaker ID.

    However, if you delete parent without deleting child or without deleting child first, again as mentioned earlier, you create data inconsistency.

    To avoid that, we have to set constraints on both tables.

    There is no issues there at all.

    Where we have the issues we have is the history tables.

    I can't have them operate independently where you could delete each table in isolation. This will create a nightmare for the user.

    I am interested the two items you mentioned and will try to explore their viability - using flags or views but I need a bit more info if you can please.

    Saturday, March 20, 2021 4:45 PM
  • User475983607 posted

    1, If you delete a record from one of the history table but not both, you create data anormally because now, one record exists on the db but the dependency is gone.

    You have the same situation now only you are using different keys.

    2, I have got to find a way to delete both records when needed and I am trying to understand why you guys think I can use the same IDs from Speakers and Sermons tables as IDs for history tables.

    Because you can.  The operation is a very simple INSERT INTO then a delete in the main table.   This code can be scripted in a stored procedure rather than a trigger.

    Cascade delete is fine if the primary key record is deleted.  Deleting the foreign key does not cascade.  

    Many of us have designs with history tables.  We are simply explaining how history tables work in our designs.  

    Saturday, March 20, 2021 7:33 PM
  • User1216627406 posted

    Deleting the foreign key does not cascade.

    I completely agree.

    Not what I am trying to do.

    I started to do this in SP as you suggested, awhile back.

    I will try it again later. I think I still have a copy of the unfinished SP.

    NOTE: I even tried dabbling into  temporal tables but it was way over my head.

    Saturday, March 20, 2021 9:32 PM
  • User1216627406 posted

    Thank you guys, using your idea of using speakerId, PK from speakers and sermons, I was able to handle this on the front end using my asp.net and it seems to have worked so far, after many tests.

        protected void grvSpeakerBin_RowCommand(object sender, GridViewCommandEventArgs e)
        {
            SqlConnection con;
    
            if (e.CommandName == "cmDel")
            {
                string strConnStr = ConfigurationManager.AppSettings["constr"].ToString();
                con = new SqlConnection(strConnStr);
                con.Open();
                string sql = "";
                SqlCommand cmd;
                int SpeakerId = Convert.ToInt32(e.CommandArgument);
                Label lblSpeaker;
                lblSpeaker = (Label)(grvSpeakerBin.FindControl("lblSpeaker"));
                sql += "DELETE FROM ShootLogHistory WHERE SpeakerID = " + SpeakerId + "; ";
                sql += "DELETE FROM SermonsHistory WHERE SpeakerID = " + SpeakerId + "; ";
                sql += "DELETE FROM SpeakerHistory WHERE SpeakerID = " + SpeakerId + "; ";
                con = new SqlConnection(strConnStr);
                con.Open();
                cmd = new SqlCommand(sql, con);
                //Response.Write(sql);
                //Response.End();
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                con.Close();
                this.BindSpeakersGrid();
                SpeakerMsg.ForeColor = System.Drawing.Color.Red;
                SpeakerMsg.Text = lblSpeaker.Text + " Details Deleted successfully";
                grvSpeakerBin.DataBind();
                int i = cmd.ExecuteNonQuery();
                if (i == 1)
                {
                    lblMsg.ForeColor = System.Drawing.Color.Red;
                    lblMsg.Text = "Record has been deleted and cannot be recovered";
                    BindSpeakersGrid();
                }
                else
                {
                    lblMsg.ForeColor = System.Drawing.Color.Red;
                    lblMsg.Text = "There is a problem deleting this record";
                }
    
                Page_Load(sender, e);
                con.Close();
            }
        }

    I appreciate you guys hanging in there with me with your expertise contributions.

    Sunday, March 21, 2021 7:03 AM
  • User1535942433 posted

    Hi simflex,

    Congratulation!If you still have problems,you could post to us.We're willing to help you.

    And you could mark answers which help you.It  makes you more popular for your next thread.

    Best regards,

    Yijing Sun

    Wednesday, March 24, 2021 5:28 AM