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 was asked to write a test script to run that uses my project. Our test structure is to create an empty database containing only the object under test, then run a script in this database that creates all the other objects necessary for the test, populate them, run any tests, compare the results with the expected results, and then delete everything. except for the object under test.

I cannot just create a database that is empty except for the trigger, because the trigger depends on several tables. My test script is currently running CREATE TRIGGER after creating all the necessary tables, but this will not be done because the test script cannot contain the test object.

It has been suggested that instead of running CREATE TRIGGER, I will somehow copy the trigger at this point in the script from the live database to the test database. I had a quick google and I did not find a way to do this. So my question is, is it even possible, and if so, how can I do this?

Answer 1

You can read the trigger text with sp_helptext (triggername)

:

declare @sql varchar(8000)


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

EXEC @sql