How can I copy a trigger? 1

[kaymc]

I have one pesky ickle trigger which I have to delete before I run a few innoecent queries in a DTS job. Up till now, I have simply recreated the trigger in my script after the queries have been processed.

Now the problem is that this trigger gets changed from time to time and I have to change the create trigger script in my dts query every time. Unfortunately, people, being people, do sometimes neglect to tell me about trigger changes.

Yes, I've considered murder, but it's not really my style. What I'd like to do is take the human element out of the equation and have my job copy the trigger from another database (where it exists quite happily) OR perhaps have my DTS job somehow save the trigger before I drop it, and then be able to restore it after my other queries have run.

Clues anyone?

 

[JayKusch]

This should help ...

It will export the triggers schema to a text file that you can call on later.

This is the underlying SP you need to create first:

CREATE PROCEDURE proc_genscript
@ServerName varchar(30),
@DBName varchar(30),
@ObjectName varchar(50),
@ObjectType varchar(10),
@TableName varchar(50),
@ScriptFile varchar(255)
AS

DECLARE @CmdStr varchar(255)
DECLARE @object int
DECLARE @hr int

SET NOCOUNT ON

SET @CmdStr = 'Connect('+@ServerName+')'

EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT

-- Comment out for standard login

EXEC @hr = sp_OASetProperty @object, 'LoginSecure', TRUE

/* Uncomment for Standard Login

EXEC @hr = sp_OASetProperty @object, 'Login', 'sa'
EXEC @hr = sp_OASetProperty @object, 'password', 'sapassword'
*/

EXEC @hr = sp_OAMethod @object,@CmdStr

SET @CmdStr = 'Databases("' + @DBName + '&quot.Tables("' + @TableName + '&quot.Triggers("'

SET @CmdStr = @CmdStr + @ObjectName + '&quot.Script(5,"' + @ScriptFile + '&quot'

EXEC @hr = sp_OAMethod @object, @CmdStr

EXEC @hr = sp_OADestroy @object

GO



This is the execute statement(You will of course need to change the values for your setup!):

exec proc_genscript

@ServerName = 'MyServer',
@DBName = 'MyDB',
@ObjectName = 'TriggerName',
@ObjectType = 'Trigger',
@TableName = 'MyTable',
@ScriptFile = 'c:\MyTriggerSrcipt.sql'


Good Luck!


Thanks

J. Kusch

 

[kaymc]

I should have posted this earlier, but THANK YOU so much for that procedure. It worked like a charm, and thanks to it being so general in nature, I have already found other uses for it.

Thanks!