Need Help on Update Trigger

[vaidyanathanpc]

I am having a difficulty in coding a trigger. The situation is something like this.
I have two different databases DB1 and DB2 both on them on the same server.
In DB1 I have a table USERMASTER and it's structure is like this

USER_ID int
USER_FIRST_NAME varchar(50)
USER_MIDDLE_NAME varchar(50)
USER_LAST_NAME varchar(50)

In DB2 I have a table USER amd it's structure is as follows

USER_ID int
USER_NAME varchar(150)

USER_NAME in USER table of DB1 is the combined of USER_FIRST_NAME,USER_MIDDLE_NAME and USER_LAST_NAME of USERMASTER table of DB1.

For example if USER_ID in USERMASTER has the values
USER_ID: 2184
USER_FIRST_NAME: Vaidyanathan
USER_MIDDLE_NAME: Chandrasekharan
USER_LAST_NAME: Pazhayannur

USER table would hold
USER_ID: 2184
USER_NAME: Vaidyanathan Chandrasekharan Pazhayannur

Now I want to write a trigger such that if any of the fields USER_FIRST_NAME or USER_MIDDLE_NAME or USER_LAST_NAME of USERMASTER is updated, USER_NAME in USER table should be correspondingly updated.
i.e say the record in USERMASTER is updated as

USER_ID: 2184
USER_FIRST_NAME: Vaidyanathan
USER_MIDDLE_NAME: Null
USER_LAST_NAME: Pazhayannur

the record in USER table would hold
USER_ID: 2184
USER_NAME: Vaidyanathan Pazhayannur

Similarly say the record in USERMASTER is changed as

USER_ID: 2184
USER_FIRST_NAME: Vaidyanathan
USER_MIDDLE_NAME: Null
USER_LAST_NAME: Thrissur

the record in USER table would hold
USER_ID: 2184
USER_NAME: Vaidyanathan Thrissur

and like wise...

How can I achieve this?

Thanks in advance
P.C. Vaidyanathan

 

[mgid]

Maybe this will work?

CREATER TRIGGER
FOR INSERT, UPDATE
AS
DECLARE @USER_FIRST_NAME varchar(50),
@USER_MIDDLE_NAME varchar(50),
@USER_LAST_NAME varchar(50)
@USER_ID int,
@USER_NAME varchar(150)

SELECT @USER_ID=USER_ID,
@USER_FIRST_NAME=USER_FIRST_NAME,
@USER_MIDDLE_NAME=USER_MIDDLE_NAME,
@USER_LAST_NAME=USER_LAST_NAME
FROM inserted

SET @USER_NAME = ISNULL(@USER_FIRST_NAME,"&quot +
ISNULL(@USER_MIDDLE_NAME,"&quot +
ISNULL(@USER_LAST_NAME,"&quot

UPDATE DB2.dbo.USER SET USER_NAME=@USER_NAME
WHERE USER_ID=@USER_ID

 

[vaidyanathanpc]

Thanks... It worked...
P.C. Vaidyanathan