SEO

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.

Sincerely,

Nils Hagner
nils.hagner@3dio.co.uk

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)

Thanks Hugo,

I know that the example looks a bit strange. Basically, one table is
owned and controlled by not only another application but another
organisation (our service provider). The way of integrating my
app/table (which is hosted by the service provider) and theirs is by
linking these tables. The natural thing would be to use referential
integrity between the tables, but for commercial/political reasons,
they need to be "galvanically separated." I would rather try to
convince them to put a trigger in place rather than me having to write
an application which does the syncing of the tables.

Sincerely,

Nils