Triggers - Copying table from one d/b to another after any changes

[MikeMcKeown]

Hi all,

I have 6 tables which are to be shared between two databases. The input to these will occur through two seperate adp Access projects.

i believe that you can do this with triggers - when anything changes in one table -update the other tables across d/b on the same server.

When a change occurs do you have to copy the whole table over or is there away to amend the individual records where the change has occured? As I thought the code would look like this:-

CREATE TRIGGER [Updated_Corresponding] ON [dbo].[Withdrawn]
FOR INSERT
AS
INSERT INTO [dbo].[Temp_A].[Withdrawn]
( [no] and all other fields)
SELECT no FROM [dbo].[Temp_C].[Withdrawn]

Thanks in advance

 

[mrdenny]

No, you can work with only the records that were changed in that transaction by selecting from the virtual table inserted instead of your table name. You code should look like this.

CREATE TRIGGER [Updated_Corresponding] ON [dbo].[Withdrawn]
FOR INSERT
AS
INSERT INTO [COLOR=red][Temp_A][/color].[dbo].[Withdrawn]
 ( [no] and all other fields)
SELECT no FROM [COLOR=red][Temp_C][/color].[dbo].[inserted]

The database name is the first part of the 3 part name, with the owner (dbo) being the second. You had this reversed.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[MikeMcKeown]

I have managed to copy information from one table when a record is entered to another table in another database using a trigger.

Both tables are idential in the different datbases. I am now however having a problem with SET identity property. In each of the tables there is a identity field named Entry_Number identity (1,1)

If I am in database one and insert a record _ i get an error message saying that it cannot insert as the identity property is not set for the table in database two.

SET IDENTITY_INSERT [Temp_Database2].[dbo].[Practice] ON

Then it says the one in the other table is not set

SET IDENTITY_INSERT [Temp_Database1].[dbo].[Practice] ON

Then it says they are both set and I can not update.

Any ideas - I thought if I just set both of them at the start of the trigger.


CREATE trigger Tg_Changes_Withdrawn on dbo.Withdrawn
for insert, update , delete
as
SET IDENTITY_INSERT [Temp_Database1].[dbo].[Withdrawn] ON
insert into [Temp_Database1].[dbo].[Withdrawn]
(Entry_Number,
cis_no,
Withdraw_A,
Date_A_Withdraw,
Withdraw_C,
Date_C_Withdraw

)
Select Entry_Number,
cis_no,
Withdraw_A,
Date_A_Withdraw,
Withdraw_C,
Date_C_Withdraw from inserted

go

Thanks

 

[MikeMcKeown]

The triggers are going into a loop as they are both executing each time. I will have to set the opposite one off before I excute the current one.