locked
If update in primary table inactive same record in secondary table and load updated record RRS feed

  • Question

  • Hi Team,

    Have one requirement like If update in primary table inactive same record in secondary table and load updated record.

    If it is possible please help on same.

    Sample data:

    Primary table -

    BookID BookName Category Price Price_Range
    1 Computer Architecture Computers 125.60 100-150
    2 Advanced Composite Materials Science 172.56 150-200
    3 Asp.Net 4 Blue Book Programming 56.00 50-100
    4 Strategies Unplugged Science 99.99 50-100
    5 Teaching Science Science 164.10 150-200
    6 Challenging Times Business 150.70 150-200
    7 Circuit Bending Science 112.00 100-150
    8 Popular Science Science 210.40 200-250
    9 ADOBE Premiere Computers 62.20 50-100

    Secondary table -

    BookID BookName Category Price Price_Range isActive
    1 Computer Architecture Computers 125.60 100-150 1
    2 Advanced Composite Materials Science 172.56 150-200 1
    3 Asp.Net 4 Blue Book Programming 56.00 50-100 1
    4 Strategies Unplugged Science 99.99 50-100 1
    5 Teaching Science Science 164.10 150-200 1
    6 Challenging Times Business 150.70 150-200 1
    7 Circuit Bending Science 112.00 100-150 1
    8 Popular Science Science 210.40 200-250 1
    9 ADOBE Premiere Computers 62.20 50-100 1

    Please refer bellow screenshot 



    Thanks Bala Narasimha

    Tuesday, October 1, 2019 9:18 AM

All replies

  • Hi

    Please find the below code which will help you to fulfill the requirement.

    CREATE TABLE table1 
    (BookID int, BookName varchar(100),Category varchar(100), Price numeric(6,2),Price_Range varchar(100))
    
    CREATE TABLE table2
    (BookID int, BookName varchar(100),Category varchar(100), Price numeric(6,2),Price_Range varchar(100),IsActive bit)
    
    
    CREATE TRIGGER TR_table1_table2  
    ON table1  
    AFTER UPDATE,INSERT, DELETE
    AS  
    --Update
    UPDATE t
    SET t.IsActive = 0
    FROM table2 t
    INNER JOIN inserted i
    ON i.BookID = t.BookID
    INNER JOIN deleted d
    ON i.BookID = d.BookID
    WHERE t.IsActive = 1
    
    --Insert
    INSERT INTO table2
    SELECT 
    	i.*,1 AS IsActive
    FROM 
    inserted i
    LEFT JOIN deleted d
    ON i.BookID = d.BookID
    GO
    
    --Test
    
    INSERT INTO table1 values (1,'test','test',1,'1-1')
    
    SELECT * FROM table1
    SELECT * FROM table2
    
    UPDATE table1
    SET Price =3
    
    SELECT * FROM table1
    SELECT * FROM table2

    Hope this is helpful !!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Tuesday, October 1, 2019 10:51 AM
  • You are trying to do a "type 2" table with history.

    The simplest way to accomplish this by using start/end effective dates of the records, not a separate table.

    Tuesday, October 1, 2019 12:35 PM
  • >> Have one requirement like If update in primary table inactive [sic] same record [sic] in secondary table and load updated record [sic] .<<

    Please explain what a primary table is. We never use that term the whole time I served on the ANSI ISO standards committee for SQL, nor have I ever used in any of my 10 books. However, because I am old I do remember before we had RDBMS there is a concept of a master file. This is totally non-relational!

    Then I see you feel that you are exempted from even the most basic netiquette of posting DDL on SQL forum I guess I'll have to do that for you since you're too rude or lazy to do this for everyone else here. You didn't even bother to research the international standard book number (ISBN) when you were when you were trying to create this schema. You also don't even know that there's no such thing as a "book_name"; there's a difference between a "_name" and "_title" can be very important (I used to own several bookstores dealing collectibles").

    Yet you expect other people to spend hours of their own time serving you

    >> Secondary table -<<

    Again a term I have never heard in ANSI/ISO standards, aim iBooks or any of the basic documents on RDBMS. We do have however tables that model strong and weak entities. A weak entity (such as an order detail line) exist only because a strong entity supports it (the order). Is that what you meant? These dependencies get a little trickier than you seem to think

    >>  Please refer bellow screenshot <<

    We do not type screenshots into a schema for someone who's too lazy or rude to post it. This is been the basic netiquette on SQL forums for over 30 years. If you think you're special please tell us why; I'd love to hear it. 



    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Wednesday, October 2, 2019 4:09 AM
  • Hi BaluChalla,

     

    I do a little change in nkumar230's script. Please check.

     

    Also , you can use Temporal Table History, please refer to https://www.mssqltips.com/sqlservertip/4674/managing-temporal-table-history-in-sql-server-2016/.

     
    IF OBJECT_ID('table1') IS NOT NULL drop table  table1 
    IF OBJECT_ID('table2') IS NOT NULL drop table  table2 
    go
    
    CREATE TABLE table1 
    (BookID int, 
    BookName varchar(100),
    Category varchar(100), 
    Price numeric(6,2),
    Price_Range varchar(100))
    
    CREATE TABLE table2
    (BookID int, 
    BookName varchar(100),
    Category varchar(100), 
    Price numeric(6,2),
    Price_Range varchar(100),
    IsActive bit)
    go
    
    CREATE TRIGGER TR_table1_table2  
    ON table1  
    AFTER UPDATE,INSERT, DELETE
    AS  
    --DELETE/Update
    UPDATE t
    SET t.IsActive = 0
    FROM table2 t
    INNER JOIN deleted d
    ON t.BookID = d.BookID
    WHERE t.IsActive = 1
    
    --Insert
    INSERT INTO table2
    SELECT 
    	i.*,1 AS IsActive
    FROM 
    inserted i
    
    GO
    
    --Test
    
    INSERT INTO table1 values (1,'test','test',1,'1-1')
    
    SELECT * FROM table1
    SELECT * FROM table2
    /*
    BookID      BookName   Category      Price    Price_Range
    ----------- ---------- ------------- -------- ----------------
    1           test       test          1.00     1-1
    BookID      BookName   Category      Price    Price_Range      IsActive
    ----------- ---------- ------------- -------- ---------------- --------
    1           test       test          1.00     1-1              1
    */
    
    UPDATE table1
    SET Price =3
    
    SELECT * FROM table1
    SELECT * FROM table2
    /*
    
    BookID      BookName     Category    Price   Price_Range
    ----------- ------------ ----------- ------- ---------------
    1           test         test        3.00    1-1
    BookID      BookName     Category    Price   Price_Range     IsActive
    ----------- ------------ ----------- ------- --------------- --------
    1           test         test        1.00    1-1             0
    1           test         test        3.00    1-1             1
    */

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, October 2, 2019 4:29 AM
  • HI Rachel,

    Thanks for your response.

    Is their any option to track data without creating trigger?

    If it is possible please help on same.


    Thanks Bala Narasimha

    Thursday, October 3, 2019 6:35 AM
  • This is not quite what you expected above, but you can try it.

    For more information, Please refer to https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-2017

    IF OBJECT_ID('table1') IS NOT NULL drop table  table1 
    go
    
    CREATE TABLE table1 
    (BookID int PRIMARY KEY CLUSTERED, 
    BookName varchar(100),
    Category varchar(100), 
    Price numeric(6,2),
    Price_Range varchar(100),
    starttime  DATETIME2 GENERATED ALWAYS AS ROW START
                HIDDEN NOT NULL,
    endtime DATETIME2 GENERATED ALWAYS AS ROW END
                HIDDEN NOT NULL,
    PERIOD FOR SYSTEM_TIME (starttime, endtime)   
    )
    WITH(SYSTEM_VERSIONING= ON (HISTORY_TABLE=dbo.table1history))
    
    
    INSERT INTO table1 values (1,'test','test',1,'1-1')
    /*
    ------table1
    BookID      BookName     Category   Price   Price_Range
    ----------- ----------------------- ------- -----------
    1           test         test       1.00    1-1
    ------table1history
    BookID      BookName     Category   Price   Price_Range starttime   endtime
    ----------- ----------------------- ------- ----------- ----------- -------------
    */
    GO
    update table1 set Category='aaa' where BookID=1
    
     
    select * from table1
    select * from table1history
    /*
    ------table1
    BookID      BookName    Category   Price     Price_Range
    ----------- ---------------------- --------- --------------
    1           test        aaa        1.00      1-1
    ------table1history
    BookID      BookName    Category   Price     Price_Range    starttime                   endtime
    ----------- ---------------------- --------- -------------- --------------------------- ---------------------------
    1           test        test       1.00      1-1            2019-10-07 07:31:06.2470082 2019-10-07 07:35:48.2820446
    */

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 7, 2019 7:40 AM
  • Hi ,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, October 11, 2019 9:43 AM
  • Hi ,

     

    I am  writing to follow up this thread with you again . Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 14, 2019 8:42 AM