SMO - Triggers

Before talking about the code part, it is important to tell something about triggers. Triggers are a special kind of Stored Procedure that respond to special events. SQL Server offers two types of Triggers: Data Definition Language (DDL) Triggers and Data Manipulation Language (DML) Triggers.

DDL Triggers fire in response to a change in the structure of a database (when CREATE, ALTER, or DROP statements are executed).

Creating a DML Trigger
DML Triggers fire in response to changing of data (when INSERT, UPDATE, or DETELE statements are executes). DML Triggers are represented by a Trigger object. One of the main properties of the Trigger object is TextBody. This property represents code that runs when a Trigger is fired. The type of Trigger is set by the Insert, Update, or Delete properties.

Database myNewDatabase = srv.Databases["myNewDatabase"];
Table myFirstSMOTable = myNewDatabase.Tables["myFirstSMOTable"];
Trigger trigger = new Trigger(myFirstSMOTable, "SMOTrigger");
trigger.TextMode = false;
trigger.Insert = true;
trigger.Update = true;
trigger.Delete = false;
trigger.InsertOrder = 
  Microsoft.SqlServer.Management.Smo.Agent.ActivationOrder.First;
trigger.TextBody = " RAISERROR('MESSAGE',16,10) "; ;
trigger.ImplementationType = ImplementationType.TransactSql;
trigger.Create();

In this example, a Trigger named SMOTrigger is created. This trigger fires when an Insert or Update statement is executed. DML Triggers are bound to a table. In this case, the trigger is bound with MyFirstSMOTable and when one of the mentioned statements (INSERT or UPDATE) occurs, the Trigger fires.

Creating a DDL Trigger
Another type of Triggers is DDL Triggers. In SMO, this type is represented by two objects: ServerDdlTrigger and DatabaseDdlTrigger. Here is an example of DatabaseDdlTrigger:

Database myNewDatabase = srv.Databases["myNewDatabase"];
DatabaseDdlTrigger databaseTrigger = 
  new DatabaseDdlTrigger(myNewDatabase,"SMODatabaseTrigger");
databaseTrigger.TextHeader = 
  "CREATE TRIGGER SMODatabaseTrigger ON DATABASE FOR DROP_TABLE AS";
databaseTrigger.TextBody = 
  "PRINT 'You can not delete table!' ; ROLLBACK "; ;
databaseTrigger.ImplementationType = ImplementationType.TransactSql;
databaseTrigger.ExecutionContext = DatabaseDdlTriggerExecutionContext.Caller;
databaseTrigger.Create();

In this example, a DDL trigger named SMODatabaseTrigger in the database MyNewDatabase is created. This Trigger fires when someone tries to execute a Drop table statement.