Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


update trigger not updating across databases


update trigger not updating across databases

Author
Message
tan110
tan110
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 702
I'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 UPDATE
AS
DECLARE @ext varchar(5)
   DECLARE @user varchar(50)

SELECT @ext = ext, @user = loginname
FROM dbo.tblUser

BEGIN
   UPDATE test1.dbo.tblUser
   Set ext = @ext,
      ext2 = @ext
   where loginname = @user

   UPDATE test1.dbo.tblUser2
   Set ext = @ext,
      ext2 = @ext
   where loginname = @user
END
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: Administrators
Points: 35963 Visits: 18725
What 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.

Follow me on Twitter: @way0utwestForum Etiquette: How to post data/code on a forum to get the best help
tan110
tan110
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 702
Its 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 ON
set QUOTED_IDENTIFIER ON
go

ALTER TRIGGER [trgUpdateExt]
ON [dbo].[tblUser]
FOR UPDATE
AS
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.loginame

print @ext
print @user

BEGIN
   UPDATE test1.dbo.tblUser
   Set ext = @ext,
      ext2 = @ext
   where loginname = @user

   UPDATE test1.dbo.tblUser2
   Set ext = @ext,
      ext2 = @ext
   where loginname = @user
END
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14929 Visits: 38913
could it be security? does the uer who is inserting/updating into the table also have insert rights on the table in the other database?
maybe these specific statements need EXECUTE AS to make them occur correcty:
UPDATE test1.dbo.tblUser
Set ext = @ext,
ext2 = @ext
where loginname = @user

UPDATE test1.dbo.tblUser2
Set ext = @ext,
ext2 = @ext
where loginname = @user



Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

tan110
tan110
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 702
Got it working thanks, it was the loginname column had some nulls, so I had to match on a different column. Dohh!
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: Administrators
Points: 35963 Visits: 18725
If 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:

UPDATE test1.dbo.tblUser
Set ext = @ext,
ext2 = @ext
where loginname = @user



than it does to write:

UPDATE a
Set ext = t1.ext,
ext2 = t1.ext
from inserted i
inner join test1.dbo.tblUser a
on a.loginname = i.loginname



Follow me on Twitter: @way0utwestForum Etiquette: How to post data/code on a forum to get the best help
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search