November 10, 2009 at 5:34 am
Hi,
I have a problem with checking whether the database that is referenced in trigger sql exist and is online. What I would like to have is either running the trigger only if db is online or running the trigger always and checking if the db is online and not do anything if it's not.
I tried to do this (second option) with the following code:
IF NOT EXISTS(SELECT * FROM sys.databases WHERE [name] = 'dbname' AND [state_desc] = 'ONLINE')
RETURN
, but this is not working. I mean executing above code in query window works ok but when use it in the "AFTER INSERT" or "AFTER UPDATE" trigger and try to insert or update a row in the table I get the following error message: "Database 'dbname' can not be opened because it is offline".
Trigger sql:
IF EXISTS(SELECT * FROM sys.databases WHERE [name] = 'dbname' AND [state_desc] = 'ONLINE')
BEGIN TRY
DECLARE @Id INT
SELECT @Id = inserted.[TBL_ID] FROM inserted
IF((SELECT COUNT(*) FROM [dbname].[int].[V_TBL_1]
WHERE ([TBL_ID] = @Id))=0)
INSERT INTO [dbname].[int].[V_TBL_1]
( [TBL_ID]
,[TBL_Number])
SELECT
( [TBL_ID]
,[TBL_Number])
FROM inserted
END TRY
BEGIN CATCH
END CATCH
How can I solve this issue?
Thanks in advance,
Dzordz
November 10, 2009 at 7:30 am
I'm going to venture a guess and say that you are experiencing a permissions issue. From BOL:
If the caller of sys.databases is not the owner of the database and the database is not master or tempdb, the minimum permissions required to see the corresponding row are ALTER ANY DATABASE or VIEW ANY DATABASE server-level permission, or CREATE DATABASE permission in the master database. The database to which the caller is connected can always be viewed in sys.databases.
You can do an EXECUTE AS or you could insert into a local table and then use another process to another database if needed.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply