SQL Server 2014 with PowerShell v5 Cookbook

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 ...