O'Reilly logo

SQL Server 2012 with PowerShell V3 Cookbook by Donabel Santos

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required