Book Image

SQL Server 2014 with PowerShell v5 Cookbook

By : Donabel Santos
Book Image

SQL Server 2014 with PowerShell v5 Cookbook

By: Donabel Santos

Overview of this book

Table of Contents (21 chapters)
SQL Server 2014 with PowerShell v5 Cookbook
About the Author
About the Reviewers

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]

     GETDATE() AS UpdatedOn,
     SYSTEM_USER AS UpdatedBy,
     i.LastName AS NewLastName,
     i.FirstName AS NewFirstName,
     d.LastName AS OldLastName,
     d.FirstName AS OldFirstName
     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...