April 11, 2005 at 5:16 pm
My developers have asked me this one. They have created several triggers on different tables in our development system. They now want to move these triggers to QA and then Prod. They are presently grabbing the script for each trigger and manually recreating it in the table on the QA system.
Is there a way for me to DTS all of these triggers? When I create a package with Copy SQL Objects Task, I cannot see any triggers. Or, is there another way?
Thanks.
April 11, 2005 at 9:19 pm
You can copy tables and include any associated triggers, but you can't just copy triggers using DTS.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
April 12, 2005 at 12:37 pm
I was afraid of that.
Any other ideas???
April 12, 2005 at 12:56 pm
This gets into establishing change control procedures and is a very big subject. Generally though all changes should be scripted anyway, and as you run the scripts against dev the scripts should be organized and placed in a centralized and controlled location. As you prepare to move into a QA environment the scripts are already there for you. There are products that make this very manual process more automated but they don't really change the process.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
April 13, 2005 at 9:26 am
have the dts package dynamically add the sql script in to drop and refresh the triggers.
this function returns the drop and create sql for all triggers on a table. if you are gonna do a bunch of tables on the same server, i'd tweak it some. something similar will give you the sql you need then use dts.package to modify the sql task
Option Explicit
wscript.echo GetTriggerRefreshSql("test","test","test")
Function GetTriggerRefreshSql(strServerName, strDatabaseName, strTableName)
dim objTrigger, objTriggers, strTriggers
with CreateObject ("SQLDMO.SQLServer")
.LoginSecure = TRUE
.Connect strSourceServerName
set objTriggers = objSourceServer.Databases(strSourceDatabaseName).Tables(strTableName).Triggers
for each objTrigger in objTriggers
strTriggers = strTriggers & objTrigger.Script(1+4)
next
.DisConnect
end with
GetTriggerRefreshSql = strTriggers
End Function
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply