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.