February 16, 2010 at 6:40 am
I have create a set of triggers which updates information in an external database on the same server. the triggers were designed to allow for instances where the external db may be offline for any reason. the problem I am having is that when these triggers were created the external database existed but now it doesn’t exist and the triggers are failing due to this fact.
here is one of the triggers I have created:
ALTER TRIGGER [dbo].[trg_CGR_tlb_AftUpdateInsert] ON dbo.tlb AFTER UPDATE, INSERT
AS
BEGIN
SET NOCOUNT ON
DECLARE @ReportDBActive BIT
SELECT @ReportDBActive = COUNT(*) FROM [master].[sys].[databases] WHERE [name] = N'CGR' AND state_desc = 'ONLINE'
IF @ReportDBActive > 0
BEGIN
UPDATE sd SET
OriginalSource = a.SOURCE,
OriginalSubSource = a.SubSource
FROM CGR.dbo.SalesDetails sd WITH(NOLOCK)
JOIN inserted a ON a.ID = sd.SALTRef
INSERT INTO 'CGR'.dbo.SalesDetails
(
SaltRef,
OriginalSource,
OriginalSubSource
)
SELECT
a.SaltRef,
a.SOURCE,
a.SubSource
FROM inserted a
LEFT JOIN CGR.dbo.SalesDetails sd WITH(NOLOCK) ON a.ID = sd.SALTRef
WHERE sd.ID IS NULL
END
END
GO
Any help would be much appreciated as this is causing me such a headache.
February 16, 2010 at 7:12 am
Terry,
What is the exact version number of the SQL Server? (e.g. 9.0.4273)
What is the exact error message you get?
I've tried to reproduce the error on my server, without success.
On a separate point, you know this isn't the greatest design in the world, yes?
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 16, 2010 at 8:29 am
Thanks for your reply paul,
The exact version is 9.0.3027
The error message comes from replication and is: Database 'CGR' cannot be opened. It is in the middle of a restore. (Source: MSSQLServer, Error number: 927)
I know this isn't the best method but the business require upto the second MI from a mass of databases with no delay and they wanted it yesterday and this is the best i could come up with in the time i had, as is the norm here. This has only now become an issues since we are moving the dbs from one server to another and on the new server the CGR db is being restored, but i thought that the triggers would manage this using my IF statement.
I am thinking of using a sproc to manage the updating of the data but just thought i would see what others thought of the issue and if there was any way around sql server pre-validating the trigger on execution.
the problem is possibly down to the fact that CGR does exist but in a state of restore and that means that deferred name resolution is not applied and CGR db is being validated by SQL.
Apologies but i had made a mistake in my cut down version of the trigger SQL posted; this was having 'CGR'.dbo.SalesDetails it should be CGR.dbo.SalesDetails without the quotes.
UPDATE: I have sorted this by renaming the restoring CGR db to _CGR and now it doesnt exist as CGR the triggers work as expected.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply