On 27 Mar 2005 11:21:31 -0800, nils.hagner@3dio.co.uk wrote:

>I haven't used triggers before, but I suppose they would solve the
>problem I am having. If anyone can give me some advice on this, then I
>would be very grateful.
>
>The problem is that I need to "sync" two tables used by two different
>applications. Assume, for example, that the tables are defined as:
>table1(A, B, C, D, E) with A being PK, and
>table2(A, B, D) again with col A as PK.
>
>When a record is inserted or updated in table2, the following should
>happen:
>
>(1) if there already is a row in table1 with the same key (i.e.
>table1.A = table2.A), then I want to update table1 to set
>table1.B = table2.B and table1.D = table2.D
>
>(2) if there is no row in table1 with that key, then a row
>should be inserted with values (A, B, null, D, null)
>
>The DB is SQL Server 7.0. All help is highly appreciated.

Hi Nils,

The first question should be WHY you need to store the same data in two
tables. Usually, this kind of design is begging for loss of data
integrity.

Assuming that there is a valid reason in your case, then the rough
outline of your trigger would be:

CREATE TRIGGER MyTrigger
ON Table2
AFTER INSERT, UPDATE
AS
-- No rows affected? Then bail out
IF @@ROWCOUNT = 0 RETURN
-- Check that key column is not touched
-- in any update operation
IF UPDATE(A)
AND EXISTS (SELECT * FROM deleted)
BEGIN
RAISERROR ('Don''t update the primary key - it would confuse the
trigger', 16, 1)
ROLLBACK TRANSACTION
RETURN
END

-- Update existing rows with new data
-- uses proprietary T-SQL syntax - quicker, but not portable!
UPDATE t
SET t.B = i.B
, t.D = i.D
FROM inserted AS i
INNER JOIN table1 AS t
ON t.A = i.A

-- Insert rows that didn't exist yet
-- could also use left outer join instead of not exists
INSERT INTO table1 (A, B, C, D, E)
SELECT i.A, i.B, NULL, i.D, NULL
FROM inserted AS i
WHERE NOT EXISTS
(SELECT *
FROM table1 AS t
WHERE t.A = i.A)
go

It's untested and you might want to add error handling.

You should also think about how table1 should change if rows are deleted
from table2, and you should consider what to do if someone changes
table2.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)