asked on

Use Tables from another database in SQL SERVER

Can I do thi sin a trigger?
there is no error but My Data wouldnt update when the ID is not in current DB (SRS2) , but if the ID is in current DB (SRS2)it will update ?
Any adea?
IF ID Exists in SRS2 update will perform ,
If ID not EXISTS in SRS2 we should search in SSRS1 that have the ID and fid the name but update doesnt perform in this situation.

IF UPDATE(RoutingID)
BEGIN
	DECLARE @RoutingName nvarchar(50)
	DECLARE @ORG char(10)
	DECLARE @DEST char(10)
	DECLARE @RoutingID Decimal 
 
	SET  @RoutingID = (SELECT RoutingID FROM INSERTED)
 
	IF EXISTS ((SELECT TOP (1) RoutingName FROM [SRS2].[dbo].vw_AVI WHERE @RoutingID = @RoutingID)  )
		BEGIN
			SET  @RoutingName = (SELECT TOP (1) RoutingName FROM [SRS2].[dbo].vw_AVI WHERE RoutingID = @RoutingID) 
			SET  @ORG = (SELECT TOP (1) ORG FROM [SRS2].[dbo].vw_AVI WHERE RoutingID = @RoutingID) 
			SET  @DEST = (SELECT TOP (1) DEST FROM [SRS2].[dbo].vw_AVI WHERE RoutingID = @RoutingID) 
 
			update [SRS2].[dbo].vw_AVI set RoutingName = @RoutingName , ORG = @ORG , DEST = @DEST WHERE ClassID = @ClassID
			IF @@ROWCOUNT != 0
				RETURN
			ELSE
				RAISERROR(N'could not update', 16, 1);
		END
	ELSE 
		BEGIN
			SET @RoutingName = (SELECT TOP (1) RoutingName FROM [SRS1].[dbo].Routing WHERE RoutingID = @RoutingID)
			SET @ORG =  SUBSTRING(@RoutingName , 0, CHARINDEX('-', @RoutingName));
			SET @DEST =  SUBSTRING(@RoutingName ,CHARINDEX('-', @RoutingName) ,LEN(@RoutingName) );
			update vw_AVI set RoutingName = @RoutingName , ORG = @ORG , DEST = @DEST WHERE ClassID = @ClassID
			IF @@ROWCOUNT != 0
				RETURN
			ELSE
				RAISERROR(N'could not update', 16, 1);
		END
END

Open in new window

jindalankush

check that , ur linked server are update able or read only

Lord_Death

ASKER

DB SRS2 : vw_AVI (RoutingID , RoutingName,...)
DB SRS1 : Routng(RoutingID , RoutingName, RoutingUserID,RoutingDescription)

if RoutingID is in SRS2.vw_AVI then record's RoutingName will update by trigger
but if RoutingID doesnt exists in if ID is in SRS2.vw_AVI trigger would serach in SRRS1.Routing Table to find the RoutingName , in this situation update wont perform
:(

Mark Wills

Which table is the trigger on ? seems you are qualifying the table to update using a 4 part identifier in the first instance, but not the second... Are you looking at the correct tables ? also vw_ is traditionally a prefix often used for views - are you sure the local vw_AVI is updateable ?

Would be inclined to debug the routine first. Make it a script in a query window and set up the variables for @class_id and @routingid then run it intereactively with a few well chosen print statements in there...

Need to be careful when describing your problem. Take time to read it and double check. e.g.
>> wouldnt update when the ID is not in current DB (SRS2) , but if the ID is in current DB (SRS2)it will update <<

So which one is current ?

Mark Wills

And taking a lesson from my own comment, what I meant was, are you running this from SRS2 or SRS1 ? What does "current" mean ?

Lord_Death

ASKER

if RoutingID is in SRS2.vw_AVI then record's RoutingName will update by trigger (Line 12) but if RoutingID doesnt exists in SRS2.vw_AVI trigger would serach in SRS1.Routing table to find the RoutingName , in this situation update wont perform (Line 24)
& yes the vw_AVI is actually a Table not a view only the name is not properly set.

and also yes I have tested the statements in QueryAnalizer and its Updating but as a trigger it doesnt do anything.

Mark Wills

OK, got the first bit, still do not know where that trigger is and if it is on the table vw_AVI on SRS1 (or SRS2). Mighthave said it, but I still have a block.

Thanks for clarifying the names, was a stab in the dark...

Do you have RECURSION enabled (ie sp_configure option 'server trigger recursion' 1 ) ? have your seen it complain about recursion? Have you RECURSIVE_TRIGGERS option set for that database ?

Lord_Death

ASKER

BTW thx for reply but
SRS2.vw_AVI means that vw_AVI table is in SRS2 database and the trigger is in SRS2 database on vw_AVI table,
and no there was no complain about recursion.

as I said if data exist in table that trigger is fired on update will perform but if data not exist in table that trigger is fired so it should search in another database that has a table with respective data

e.g  DB1 have T1 that have all the data & DB2 have T2 that have an update trigger on T2 so if data is not in DB2 we should look for it in DB1 and in respective table T1

Open in new window

Mark Wills

Hi Lord_Death, yes, understand the multipart table identifier.... in fact, what you have said is technically wrong SRS2.vw_AVI lives in database SRS2 if using MySQL, but in MS SQL it means it lives in schema SRS2 - the MS SQL identifier is a four part beast in the form <server>.<database>.<schema>.<tablename> and the code correctly has the intervening schema name (which in this case is optional) between database and table - ie DBO... But I do undertsand what you were meaning...

The reason for my question was the table in the "else" part of the "if" statement does not have any identifier other than the table itself, and obviosuly will ne updating that table in the "local" database (ie no db identifier). Now it does become important to understand which table that trigger belongs to...

So, you are trying to update the table using a trigger on that table being fired with Inserts (and changes ?) but only if RoutingID is part of the changes, and it exists (or not) in the table that we are currently updating... Which coincidentally will fire a change trigger...

Has all the right ingredience for recursion... so thought it might be a worthwhile ask... but possibly a non-issue if you have not seen any evidence (is trigger for changes as well ?)

Now, there are a couple of "interesting" facts....

1) SET @RoutingID = (SELECT RoutingID FROM INSERTED) -- there can be multiple rows in inserted so this will fail if there is...
2) Here is the real rub, and most likely your problem :
a) Triggers fire after the event - ie data is in the database.
b) if updating routingid - and only checking what to do if routingid is part of the update, then it is already committed
c) it means that routingid will always / already exist in the check ; IF EXISTS ((SELECT TOP (1) RoutingName FROM [SRS2].[dbo].vw_AVI WHERE @RoutingID = @RoutingID) )
d) now, if routingname is only being populated by this trigger (and ORG and DEST) then do not do the check in step 2.c. above. Always maintain from SRS1
e) if 2.d. not desirable, then need to check if routingname has been pre-populated (not routingid), but the "ELSE" part suggests otherwise.

So, bottom line ? 2.d. given the evidence so far...

Lord_Death

ASKER

BTW thx mark_wills for your time & sharing your knowledge

your reply helped too much ,
but I have a problem of performance the SRS2 has few tables with huge queries (SELECT) on it ,
and records in this table have some simmilar data , like Routing usually is repeated in multiple records so if i use a select on this(SRS2) DB and not on main (SRS1) DB i have a select with no INNER JOINs so I will have a very much better performance.
and cause of using other DataBase is for further use in other phases of our project (I know I can use a table in same DB)
SRS1 is the main DB and contain all the data. and those Quries (SELECTS) I mentioned before execute on SRS1 there would be Lots of Joins for example at least on 8 Tables. & i need the highest speed so here comes the second DB that helpes and in this phase solve those joins .

for reaching to this higher performance I put that " IF EXISTS ((SELECT TOP (1) ...." on line 10.
now I Underestand why it worked in QueryAnalyzer & not in Trigger (2.b)

and is there any other way to reach that performance and bypass those INNER JOINs ? (check SRS2 DATABASE if id is not found in it then Search in SRS1 DATABASE and get the data)

ASKER CERTIFIED SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark Wills

You are a hard person to please - not even a thank you (the more traditional acknowledgement in lieu of points) - suppose it is all in the name...

Lord_Death

ASKER

Dear Mark Wills about your last post I said before:

"BTW thx mark_wills for your time & sharing your knowledge"
"your reply helped too much ,"

but again Thanks a lot ...

-Share Your knowledge , gain more.

Mark Wills

Thanks Lord_Death, I do see that now, and owe you an apology, did not really see the thx. So, I now need to say thank you for taking the time to point it out and reiterate the "Thanks a lot" - it is very much appreciated.