Copy trigger from one database to another

Question

Is it possible, in a script executed in MS SQL Server 2005, to copy a trigger from one database to another?

I've been asked to write a test script for a trigger my project is using. Our test structure is to create an empty database containing only the object under test, then execute a script on that database that creates all the other objects needed for the test, fills them, runs whatever tests are needed, compares the results against expected results, and then drops everything except the object under test.

I can't just create a database that is empty except for the trigger, because the trigger depends on several tables. My test script currently runs the CREATE TRIGGER after all the required tables are created, but this won't do because the test script isn't allowed to contain the object under test.

What's been suggested is that, instead of running a CREATE TRIGGER, I somehow copy the trigger at that point in the script from the live database to the test database. I've had a quick Google and haven't found a way to do this. Thus my question - is this even possible, and if so, how can I do it?

Answer 1

You could read the text of the trigger with sp_helptext (triggername)

Or you can select the text into a variable and execute that:

declare @sql varchar(8000)


select @sql = object_definition(object_id) 
from sys.triggers
where name = 'testtrigger'

EXEC @sql