January 9, 2008 at 3:15 am
Hi all.
I'm trying to figure out how to solve the problem below. We have a database that is used by 200 people, now a trigger in this database needs to update a row in a different database.(row is found by using an external_document_no field)
This needs to be accomplished without allowing all user access to the table in the other database and needs to be done by using an trigger after delete.
CREATE TRIGGER [dbo].[trg_updateWorklog]
ON [dbo].[Updates]
AFTER DELETE
AS
BEGIN
SET NOCOUNT ON;
declare @update_filter int
declare cur cursor for select cast([External Document No_] as int) from deleted
open cur
fetch next from cur into @update_filter
while @@fetch_status = 0
begin
if (@update_filter> 0)
begin
UPDATE dest_db.dest_table_schema.dest_table SET update_field = getdate() WHERE update_field = @update_filter
end
fetch next from cur into @worklogid
end
close cur
deallocate cur
END
The above code will give me an error saying that user has no rights to read or write in the dest_table. Now in sql 2005 I would simply use execute as login = ‘my login’ is there anything like that in sql 2000 ?
Any ideas.
kgunnarsson
Mcitp Database Developer.
January 9, 2008 at 6:29 am
The was solved by the following but ugly solution.
1.) Create a table in the local database.
2.) Make the trigger dump the external number in that database (All users have read/write in the dbo schema)
3.) Create and stored procedure that queries the local table and update the table in the external database.
4.) use sql agent to run the stored procedure as an user with rights in the destination database.
ugly but works
kgunnarsson
Mcitp Database Developer.
January 9, 2008 at 6:35 am
That's really the only way to make this work. The trigger will always execute as the person updating the table, and it would need rights to the other database if it directly moved the data.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 11, 2008 at 4:53 am
I recommend you to check the user rights....
January 11, 2008 at 9:40 pm
If you have an answer to this, Ananth, just tell us... 😉
--Jeff Moden
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply