Cross DB SQL Trigger Epic Failure
I have the below SQL Trigger on a SQL 2005 box that is supposed to help me replicate certain Person Info to another database to be used for Reporting and various other things as a reference.
I have 1 DB named Connect which is where the current app manipulates the Person Data on tblPerson. I have another DB, on same physical box, named MATRIX where a new app manipulates it's Data. I am trying to build a Table in MATRIX called tblIdentificationMap that simply stores all of the various ID's we have from the different apps in house.
When I enable this Trigger and try to update tblPerson I get the following error --> Msg 208, Level 16, State 1, Procedure tblPersonIDMap_OnUpdate, Line 15
Invalid object name 'MATRIX.dbo.tblIndentificationMap'.
This is my UPDATE statement -->
` use Connect
update tblPerson
set MiddleName = 'Fakey'
where PersonID = 258243`
And this is my Trigger -->
ALTER TRIGGER [dbo].[tblPersonIDMap_OnUpdate]
ON [dbo].[tblPerson]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
UPDATE MATRIX.dbo.tblIndentificationMap
SET m.PersonID = i.PersonID
,m.FirstName = i.FirstName
开发者_StackOverflow中文版 ,m.MiddleName = i.MiddleName
,m.LastName = i.LastName
FROM MATRIX.dbo.tblIdentificationMap m, inserted i, deleted d
WHERE d.PersonID = m.PersonID
END
Replace
UPDATE MATRIX.dbo.tblIndentificationMap
with
m
in the first line. Also in the SET area you don't need the references to m (although I don't think they do any harm, it makes it a smidgeon harder to maintain).
Your SET statement is confused as you're inconsistently referring to the alias and the real name.
精彩评论