A trigger is an event based stored procedure that automatically executes when an event occurs in the database server. DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. DDL trigger is executed when a database object is created, altered or dropped from the database.
Below are the key differences in DDL and DML triggers:
| DML Triggers | DDL Triggers |
| Events associated with DML keywords like ‘Insert’, ‘Update’ and ‘Delete’ cause the execution of DML triggers | Events associated with DDL keywords like ‘Create’, ‘Alter’ and ‘Drop’ cause the execution of DDL triggers |
| These triggers can be executed both before and after the ‘Insert’, ‘Update’ and ‘Delete’ events occur. | These triggers are executed only after the ‘Create’, ‘Alter’ and ‘Drop’ DDL events are occured. |
| These triggers are object level triggers and are associated with Tables and Views | These triggers are database level triggers and they are directly associated with DDL objects |
| Magic Tables viz. ‘inserted’ and ‘deleted’ are accessible in DML triggers | There are no magic tables in the DDL Triggers |
Post comments if this helps
