Trigger to sinchronize table in two different data

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 sinchronize table in two different data

Author

Topic

fmenditto
Starting Member

6 Posts

Posted - 2004-06-25 : 10:42:10

I have different database all with a table PEOPLE. In this table there are two field: USER_ID and USER_PASSWORD. I want to write a trigger so when a user changes USER_PASSWORD field in one database I update the same field in other databases. The problem is that the name of databases to update are in another table REMOTE_LIBRARIES. The trigger look like this

CREATE TRIGGER PWD_UPD ON PEOPLE
FOR UPDATE
AS

UPDATE t1
SET t1.USER_PASSWORD = i.USER_PASSWORD
FROM a..PEOPLE t1
INNER JOIN inserted i
ON t1.USER_ID = i.USER_ID

where a is the result of
SELECT DATABASE_NAME FROM REMOTE_LIBRARIES a WHERE SYSTEM_ID <> 0

How can I use the result of the SELECT in UPDATE query trigger?

Thanks to all

Kristen
Test

22859 Posts

Posted - 2004-06-25 : 12:25:42

Dunno if this will work, but might be worth a shot


DECLARE @sqlSQL varchar(8000)
SET @sqlSQL = ''

SELECT @strSQL = @strSQL + 
'UPDATE t1
SET t1.USER_PASSWORD = ''' + i.USER_PASSWORD + '''
FROM ' + DATABASE_NAME + '..PEOPLE t1
WHERE USER_ID = ''' + USER_ID + ''''
+ CHAR(13)+CHAR(10)+'GO'+CHAR(13)+CHAR(10)
FROM inserted i

EXEC (@strSQL)

Kristen

fmenditto
Starting Member

6 Posts

Posted - 2004-06-25 : 13:00:03

Hy Kristen,
the problem is that DATABASE_NAME is a field result form a query
SELECT DATABASE_NAME FROM REMOTE_LIBRARIES WHERE SYSTEM:ID <> 0. The result of this query my be more records.

quote:

Originally posted by Kristen

Dunno if this will work, but might be worth a shot


DECLARE @sqlSQL varchar(8000)
SET @sqlSQL = ''

SELECT @strSQL = @strSQL + 
'UPDATE t1
SET t1.USER_PASSWORD = ''' + i.USER_PASSWORD + '''
FROM ' + DATABASE_NAME + '..PEOPLE t1
WHERE USER_ID = ''' + USER_ID + ''''
+ CHAR(13)+CHAR(10)+'GO'+CHAR(13)+CHAR(10)
FROM inserted i

EXEC (@strSQL)

Kristen

fmenditto
Starting Member

6 Posts

Posted - 2004-06-25 : 13:08:39

I try with the below but I receive an error because che SELECT DATABASE_NAME FROM REMOTE_LIBRARIES WHERE SYSTEM_ID <> 0 query
retunr more than one result ...

ALTER TRIGGER [DOCSADM].PWD_UPD ON [DOCSADM].[PEOPLE]
FOR UPDATE
AS

DECLARE @strSQL varchar(8000)
SET @strSQL = ''

SELECT @strSQL = @strSQL +
'UPDATE t1
SET t1.USER_PASSWORD = ''' + i.USER_PASSWORD + '''
FROM ' + (SELECT DATABASE_NAME FROM REMOTE_LIBRARIES WHERE SYSTEM_ID <> 0)
+ '..PEOPLE t1
WHERE t1.USER_ID = ''' + i.USER_ID + ''''
+ CHAR(13)+ CHAR(10) + 'GO' + CHAR(13)+ CHAR(10)
FROM inserted i

EXEC (@strSQL)

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-25 : 13:18:27

You need to select the name of the databases into a table variable with an identity column. You can then loop through the table and select one database at a time to update.

Or better yet, far better, just write two update statements and don't use dynamic SQL at all.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Kristen
Test

22859 Posts

Posted - 2004-06-25 : 14:05:47

quote:
Originally posted by fmenditto

the problem is that DATABASE_NAME is a field result form a query
SELECT DATABASE_NAME FROM REMOTE_LIBRARIES WHERE SYSTEM:ID <> 0. The result of this query my be more records.

Sorry, wasn't thinking properly. The sun is already over the yardarm here!


DECLARE @sqlSQL varchar(8000)
SET @sqlSQL = ''

SELECT @strSQL = @strSQL + 
'UPDATE t1
SET t1.USER_PASSWORD = ''' + i.USER_PASSWORD + '''
FROM ' + DATABASE_NAME + '..PEOPLE t1
WHERE USER_ID = ''' + USER_ID + ''''
	+ CHAR(13)+CHAR(10)+'GO'+CHAR(13)+CHAR(10)
FROM	inserted I,
	REMOTE_LIBRARIES A
WHERE	SYSTEM_ID <> 0

EXEC (@strSQL)

This will gernate a carteian resultset on INSERTED and REMOTE_LIBRARIES so better that there are not too many rows in either table!

The use of dynamic SQL here is NOT A GOOD IDEA as other have/will point out, but maybe it gets you going for now.

Kristen

fmenditto
Starting Member

6 Posts

Posted - 2004-06-29 : 04:44:34

Hy Kristen, now the trigger is ok. The last code is bottom. I write instruction for disable trigger so there are no triggers that call each other. But one people tell me why I don't write a procedure in VB and after I call the procedure in trigger with xp_xmdshell. For your experience wich is the best method ?

Thanks

ALTER TRIGGER [DOCSADM].PWD_UPD ON [DOCSADM].[PEOPLE]
FOR UPDATE
AS

IF UPDATE (USER_PASSWORD)
BEGIN
DECLARE @strSQL varchar(8000)
SET @strSQL = ''
SELECT @strSQL = @strSQL +
'ALTER TABLE ' + a.DATABASE_NAME + '.DOCSADM.PEOPLE DISABLE TRIGGER PWD_UPD' + CHAR(13) + CHAR(10)
FROM inserted i, [DOCSADM].REMOTE_LIBRARIES a
WHERE a.SYSTEM_ID <> 0
EXEC (@strSQL)

SET @strSQL = ''
SELECT @strSQL = @strSQL +
'UPDATE t1
SET t1.USER_PASSWORD = ''' + i.USER_PASSWORD + '''
FROM ' + a.DATABASE_NAME + '.DOCSADM.PEOPLE t1
WHERE t1.USER_ID = ''' + i.USER_ID + '''' + CHAR(13) + CHAR(10)
FROM inserted i, [DOCSADM].REMOTE_LIBRARIES a
WHERE a.SYSTEM_ID <> 0
EXEC (@strSQL)

SET @strSQL = ''
SELECT @strSQL = @strSQL +
'ALTER TABLE ' + a.DATABASE_NAME + '.DOCSADM.PEOPLE ENABLE TRIGGER PWD_UPD' + CHAR(13) + CHAR(10)
FROM inserted i, [DOCSADM].REMOTE_LIBRARIES a
WHERE a.SYSTEM_ID <> 0
EXEC (@strSQL)
END

Kristen
Test

22859 Posts

Posted - 2004-06-29 : 09:43:28

If you use xp_xmdshell it will be much slower. Also if your VB code crashes it is likely to crash SQL server - not good!

Disabling the trigger on the other table looks a bit risky to me! but I expect you know the structure of your database well enough. Could someone else updating that table direct, at the same time, be allowed to make an update and bypass the trigger whilst this trigger was running?

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-29 : 10:55:24

I wonder what a listener would see?

Brett

8-)

fmenditto
Starting Member

6 Posts

Posted - 2004-06-29 : 13:55:46

The problem is that one person can access to different database with the same USER_ID. All databases have the same table.
If the person change the password in one database I want that this change is made on all databases. For this I created a trigger
on PEOPLE table of every databases. If people is working on database A and changes password this change is made also in database B. But the same is if the person works on database B. I disable the trigger so when the trigger changes the password on the database B don't activate the same trigger on database B for change again the password on database A.

Kristen
Test

22859 Posts

Posted - 2004-06-29 : 14:06:36

Ah, I see, I've got it now.

Dunno if its a good idea, but maybe you could replace the PEOPLE table with a VIEW which says


CREATE VIEW PEOPLE
AS
SELECT *   -- Use a full column list, not "*" :)
FROM   MyCentralDB.dbo.PEOPLE
GO

and put this in every database (EXCEPT "MyCentralDB") and then when they UPDATE that, or SELECT even, they see the data from the central table.

Kristen

fmenditto
Starting Member

6 Posts

Posted - 2004-06-30 : 04:06:54

The database structure is the same for every databases but the data inside the table are not the same. The PEOPLE table contain the people that can access to the database. Some people, but not all, can acces more databases with the same account. For this reason is not possible to create one only PEOPLE table on master_database: don't exist a master_database !

P.S. Sorry for my bad english but I'm Italian ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources