| Author |
 Topic  |
|
influent
Constraint Violating Yak Guru
USA
367 Posts |
 Posted - 09/20/2004 : 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
USA
38200 Posts |
Posted - 09/20/2004 : 15:23:00
|
Why not use transactional replication?
Tara |
 |
|
|
influent
Constraint Violating Yak Guru
USA
367 Posts |
Posted - 09/20/2004 : 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 - 09/20/2004 : 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
Sweden
3279 Posts |
Posted - 09/20/2004 : 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 - 09/20/2004 : 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
USA
367 Posts |
Posted - 09/20/2004 : 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. |
Edited by - influent on 09/20/2004 17:19:45 |
|
|
|
rockmoose
SQL Natt Alfen
Sweden
3279 Posts |
Posted - 09/20/2004 : 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
USA
367 Posts |
Posted - 09/20/2004 : 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
USA
367 Posts |
Posted - 09/20/2004 : 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
Sweden
3279 Posts |
Posted - 09/20/2004 : 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
Sweden
3279 Posts |
Posted - 09/20/2004 : 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
USA
367 Posts |
Posted - 09/20/2004 : 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
USA
367 Posts |
Posted - 09/20/2004 : 18:31:42
|
| Are the triggers re-enabled after they are executed? |
|
|
|
rockmoose
SQL Natt Alfen
Sweden
3279 Posts |
Posted - 09/20/2004 : 18:45:15
|
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 |
|
|
|
influent
Constraint Violating Yak Guru
USA
367 Posts |
Posted - 09/20/2004 : 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
Sweden
3279 Posts |
Posted - 09/20/2004 : 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
Sweden
3279 Posts |
Posted - 09/20/2004 : 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
Sweden
3279 Posts |
Posted - 09/20/2004 : 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
Flowing Fount of Yak Knowledge
Sweden
4110 Posts |
Posted - 09/21/2004 : 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 - 09/21/2004 : 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-) |
|
|
|
Topic |
|
trigger to keep tables matching