Post reply

checking if db is online in trigger

  • November 10, 2009 at 5:34 am

    #227814

    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

    #1077243

    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