InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 18:51:17 GMT20http://www.sqlservercentral.com/Forums/Topic755818-145-1.aspxIf it was security, I'd expect that it wouldn't update the original table and a rollback would occur. Is that what is happening?Also, I'd write it in case multiple rows are updated. If it never happens, you haven't lost anything. If it does, then you've written bad code, and it doesn't take any longer to write:[code="sql"]UPDATE test1.dbo.tblUserSet ext = @ext,ext2 = @extwhere loginname = @user[/code]than it does to write:[code="sql"]UPDATE aSet ext = t1.ext,ext2 = t1.ext from inserted i inner join test1.dbo.tblUser a on a.loginname = i.loginname[/code]Mon, 20 Jul 2009 09:26:30 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic755818-145-1.aspxGot it working thanks, it was the loginname column had some nulls, so I had to match on a different column. Dohh!Mon, 20 Jul 2009 09:26:20 GMTtan110http://www.sqlservercentral.com/Forums/Topic755818-145-1.aspxcould it be security? does the uer who is inserting/updating into the table also have insert rights on the table in the [b]other database[/b]?maybe these specific statements need EXECUTE AS to make them occur correcty:[code]UPDATE test1.dbo.tblUserSet ext = @ext,ext2 = @extwhere loginname = @userUPDATE test1.dbo.tblUser2Set ext = @ext,ext2 = @extwhere loginname = @user[/code]Mon, 20 Jul 2009 09:17:32 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic755818-145-1.aspxIts not updating multiple rows. Its updating multiple columns on a row. Don't know if that makes a difference. I tried the following, but the update across the db is still not occurring. Again, it works fine in my test environment, just not production. set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER TRIGGER [trgUpdateExt] ON [dbo].[tblUser]FOR UPDATEAS DECLARE @ext varchar(5) DECLARE @user varchar(50) SELECT @ext = ext, @user = loginname FROM inserted i inner join dbo.tblUser tu on tu.loginname = i.loginameprint @extprint @userBEGIN UPDATE test1.dbo.tblUser Set ext = @ext, ext2 = @ext where loginname = @user UPDATE test1.dbo.tblUser2 Set ext = @ext, ext2 = @ext where loginname = @userENDMon, 20 Jul 2009 09:04:58 GMTtan110http://www.sqlservercentral.com/Forums/Topic755818-145-1.aspxWhat doesn't work? By that I mean what behavior is occurring. The trigger looks fine syntactically, but it won't work if multiple rows are updated.Instead what you'd do is join the tblUser to the inserted table and set the values.The inserted table will have the same structure as tblUser and contain only the new values of the updated rows. So you can join on the PK (or the user) and update the extension.Mon, 20 Jul 2009 08:47:12 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic755818-145-1.aspxI've written the following update trigger on my local machine, and i've tested it and works fine, but when I try to do in production, it doesn't work. I'm not sure what else to look at. I'm just updating a couple of phone extension columns on a different database. Any ideas.ALTER TRIGGER [trgUpdateExt] ON [dbo].[tblUser]FOR UPDATEAS DECLARE @ext varchar(5) DECLARE @user varchar(50) SELECT @ext = ext, @user = loginname FROM dbo.tblUserBEGIN UPDATE test1.dbo.tblUser Set ext = @ext, ext2 = @ext where loginname = @user UPDATE test1.dbo.tblUser2 Set ext = @ext, ext2 = @ext where loginname = @userENDMon, 20 Jul 2009 08:31:47 GMTtan110