Creating a trigger

This recipe shows 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 AdventureWorks2014 database. We will create a trivial AFTER trigger that merely displays values from the inserted and deleted tables 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 PowerShell ISE as administrator.

2. Import the SQLPS module and create a new SMO Server object:

   #import SQL...