November 30, 2009 at 4:48 am
Dear All,
I have trigger that performs checking whether the database is online or not and it inserts some value into local table on each if path so I could see the result of this test. It's working good but when I added extra sql to the true path of the if statement that inserts row to the table in the databse which state is checked in the if statement the trigger is throwing error when the database is off line. Below is the trigger sql:
IF((db_id('dbname') IS NULL) OR ((SELECT COUNT(*) FROM sys.databases WHERE [name] = 'dbname' AND [state_desc] = 'ONLINE')=0))
BEGIN
-- db is off
INSERT INTO [dbo].[testTBL] ([test_result]) VALUES('off')
END
ELSE
BEGIN
-- db is on
INSERT INTO [dbo].[testTBL] ([test_result]) VALUES('on')
-- extra sql that causes error
INSERT INTO [dbname].[dbo].[someTBL] ([some_value]) VALUES('test')
END
Is there a way to make it work?
Thanks in advance,
Dzordz
November 30, 2009 at 6:48 am
What is the error?
How are you determining what db you are doing the last insert into?
Can you supply ALL the trigger code?
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
November 30, 2009 at 7:02 am
Well, if the database is offline you can't connect to it, so why would you want to write anything to it?
Regards
Piotr
...and your only reply is slàinte mhath
November 30, 2009 at 7:24 am
Piotr.Rodak (11/30/2009)
Well, if the database is offline you can't connect to it, so why would you want to write anything to it?
The insert is in the ELSE portion, ie the database is online.
I suspect the problem's occurring during parse and compile. The entire thing has to get parsed, bound and optimised, regardless of whether the statement in question can be reached. Try dynamic SQL. It defers the parse, bind and compile until the statement is actually run.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 30, 2009 at 8:55 am
I tried to run sql dynamically - complete trigger code:
ALTER TRIGGER [dbo].[TBLIns]
ON [dbo].[TBL]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @SQLCmd NVARCHAR(2000)
SET @SQLCmd = '
-- inserting only records not existing in the target db if any
INSERT INTO [dbname].[int].[TBL]
([TBL_ID]
,[TBL_Value])
SELECT
I.[TBL_ID]
,I.[TBL_Value]
FROMinserted AS I LEFT JOIN [dbname].[int].[TBL]
ON [dbname].[int].[TBL].[TBL_ID] = I.[TBL_ID]
WHERE([dbname].[int].[TBL].[TBL_ID] IS NULL)'
-- check if db exist and if it is online
IF((db_id(dbname) IS NULL) OR ((SELECT COUNT(*) FROM sys.databases WHERE [name] = 'dbname' AND [state_desc] = 'ONLINE')=0))
BEGIN
-- adding inserted items
INSERT INTO [dbo].[Test] ([Test_Value]) VALUES('Off')
END
ELSE
BEGIN
EXEC(@SQLCmd)
END
END
but I got error message:
Msg 208, Level 16, State 1, Line 4
Invalid object name 'inserted'.
What can be the reason?
In general the idea was to insert some data to other database when rows are inserted in local db but only when the other database is up and running to not cause errors when it's not. If anyone knows better way how it can be done then please help.
BR,
Dzordz
November 30, 2009 at 9:23 am
dzordz_parowkowy (11/30/2009)
but I got error message:Msg 208, Level 16, State 1, Line 4
Invalid object name 'inserted'.
What can be the reason?
Because inserted is only visible in the trigger itself, not in anything it calls, including dynamic SQL. Your previous post didn't mention using inserted, or I wouldn't have suggested dynamic SQL.
In general the idea was to insert some data to other database when rows are inserted in local db but only when the other database is up and running to not cause errors when it's not. If anyone knows better way how it can be done then please help.
Do you often have databases offline? Why?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply