Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

update trigger not updating across databases Expand / Collapse
Author
Message
Posted Monday, July 20, 2009 8:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 16, 2014 10:08 PM
Points: 148, Visits: 642
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

Post #755818
Posted Monday, July 20, 2009 8:47 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: 2 days ago @ 12:34 PM
Points: 31,181, Visits: 15,626
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: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #755845
Posted Monday, July 20, 2009 9:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 16, 2014 10:08 PM
Points: 148, Visits: 642
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

Post #755873
Posted Monday, July 20, 2009 9:17 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:09 PM
Points: 12,905, Visits: 32,166
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #755896
Posted Monday, July 20, 2009 9:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 16, 2014 10:08 PM
Points: 148, Visits: 642
Got it working thanks, it was the loginname column had some nulls, so I had to match on a different column. Dohh!
Post #755915
Posted Monday, July 20, 2009 9:26 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: 2 days ago @ 12:34 PM
Points: 31,181, Visits: 15,626
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: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #755916
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse