Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115

    Unanswered: Insert, Update & Delete on two tables with same data structure...

    I have created two table with same data structure. I need realtime effects (i.e. data) on both tables - Table1 & Table2.

    Following Points to Consider.

    1. Both tables are in the same database.

    2. Table1 is using for data entry & I wants the same data in the Table2.

    3. If any row insert, update & delete occers on Table1, the same effect should be done on Table2.

    4. I need real time data insert, update & delete on Table2.

    I knew that using triggers it could be possible, I have successfully created a trigger for inserting new rows (using logical table "Inserted") in Table2 but not succeed for update & delete yet.

    I want to understand how can I impletement this successfully without any ambiguity.

    I have attached data structure for tables. Thanx...
    Attached Files Attached Files

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You want TWO tables with IDENTICAL structure and the SAME data in a SINGLE database?
    We can help you debug your triggers if you post the code, but WHY?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115
    Actually we required some reports and as we have old version application, it could not be possible to generate required reports.

    The data is dynamic (i.e. Table1) & changing with the stock quantity IN & OUT, thats why I will store data for specific span of time in the new table (Table2). I will use that data for reporting.

    Which code you required..? I have attached script for creating a tables.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I understand you want inserts copied to the second table.
    What about updates? Do you want the data in the second table updated, or do you want a new record added instead?
    What about deletes? Do you want the data in the second table deleted as well, or do you just want to mark the record as deleted?

    Did you try writing triggers for Update and Delete? If so, post the code for those triggers and we will help you debug it our fix syntax errors.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    oye...redundant data...

    In any case if you follow the Hint link sticky at the top of the forum and post what it tells you, I'm sure we can supply enough rope
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115
    As I have told that the data entry done through frontend, I want each and every effect (i.e. row insert, update or delete) on Table2.

    As I have told you that the second table I am using for reporting purpose & the reports will be wrong if it is not reflect the data which entered or modified last.

    May be you think its too cumbersome but now let me explain the full scenario.

    1. I have to do this because I have added new column in the Table2 which is not part of Table1.

    2. Using insert trigger on Table1 I can add new row in Table2 same as Table1 as well as I can feed data in the new added column which is not part of Table1.

    3. Whenever row inserted, update or delete in Table1 the Table2 should update accordingly.

    4. I can not cascade update or delete because both tables are having only foreign keys. (cFinYrs, cLocCode, cMonth, cItemCode are foreign keys)

    5. I will re-write triggers according to my requirement, but I need little help to be clear of the concept from you expert guys.

    6. The script which I have given for creating a tables will create the same data structure for tables.

    I have written trigger for insert, it's given below. It's working good for insert.

    CREATE TRIGGER [InForRpt] ON [dbo].[Table1]
    FOR INSERT

    AS

    Declare @cFinYrs varchar(3)
    Declare @cLocCode varchar(7)
    Declare @cMonth varchar(3)
    Declare @iSrNo int
    Declare @cItemCode varchar(20)
    Declare @dQty dec
    Declare @dRate dec(9,2)
    Declare @cDesc varchar(200)
    Declare @cCreated varchar(6)
    Declare @dtcreated datetime
    Declare @cModified varchar(6)
    Declare @dtModified datetime
    Declare @cMachIP varchar(15)

    SET @cFinYrs = (select cFinYrs from inserted)
    SET @cLocCode = (select cLocCode from inserted)
    SET @cMonth = (select cMonth from inserted)
    SET @iSrNo = (select iSrNo from inserted)
    SET @cItemCode = (select cItemCode from inserted)
    SET @dQty = (select dQty from inserted)
    SET @cDesc = (select cDesc from inserted)
    SET @cCreated = (select cCreated from inserted)
    SET @dtcreated = (select dtCreated from inserted)
    SET @cModified = (select cModified from inserted)
    SET @dtModified = (select dtModified from inserted)
    SET @cMachIP = (select cMachIP from inserted)

    Select @dRate = drate from ssstockmst where citemcode=@cItemCode

    Insert INTO Table2 values(@cFinYrs, @cLocCode, @cMonth,
    @iSrNo, @cItemCode, @dQty,
    @dRate, @cDesc, @cCreated,
    @dtCreated, @cModified,
    @dtModified, @cMachIP)


    How I can make this happen..? Thanx for replying...

  7. #7
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115
    Quote Originally Posted by Brett Kaiser
    oye...redundant data...
    Yeah it could be redundant data but it will helps me lot to produce reports according to management requirement. And this data will not be heavy in size (1 to 5MB) so don't affect the server space as we have provision for same.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by rajeshpatel
    I have to do this because I have added new column in the Table2 which is not part of Table1.
    This still makes no sense. Why not just add the column to Table1? Are you dealing with a reduced record set in table2? Is that data truncated occasionally, or filtered? We need to know how that data is being retained before helping you create Update/Delete triggers.

    But regarding your insert trigger....

    The method you have chosen is not only slow and verbose, but will also fail if more than one record is inserted into the table by a single transaction. Triggers MUST be designed to function correctly with multi-record inserts.

    No exceptions.

    This is the method you want to use for your insert trigger:
    Code:
    CREATE TRIGGER [InForRpt] ON [dbo].[Table1]
    FOR INSERT
    
    AS
    begin
    insert into Table2
    	(cFinYrs,
    	cLocCode,
    	cMonth,
    	iSrNo,
    	cItemCode,
    	dQty,
    	dRate,
    	cDesc,
    	cCreated,
    	dtCreated,
    	cModified,
    	dtModified,
    	cMachIP)
    select	inserted.cFinYrs,
    	inserted.cLocCode,
    	inserted.cMonth,
    	inserted.iSrNo,
    	inserted.cItemCode,
    	inserted.dQty,
    	ssstockmst.dRate,
    	inserted.cDesc,
    	inserted.cCreated,
    	inserted.dtCreated,
    	inserted.cModified,
    	inserted.dtModified,
    	inserted.cMachIP
    from	inserted
    	left outer join ssstockmst on inserted.cItemCode = ssstockmst.cItemCode
    end
    Much simpler, eh?
    Now, I really recommend that you go back to Books Online and read the sections on triggers, paying careful attention to the examples given.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115
    Quote Originally Posted by blindman
    This still makes no sense. Why not just add the column to Table1? Are you dealing with a reduced record set in table2? Is that data truncated occasionally, or filtered? We need to know how that data is being retained before helping you create Update/Delete triggers.
    I thought to add new column in the Table1 but Table1 is being used & lots of data in the table1. Second thing, I have to think of the forntend application too.

    We found best solution for a while is to create a new table for same & we will update the table1 later, when we upgrade our database & application.

    No, data is truncated...

    Thanx blindman, for simplify insert trigger...

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by rajeshpatel
    I thought to add new column in the Table1 but Table1 is being used & lots of data in the table1. Second thing, I have to think of the forntend application too.
    Still makes no sense. You're taking up extra space by storing redundant data in table2, extra processing time by keeping the data synchronized, extra development time in setting up this process, and a properly designed front-end won't care or even know that you've added an extra column to the table.
    You need a DBA to help you with this project...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115
    I have solved the problem.

    1. I have created a surrogate key (combination of cFinYrs, cLocCode, cMonth & cItemCode) on Table1 and accordingly foreign key on Table2.

    2. Set cascade for update & delete.

    I have test it, it's working fine.

    I am understanding what you want to say but right now I am not allowed to modify working table's structure. Surely I will do it but later.

    Thanx blindman for all efforts you placed....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •