trigger to keep tables matching

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

trigger to keep tables matching

Author

Topic

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2004-09-20 : 15:13:56

I have two tables in different databases that I need to contain exactly the same data at all times. I've tried setting up a trigger so that every time one of the tables is changed, a trigger fires that deletes all rows from the counterpart, then inserts into it everything in the table that was changed originally. The problem is that it the triggers end up calling each other back and forth, and either the data doesn't get changed in one table or both end up empty. I've tried @@nestlevel but couldn't get it to work right. Is there a better solution to this that I'm not thinking of?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-20 : 15:23:00

Why not use transactional replication?

Tara

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2004-09-20 : 15:57:29

It seems like overkill for one set of two tables, but you're the warrior princess. Plus I don't know how.

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-20 : 16:09:22

Why do the DELETE? For the Primary Key?

Are there any other relationships to the tables?

Can you post the DDL and the triggers?

bi-directional updates are a pain...

Brett

8-)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-20 : 16:11:48

Deleting ALL the rows from the one table, then reinserting ALL the rows from the first table, is not overkill

!!!

In one database create a view that reads from the designated "master" database where the table is actually stored.
Just decide in which db the physical table is to be stored.
You can do updates and inserts through this view.

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-20 : 16:44:23

I'm getting a sql server error...connection broken..I'll look at it in the AM, but I was thinking along the lines of...


SET NOCOUNT ON
GO

USE Northwind
GO
CREATE TABLE myTable99 (Col1 int PRIMARY KEY, Col2 char(1))
GO

USE Pubs
GO
CREATE TABLE myTable99 (Col1 int PRIMARY KEY, Col2 char(1))
GO

CREATE TRIGGER myTrigger99 ON myTable99
FOR INSERT, UPDATE, DELETE
AS

IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted) -- INSERT
  BEGIN
	UPDATE l
	   SET Col2 = r.Col2
	 FROM Northwind.dbo.myTable99 l
	 JOIN inserted r 
	   ON l.Col1 = r.Col1

	INSERT INTO Northwind.dbo.myTable99(Col1, Col2)
	     SELECT Col1, Col2 FROM inserted o
	      WHERE NOT EXISTS (SELECT * FROM Northwind.dbo.myTable99 i WHERE i.Col1 = o.Col1)
  END

IF NOT EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) -- DELETE
  BEGIN
	DELETE Northwind.dbo.myTable99
	WHERE Col1 IN (SELECT Col1 FROM inserted) 
  END

IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) -- UPDATE
  BEGIN
	UPDATE l
	   SET Col2 = r.Col2
	 FROM Northwind.dbo.myTable99 l
	 JOIN inserted r 
	   ON l.Col1 = r.Col1
  END
GO

USE Northwind
GO

CREATE TRIGGER myTrigger99 ON myTable99
FOR INSERT, UPDATE, DELETE
AS

IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted) -- INSERT
  BEGIN
	UPDATE l
	   SET Col2 = r.Col2
	 FROM Pubs.dbo.myTable99 l
	 JOIN inserted r 
	   ON l.Col1 = r.Col1

	INSERT INTO Pubs.dbo.myTable99(Col1, Col2)
	     SELECT Col1, Col2 FROM inserted o
	      WHERE NOT EXISTS (SELECT * FROM Pubs.dbo.myTable99 i WHERE i.Col1 = o.Col1)
  END

IF NOT EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) -- DELETE
  BEGIN
	DELETE Pubs.dbo.myTable99
	WHERE Col1 IN (SELECT Col1 FROM inserted) 
  END

IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) -- UPDATE
  BEGIN
	UPDATE l
	   SET Col2 = r.Col2
	 FROM Pubs.dbo.myTable99 l
	 JOIN inserted r 
	   ON l.Col1 = r.Col1
  END
GO

INSERT INTO Northwind.dbo.myTable99(Col1, Col2)
SELECT 1, 'A' UNION ALL
SELECT 2, 'B' UNION ALL
SELECT 3, 'C'
GO

SELECT * FROM Northwind.dbo.myTable99
SELECT * FROM Pubs.dbo.myTable99

USE Northwind
DROP TRIGGER myTrigger99
DROP TABLE myTable99
GO
USE Pubs
DROP TRIGGER myTrigger99
DROP TABLE myTable99
GO
SET NOCOUNT OFF
GO

Brett

8-)

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2004-09-20 : 17:05:56

I think your idea is good Brett, but won't that only work for inserts and deletions, not updates?
I'm intrigued by your idea rockmoose, but I don't really understand it. I didn't build the app that the "master" db is for, so I can't use a view, so I'm guessing I can't implement your idea anyway.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-20 : 17:18:52

Why can't you use a view in one of the databases ?
Let's pretend in the example below that Northwind is "master" and pubs uses a view to get the physically stored data.

( borrowed your tables Brett ;) )

SET NOCOUNT ON
GO

USE Northwind
GO
CREATE TABLE myTable99 (Col1 int PRIMARY KEY, Col2 char(8))
GO

USE Pubs
GO
CREATE VIEW myTable99 AS SELECT Col1, Col2 FROM Northwind.dbo.myTable99
GO

USE Northwind
GO
INSERT myTable99(Col1, Col2)
SELECT 1, 'nw' UNION ALL
SELECT 2, 'nw' UNION ALL
SELECT 3, 'nw'

USE Pubs
GO
INSERT myTable99(Col1, Col2)
SELECT 4, 'pb' UNION ALL
SELECT 5, 'pb' UNION ALL
SELECT 6, 'pb'

USE Northwind
GO
UPDATE myTable99 SET Col2 = 'newnw' WHERE Col1 = 5

USE Pubs
GO
UPDATE myTable99 SET Col2 = 'newpb' WHERE Col1 = 2

SELECT * FROM Northwind.dbo.myTable99
SELECT * FROM Pubs.dbo.myTable99

USE Northwind
GO
DROP TABLE myTable99
GO
USE Pubs
GO
DROP VIEW myTable99
GO




rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2004-09-20 : 17:35:51

Two problems. One is that I cannot have a view in either database (an expensive 3rd party app was built on the master db and must use the table, and the slave db has tables connected to the replica table in it for foreign key references). Unless you're saying the view would be temporary, I don't really understand your insertion statements.
Two is that there are so many columns in the update that it would be a pain to specify all of them in the query.

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2004-09-20 : 17:54:38

Here is what I'm trying to do (just because I'm a U.S. citizen doesn't mean I ignore good suggestions (hehe) so don't get frustrated rockmoose).

CREATE TRIGGER trUpdate ON [intercept].[Contact]
FOR INSERT, UPDATE, DELETE
AS
IF @@nestlevel > [not sure what value to use here]
BEGIN
return
END
delete pricelist..vicontacts
insert into pricelist..vicontacts
select * from intercept..contact

CREATE TRIGGER trUpdate ON [Pricelist].[Contact]
FOR INSERT, UPDATE, DELETE
AS
IF @@nestlevel > [not sure what value to use here]
BEGIN
return
END
delete intercept..contact
insert into intercept..contact
select * from pricelist..vicontacts

I think if I knew the proper value of @@nestlevel I might be able to get it to work right. They're relatively small tables, and will continue to be, so overhead is not an issue. thanks all

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-20 : 17:56:47

It was a suggestion and example.

Yes, you will loose referential integrity by using a view - Serious issue.

The insert statements were an example of insertion through the view, as were the update statements to the same.
The thing with the view, is that it is guaranteed to always contain exactly the same data as the table(s) it is based upon.

Too many columns .... to type(specify) !?!?!?!, that is not a plausible argument

I don't know enough about your situation and the business requirements to be able to recommend a solution,
but at least now You have been given a few options for consideration.

Hell bent on triggers
/rockmoose

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-20 : 18:04:06

ALTER TABLE DISABLE TRIGGER trgDontNestMe !?!?!?

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2004-09-20 : 18:22:16

You're a genius rockmoose! That last one was gold. It's not working for deletes, but I should be able to figure that out.

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2004-09-20 : 18:31:42

Are the triggers re-enabled after they are executed?

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-20 : 18:45:15

This should be self-explanatory.
ALTER TABLE ... ENABLE TRIGGER ...

ref. "ALTER TABLE" in BOL

/rockmoose

zzzzzzzzzzzzzzzzzzzzzzz
zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
zzzzzzzzzzzzzz

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2004-09-20 : 18:52:43

Here's what I have now (it works at first, but then the triggers stay disabled ??):

--START OF TRIGGER 1
CREATE TRIGGER trUpdate ON [dbo].[VIContacts]
FOR INSERT, UPDATE, DELETE
AS
alter table intercept..contact disable trigger all
delete intercept..contact
insert into intercept..contact
select *
from pricelist..vicontacts
alter table pricelist..vicontacts enable trigger all
--END OF TRIGGER 1

--START OF TRIGGER 2
CREATE TRIGGER trUpdate ON [dbo].[Contact]
FOR INSERT, UPDATE, DELETE
AS
alter table pricelist..vicontacts disable trigger all
delete pricelist..vicontacts
insert into pricelist..vicontacts
select *
from intercept..contact
alter table pricelist..vicontacts enable trigger all
--END OF TRIGGER 2

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-20 : 19:01:34

I just think you got the logic a bit messed up...
Will this help ?

quote:
Originally posted by influent

Here's what I have now (it works at first, but then the triggers stay disabled ??):'

--START OF TRIGGER 1
CREATE TRIGGER trUpdate ON [dbo].[VIContacts]
FOR INSERT, UPDATE, DELETE
AS
alter table intercept..contact disable trigger all
delete intercept..contact
insert into intercept..contact
select *
from pricelist..vicontacts
alter table intercept..contact enable trigger all
--END OF TRIGGER 1

--START OF TRIGGER 2
CREATE TRIGGER trUpdate ON [dbo].[Contact]
FOR INSERT, UPDATE, DELETE
AS
alter table pricelist..vicontacts disable trigger all
delete pricelist..vicontacts
insert into pricelist..vicontacts
select *
from intercept..contact
alter table pricelist..vicontacts enable trigger all
--END OF TRIGGER 2

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-20 : 19:05:35

Just a question ---
How are the foreign key references handling the delete ?

/rockmoose

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-20 : 19:18:56

Anyway, I think the approach is very coarse, deleting and reinserting all the rows in the table for every change in the data.
Also that would break quite a lot of constraints in the database, if there are any.

I don't really understand why you have to replicate/synch/match just one table, and not the others.

Oh well, it's late ... here ......

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-21 : 02:51:11

not sure if suggestions are still welcome...

you want to have bidirectional changes from tbl1 to tbl2 using triggers?

i think your problem lies on this premise (using "bi-directional" triggers).

IMHO,you may need another table (tblStage) for that, monitoring which data is being modified, inserted or deleted

if tbl1 gets modified, then the trigger updates tblStage, apply the changes to tbl1, delete the committed rows from tblStage, and vice versa.

You'll save some overhead on deleting everything and locking the tables at the same time.

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-21 : 11:40:07

Here it is.....never had much use for it, but I always wanted to do this...here you go...



SET NOCOUNT ON
GO

USE Northwind
GO
CREATE TABLE myTable99 (
	  Col1 int NOT NULL PRIMARY KEY
	, Col2 char(1)
	, ModifiedBy varchar(25) NOT NULL
	, ModifiedDate Datetime NOT NULL DEFAULT GetDate())
GO

USE Pubs
GO
CREATE TABLE myTable99 (
	  Col1 int NOT NULL PRIMARY KEY
	, Col2 char(1)
	, ModifiedBy varchar(25) NOT NULL
	, ModifiedDate Datetime NOT NULL DEFAULT GetDate())
GO

CREATE TRIGGER myTrigger99 ON myTable99
FOR INSERT, UPDATE, DELETE
AS

IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted) -- INSERT
  BEGIN
		-- This should never execute if the tables are in synch
	     UPDATE l 
		SET Col2 = r.Col2, ModifiedBy = 'TRIGGER', ModifiedDate = GetDate()
	       FROM Northwind.dbo.myTable99 l
	       JOIN inserted r 
		 ON l.Col1 = r.Col1
		AND r.ModifiedBy <> 'TRIGGER'

		-- The need for the JOIN should be not required in the tables are in synch
	INSERT INTO Northwind.dbo.myTable99(Col1, Col2, ModifiedBy)
	     SELECT l.Col1, l.Col2, 'TRIGGER' FROM inserted l
	  LEFT JOIN Northwind.dbo.myTable99 r 
		 ON l.Col1 = r.Col1 
	      WHERE r.Col1 IS NULL
		AND l.ModifiedBy <> 'TRIGGER'
  END

IF NOT EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) -- DELETE
  BEGIN

	DELETE Northwind.dbo.myTable99
	WHERE Col1 IN (SELECT Col1 FROM deleted 
--			WHERE ModifiedBy <> 'TRIGGER'
		      ) 
  END

IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) -- UPDATE
  BEGIN
	UPDATE l
	   SET Col2 = r.Col2, ModifiedBy = 'TRIGGER', ModifiedDate = GetDate()
	 FROM Northwind.dbo.myTable99 l
	 JOIN inserted r 
	   ON l.Col1 = r.Col1
	  AND r.ModifiedBy <> 'TRIGGER'
  END
GO

USE Northwind
GO

CREATE TRIGGER myTrigger99 ON myTable99
FOR INSERT, UPDATE, DELETE
AS

IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted) -- INSERT
  BEGIN
	UPDATE l
	   SET Col2 = r.Col2, ModifiedBy = 'TRIGGER', ModifiedDate = GetDate()
	 FROM Pubs.dbo.myTable99 l
	 JOIN inserted r 
	   ON l.Col1 = r.Col1
	  AND r.ModifiedBy <> 'TRIGGER'
 
	INSERT INTO Pubs.dbo.myTable99(Col1, Col2, ModifiedBy)
	     SELECT l.Col1, l.Col2, 'TRIGGER' FROM inserted l
	      LEFT JOIN Pubs.dbo.myTable99 r 
		     ON l.Col1 = r.Col1 
		  WHERE r.Col1 IS NULL
	            AND l.ModifiedBy <> 'TRIGGER'
  END

IF NOT EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) -- DELETE
  BEGIN
	DELETE Pubs.dbo.myTable99
	 WHERE Col1 IN (SELECT Col1 FROM deleted 
--			 WHERE ModifiedBy <> 'TRIGGER'
			) 
  END

IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) -- UPDATE
  BEGIN
	UPDATE l
	   SET Col2 = r.Col2, ModifiedBy = 'TRIGGER', ModifiedDate = GetDate()
	 FROM Pubs.dbo.myTable99 l
	 JOIN inserted r 
	   ON l.Col1 = r.Col1
	  AND r.ModifiedBy <> 'TRIGGER'
  END
GO

INSERT INTO Northwind.dbo.myTable99(Col1, Col2, ModifiedBy)
SELECT 1, 'A', 'Brett' UNION ALL
SELECT 2, 'B', 'Brett' UNION ALL
SELECT 3, 'C', 'Brett'
GO

SELECT * FROM Northwind.dbo.myTable99
SELECT * FROM Pubs.dbo.myTable99
GO

UPDATE Northwind.dbo.myTable99 SET Col2='X', ModifiedBy = 'BrettK', ModifiedDate = GetDate() 
 WHERE Col1 = 2
GO

SELECT * FROM Northwind.dbo.myTable99
SELECT * FROM Pubs.dbo.myTable99
GO

INSERT INTO Pubs.dbo.myTable99(Col1, Col2, ModifiedBy)
SELECT 4, 'D', 'Brett'
GO

SELECT * FROM Northwind.dbo.myTable99
SELECT * FROM Pubs.dbo.myTable99
GO

DELETE FROM Pubs.dbo.myTable99 WHERE Col1 = 1
GO

SELECT * FROM Northwind.dbo.myTable99
SELECT * FROM Pubs.dbo.myTable99
GO

DELETE FROM Northwind.dbo.myTable99 WHERE Col1 = 4
GO

SELECT * FROM Northwind.dbo.myTable99
SELECT * FROM Pubs.dbo.myTable99
GO

UPDATE Northwind.dbo.myTable99 SET Col2='X', ModifiedBy = 'BrettX', ModifiedDate = GetDate() 
 WHERE Col1 = 3
GO

SELECT * FROM Northwind.dbo.myTable99
SELECT * FROM Pubs.dbo.myTable99
GO

USE Northwind
DROP TRIGGER myTrigger99
DROP TABLE myTable99
GO
USE Pubs
DROP TRIGGER myTrigger99
DROP TABLE myTable99
GO
SET NOCOUNT OFF
GO

Brett

8-)

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources