This recipe demonstrates how to programmatically create a trigger in SQL Server using SMO and PowerShell.
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 ...