Changing DB Dynamically in Trigger

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Changing DB Dynamically in Trigger

Author

Topic

haw001
Starting Member

20 Posts

Posted - 2006-07-26 : 01:10:13

I've got a trigger which moves data from one database to another as the data arrives. Problem is that the destination database depends on the data coming in.

I'd like to change the DB of my insert statement dynamically. At first I thought I could use a variable for the DB name, but that doesn't work. Then I thought I could put the entire statement into a variable, but that doesn't work either:

CREATE Trigger RB_Upload on UPLOAD FOR INSERT AS

DECLARE @DBNAME VARCHAR(15)
DECLARE @SQL VARCHAR(8000)

IF EXISTS (SELECT ''
	FROM inserted WHERE (FIELD001 = 'MT' AND FIELD004 = 'VIC') OR
		(FIELD001 = 'MT' AND FIELD033 = 'VIC') OR
		(FIELD001 = 'PC' AND FIELD056 = 'VIC') OR
		(FIELD001 = 'XC' AND FIELD014 = 'VIC') OR
		(FIELD001 = 'RC' AND FIELD003 = 'VIC'))
SET @DBNAME = 'VIC'
ELSE IF EXISTS (SELECT ''
	FROM inserted WHERE (FIELD001 = 'MT' AND FIELD004 IN ('KP','WA')) OR
		(FIELD001 = 'MT' AND FIELD033 = 'TR') OR
		(FIELD001 = 'PC' AND FIELD056 = 'TR') OR
		(FIELD001 = 'XC' AND FIELD014 = 'TR') OR
		(FIELD001 = 'RC' AND FIELD003 = 'TR'))
SET @DBNAME = 'TR'

SET @SQL = 'INSERT INTO ' + @DBNAME +'.dbo.UPLOAD
                      (UPLOADED, TIMESTMP, FIELD001, FIELD002, FIELD003, FIELD004, FIELD005, FIELD006, FIELD007, FIELD008, FIELD009, FIELD010, FIELD011, 
                      FIELD012, FIELD013, FIELD014, FIELD015, FIELD016, FIELD017, FIELD018, FIELD019, FIELD020, FIELD021, FIELD022, FIELD023, FIELD024, 
                      FIELD025, FIELD026, FIELD027, FIELD028, FIELD029, FIELD030, FIELD031, FIELD032, FIELD033, FIELD034, FIELD035, FIELD036, FIELD037, 
                      FIELD038, FIELD039, FIELD040, FIELD041, FIELD042, FIELD043, FIELD044, FIELD045, FIELD046, FIELD047, FIELD048, FIELD049, FIELD050, 
                      FIELD051, FIELD052, FIELD053, FIELD054, FIELD055, FIELD056, FIELD057, FIELD058, FIELD059, FIELD060, FIELD061, FIELD062, FIELD063, 
                      FIELD064, FIELD065, FIELD066, FIELD067, FIELD068, FIELD069, FIELD070, FIELD071, FIELD072, FIELD073, FIELD074, FIELD075, FIELD076, 
                      FIELD077, FIELD078, FIELD079, FIELD080, FIELD081, FIELD082, FIELD083, FIELD084, FIELD085, FIELD086, FIELD087, FIELD088, FIELD089, 
                      FIELD090, FIELD091, FIELD092, FIELD093, FIELD094, FIELD095, FIELD096, FIELD097, FIELD098, FIELD099, FIELD100, FIELD101, FIELD102, 
                      FIELD103, FIELD104, FIELD105, FIELD106, FIELD107, FIELD108, FIELD109, FIELD110, FIELD111, FIELD112, FIELD113, FIELD114, FIELD115, 
                      FIELD116, FIELD117, FIELD118, FIELD119, FIELD120, FIELD121, FIELD122, FIELD123, FIELD124, FIELD125, FIELD126, FIELD127, FIELD128, 
                      FIELD129, FIELD130)

SELECT UPLOADED, TIMESTMP, FIELD001, FIELD002, FIELD003, FIELD004, FIELD005, FIELD006, FIELD007, FIELD008, FIELD009, FIELD010, FIELD011, 
                      FIELD012, FIELD013, FIELD014, FIELD015, FIELD016, FIELD017, FIELD018, FIELD019, FIELD020, FIELD021, FIELD022, FIELD023, FIELD024, 
                      FIELD025, FIELD026, FIELD027, FIELD028, FIELD029, FIELD030, FIELD031, FIELD032, FIELD033, FIELD034, FIELD035, FIELD036, FIELD037, 
                      FIELD038, FIELD039, FIELD040, FIELD041, FIELD042, FIELD043, FIELD044, FIELD045, FIELD046, FIELD047, FIELD048, FIELD049, FIELD050, 
                      FIELD051, FIELD052, FIELD053, FIELD054, FIELD055, FIELD056, FIELD057, FIELD058, FIELD059, FIELD060, FIELD061, FIELD062, FIELD063, 
                      FIELD064, FIELD065, FIELD066, FIELD067, FIELD068, FIELD069, FIELD070, FIELD071, FIELD072, FIELD073, FIELD074, FIELD075, FIELD076, 
                      FIELD077, FIELD078, FIELD079, FIELD080, FIELD081, FIELD082, FIELD083, FIELD084, FIELD085, FIELD086, FIELD087, FIELD088, FIELD089, 
                      FIELD090, FIELD091, FIELD092, FIELD093, FIELD094, FIELD095, FIELD096, FIELD097, FIELD098, FIELD099, FIELD100, FIELD101, FIELD102, 
                      FIELD103, FIELD104, FIELD105, FIELD106, FIELD107, FIELD108, FIELD109, FIELD110, FIELD111, FIELD112, FIELD113, FIELD114, FIELD115, 
                      FIELD116, FIELD117, FIELD118, FIELD119, FIELD120, FIELD121, FIELD122, FIELD123, FIELD124, FIELD125, FIELD126, FIELD127, FIELD128, 
                      FIELD129, FIELD130
FROM INSERTED

DELETE UPLOAD

FROM UPLOAD, inserted WHERE UPLOAD.DEX_ROW_ID=inserted.DEX_ROW_ID'

EXEC(@SQL)

GO

Anyone have any idea where I am going wrong or how I can go about it differently. For now I have an if statement and the query coded twice. Works, but messy. Must be a better way.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-07-26 : 03:36:17

Try to change your @SQL datatype form varchar to nvarchar and execute the sql like this:

EXEC sp_executesql @SQL;

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-29 : 06:12:36

http://www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

haw001
Starting Member

20 Posts

Posted - 2006-07-30 : 20:21:38

Madhivanan,

Thanks for the link, has the info I needed to get it working.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources