Update a column in one database from another (Dynamic) database

A row gets inserted into 2 DB tables at same time, but one of the coulmn remains null on one DB and gets value on another. No my task is :-  
I have to update a column in one database from another database After Insert,
to get the Inserted row pulled and update the record I am using a After Insert Trigger.

ALTER TRIGGER [Update_Trg]
   ON [dbo].[location_all]
   AFTER INSERT
AS
BEGIN

DECLARE @Databasename NameType
DECLARE @CurrentDB NameType
DECLARE @SQL varchar(max)

SELECT TOP 1
@site_ref = i.site_ref
, @loc = i.loc  
, @RowPointer =i.RowPointer    
FROM INSERTED i

SELECT @Databasename = Databasename
FROM dbo._Site_DatabaseNames
Where Site = @site_ref

SELECT @CurrentDB = db_name()

set @SQL ='UPDATE l_a '+
           'SET l_a.mrb_flag = l.mrb_flag ' +
           'FROM [' + @CurrentDB + '].dbo.location_all l_a ' +
           'INNER JOIN [' + @Databasename + '].dbo.location_all l ON l.RowPointer = l_a.RowPointer ' +
           'INNER JOIN inserted i ON l_a.RowPointer = i.RowPointer  '+
           'WHERE i.site_ref = l.site_ref and i.loc = l.loc'
EXEC(@SQL)

Invalid Object Name inserted.

What is wrong in this script ? Can anybody help me find a solution. Thanks.
SaiLoveAllAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
change:

  and l.row_pointer = ''' + @RowPointer  + ''' '

into:

  and l.row_pointer = ''' +cast( @RowPointer as varchar(100))  + ''' '
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
inside the dynamic sql, INSERTED is no longer valid, unfortunately.
so, you need to refer to the real table instead, using the ID value you need to fetch from inserted and add such a condition into the @sql explicitely

something
ALTER TRIGGER [Update_Trg]
   ON [dbo].[location_all] 
   AFTER INSERT
AS 
BEGIN

DECLARE @Databasename NameType
DECLARE @CurrentDB NameType
DECLARE @SQL varchar(max)

SELECT TOP 1 
@site_ref = i.site_ref
, @loc = i.loc   
, @RowPointer =i.RowPointer    
FROM INSERTED i 

SELECT @Databasename = Databasename
FROM dbo._Site_DatabaseNames
Where Site = @site_ref

SELECT @CurrentDB = db_name()

set @SQL ='UPDATE l_a   
           SET l_a.mrb_flag = l.mrb_flag 
           FROM [' + @CurrentDB + '].dbo.location_all l_a
           INNER JOIN [' + @Databasename + '].dbo.location_all l ON l.RowPointer = l_a.RowPointer  
             and l.row_pointer = ''' + @RowPointer  + ''' '
EXEC(@SQL)

Open in new window

0
 
SaiLoveAllAuthor Commented:
set @SQL ='UPDATE l_a  
           SET l_a.mrb_flag = l.mrb_flag
           FROM [' + @CurrentDB + '].dbo.location_all l_a
           INNER JOIN [' + @Databasename + '].dbo.location_all l ON l.RowPointer = l_a.RowPointer  
             and l.row_pointer = ''' + @RowPointer  + ''' '

When I do so, I get an ERROR Message :-
The data types nvarchar and uniqueidentifier are incompatible in the add operator.

The variable '@RowPointer ' is a uniqueidentifier.  What could have caused the incomplatability ?

In trigger
DECLARE @RowPointer RowPointerType  

Table column
[RowPointer] [dbo].[RowPointerType] NOT NULL CONSTRAINT [DF_location_all_RowPointer]  DEFAULT (newid())
0
 
SaiLoveAllAuthor Commented:
Thanks a Lot. It worked.
0
All Courses

From novice to tech pro — start learning today.