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...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?|||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.|||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.|||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|||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...|||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. :)|||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: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
endMuch 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.|||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...|||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...|||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...