How to Script out a trigger attached to a table in SQL

Question

I would like to use smo and powershell script to out a trigger that is attached to a table .

I have found a way to script out the table with the trigger but this is not what,I also wont always know the name of the table the trigger is connected to ,I would just know the trigger name .

I want to give the script the name of the trigger and have it script it out.

I have been looking for a way to do this and have had no luck.

Thanks for your help as always.

score 0 · Answer 1 · answered Oct 26 '12 at 12:49

This script seems to work OK with Sql Server 2008R2. You didn't specify the DB, but Powershell and SMO are kinda telltales for MS products.

SELECT
Tables.Name as 'TableName',
Triggers.name as 'TriggerName',
Comments.Text as 'TriggerText'
FROM  sysobjects Triggers
      Inner Join sysobjects Tables On Triggers.parent_obj = Tables.id
      Inner Join syscomments Comments On Triggers.id = Comments.id
WHERE 
      Tables.name = '<table-name>'
ORDER BY Tables.Name, Triggers.name

This returns multiple rows per table on my db and the Trigger text is not all ways in the same row. — justinf, Oct 29 '12 at 12:15

score 0 · Accepted Answer · answered Oct 26 '12 at 13:59

Here's an example using sqlps:

SQLSERVER:\SQL\Z001\SQL1\Databases\pubs>$db = get-item .
SQLSERVER:\SQL\Z001\SQL1\Databases\pubs>$db.Tables | foreach {$_.Triggers} | where {$_.name -like"employee*"} | foreach {$_.Script()}

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

CREATE TRIGGER employee_insupd
ON employee
FOR insert, UPDATE
AS
--Get the range of level for this job type from the jobs table.
declare @min_lvl tinyint,
   @max_lvl tinyint,
   @emp_lvl tinyint,
   @job_id smallint
select @min_lvl = min_lvl,
   @max_lvl = max_lvl,
   @emp_lvl = i.job_lvl,
   @job_id = i.job_id
from employee e, jobs j, inserted i
where e.emp_id = i.emp_id AND i.job_id = j.job_id
IF (@job_id = 1) and (@emp_lvl <> 10)
begin
   raiserror ('Job id 1 expects the default level of 10.',16,1)
   ROLLBACK TRANSACTION
end
ELSE
IF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl)
begin
   raiserror ('The level for job_id:%d should be between %d and %d.',
      16, 1, @job_id, @min_lvl, @max_lvl)
   ROLLBACK TRANSACTION
end

How to Script out a trigger attached to a table in SQL

2 Answers2