SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 trigger to keep tables matching
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

influent
Constraint Violating Yak Guru

USA
367 Posts

Posted - 09/20/2004 :  15:13:56  Show Profile  Reply with Quote
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

USA
37316 Posts

Posted - 09/20/2004 :  15:23:00  Show Profile  Visit tkizer's Homepage  Reply with Quote
Why not use transactional replication?

Tara
Go to Top of Page

influent
Constraint Violating Yak Guru

USA
367 Posts

Posted - 09/20/2004 :  15:57:29  Show Profile  Reply with Quote
It seems like overkill for one set of two tables, but you're the warrior princess. Plus I don't know how.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/20/2004 :  16:09:22  Show Profile  Reply with Quote
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-)
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 09/20/2004 :  16:11:48  Show Profile  Reply with Quote
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 */
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/20/2004 :  16:44:23  Show Profile  Reply with Quote
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-)
Go to Top of Page

influent
Constraint Violating Yak Guru

USA
367 Posts

Posted - 09/20/2004 :  17:05:56  Show Profile  Reply with Quote
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.

Edited by - influent on 09/20/2004 17:19:45
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 09/20/2004 :  17:18:52  Show Profile  Reply with Quote
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 */
Go to Top of Page

influent
Constraint Violating Yak Guru

USA
367 Posts

Posted - 09/20/2004 :  17:35:51  Show Profile  Reply with Quote
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.
Go to Top of Page

influent
Constraint Violating Yak Guru

USA
367 Posts

Posted - 09/20/2004 :  17:54:38  Show Profile  Reply with Quote
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
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 09/20/2004 :  17:56:47  Show Profile  Reply with Quote
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
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 09/20/2004 :  18:04:06  Show Profile  Reply with Quote
ALTER TABLE DISABLE TRIGGER trgDontNestMe !?!?!?

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */
Go to Top of Page

influent
Constraint Violating Yak Guru

USA
367 Posts

Posted - 09/20/2004 :  18:22:16  Show Profile  Reply with Quote
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.
Go to Top of Page

influent
Constraint Violating Yak Guru

USA
367 Posts

Posted - 09/20/2004 :  18:31:42  Show Profile  Reply with Quote
Are the triggers re-enabled after they are executed?
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 09/20/2004 :  18:45:15  Show Profile  Reply with Quote
This should be self-explanatory.
ALTER TABLE ... ENABLE TRIGGER ...

ref. "ALTER TABLE" in BOL

/rockmoose
zzzzzzzzzzzzzzzzzzzzzzz
zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
zzzzzzzzzzzzzz

Edited by - rockmoose on 09/20/2004 18:46:34
Go to Top of Page

influent
Constraint Violating Yak Guru

USA
367 Posts

Posted - 09/20/2004 :  18:52:43  Show Profile  Reply with Quote
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
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 09/20/2004 :  19:01:34  Show Profile  Reply with Quote
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


Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 09/20/2004 :  19:05:35  Show Profile  Reply with Quote
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 */
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 09/20/2004 :  19:18:56  Show Profile  Reply with Quote
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 ......
Go to Top of Page

jen
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 09/21/2004 :  02:51:11  Show Profile  Send jen a Yahoo! Message  Reply with Quote
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.

Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/21/2004 :  11:40:07  Show Profile  Reply with Quote
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-)
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000