Help With Update + Insert Trigger

[HenryE]

Hello Everyone,

I am trying to create a trigger that will add or modify data in a table, every time a user adds or modifies data in another table. Any ideas would be greatly appreciate. Here is what I currently have:

-Two databses, call them DB1 and DB2.
-Two tables, each in one of the databases, call them DB1Tbl1 and DB2Tbl2.
-One column for each table, so DB1Tbl1Col1 and DB2Tbl2Col1.
-Every time a user adds or modifies data in DB1Tbl1Col1, this causes the same addition or modification in DB2Tbl2Col1.

I've written a trigger that does the INSERT part fine. It's the UPDATE part that's giving me problems. Here is the code as it now stands (without the UPDATE capability):

CREATE TRIGGER InsCol1Tbl1Tbl2Trig ON dbo.DB1Tbl1
FOR INSERT
AS
INSERT INTO dbo.DB2Tbl2 (DB2Tbl2Col1)
SELECT DB1Tbl1.DB1Tbl1Col1
FROM DB1Tbl1 INNER JOIN Inserted ON DB1Tbl1.DB1Tbl1Col1=Inserted.DB1Tbl1Col1;

Thanks.

Henry

 

[jgoodman00]

I think you will need to seperate triggers to do this. The insert one above, & a second slightly different update one. Because you are doing to different things, it is probably easier to have two seperate triggers.

The update trigger should be something like:
CREATE TRIGGER UpdCol1Tbl1Tbl2Trig ON dbo.DB1Tbl1
FOR UPDATE
AS
UPDATE dbo.DB2Tbl2 (DB2Tbl2Col1)
SET DB2Tbl2.DB2Tbl2Col1 = DB1Tbl1.DB1Tbl1Col1
FROM DB1Tbl1 INNER JOIN Inserted ON DB1Tbl1.DB1Tbl1Col1=Inserted.DB1Tbl1Col1;


I think, although the table/column names are a little confusing & making my head hurt.


James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures