Sync (2) Tables with Triggers Insert/Update/Delete 1

[smedvid]

Greetings... I am looking for some help creating triggers under SQL Server. I have (2) Tables that must remain in sync at all times. The user will only be updating 1 table, the master (Table1). When changes (add/edit/delete) are made to the master table (Table1), I need to same exact changes made in the backup table (Table2).

Table1 and Table2 have the exact same structure with each table containing (5) fields. So, I believe I need (3) Triggers: AFTER INSERT, AFTER UPDATE and AFTER DELETE.

Any assistance is appreciated. tia,

Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[evaleah]

If the user will be updating exclusively Table1 then you will only need to put the trigger there.

And yes, if the actions are different for each (Insert, Update, Delete) then you should write three different triggers.

 

[smedvid]

Correct... I am looking to acquire some example of T-SQL for each of the Triggers. I have surf'd around and found a couple of examples, that are not very clear.

Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[evaleah]

ah... ok, examples. I will do my best here, but I am not promising that I will be any clearer

Insert:

CREATE TRIGGER trAutoInsert
ON Table1
FOR Insert

DECLARE @var1 /*Create a bunch of variables here to hold
  all the data you are getting from the Table1 insert to 
  match Table2 */

SELECT @var1 = Field1 FROM Inserted
  /* Here again I am only listing one field.  But you would
  fill all your fields with the appropriate variable 
  assigned from the Inserted table */

INSERT Table 2 (
  Field1
) VALUES (
  @var1
)  /* One more time, note only one field and variable.
     You should fill as needed. */

The next one for updating will look almost the same accept that you will use an update statement instead of an insert one:

CREATE TRIGGER trAutoUpdate
ON Table1
FOR Update

DECLARE @var1 varchar(10)
DECLARE @varID bigint
  /*Create a bunch of variables here to hold
  all the data you are getting from the Table1 to 
  match Table2 */

SELECT @var1 = Field1, @varID = ID FROM Inserted
  /* Here again I am only listing 2 fields.  But you would
  fill all your fields with the appropriate variable 
  assigned from the Inserted table */

UPDATE Table 2 SET
  Field1 = @var1
WHERE ID = @varID  /* One more time, note only 2 fields and variable. You should fill as needed. */

Finally, if you are deleting an entire record it would look like the following:

CREATE TRIGGER trAutoDelete
ON Table1
FOR Delete

DECLARE @varID bigint

SELECT @varID = ID FROM Deleted

DELETE FROM Table2 WHERE ID = @varID

HTH

Eva

 

[SQLSister]

evaleah, you should not use those examples as they assume that only one record at atime will be inserted. Triggers should never be written with this assumption. This will sooner or later break you application.

Some sample code to update table1 from some other table.

Update table1
Set Field1 = a.field1
From table1 join inserted on table1.IDField = inserted.IDField

Insert table1 (field1, Field2, field3, idfield)
Select Field1, field2, field3, Idfield from inserted

Delete table1
From Table1 join deleted on table1.idfield = deleted.idfield

Questions about posting. See faq183-874

 

[smedvid]

Excellent... Piece of Cake... Thanks to both of you. Steve

Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[smedvid]

For the Update... IS there a method to permit the updating of a key field. For example,

Table1:
TEST1 CHAR(10)
TEST2 CHAR(10)

Table2:
TEST1 CHAR(10)
TEST2 CHAR(10)

Where a user updates both TEST1 and TEST2 fields in Table1, I would like to somehow update the corresponding record in Table2. In this example, the field TEST1 is the pseudo key unique field. tia,



Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[SQLSister]

As long as the field is not an identity field in the second table (which it should not be if this table is only updated from table1), the normal update statment should work. The uniqueness should not be a problem or it wouldn't have inserted into table1 to begin with.

Questions about posting. See faq183-874