Creating a trigger

This recipe demonstrates how to programmatically create a trigger in SQL Server using SMO and PowerShell.

Getting ready

For this recipe, we will use the Person.Person table in the AdventureWorks2008R2 database. We will create a trivial AFTER trigger that merely displays values from the inserted and deleted records upon firing.

The following is the T-SQL equivalent of what we are going to accomplish programmatically in this section:

CREATE TRIGGER [Person].[tr_u_Person] 
ON [Person].[Person] 
AFTER  UPDATE 
AS 

  SELECT 
     GETDATE() AS UpdatedOn,
     SYSTEM_USER AS UpdatedBy,
     i.LastName AS NewLastName,
     i.FirstName AS NewFirstName,
     d.LastName AS OldLastName,
     d.FirstName AS OldFirstName
  FROM 
     inserted i
     INNER JOIN deleted d
     ON i.BusinessEntityID = d.BusinessEntityID

How to do it...

Let's follow these steps to create an AFTER trigger in PowerShell:

1. Open the PowerShell console by going to Start | Accessories | Windows PowerShell | Windows...