A trigger is a stored procedure in database which automatically invokes whenever a special event in the database occurs. For example, a trigger can be invoked when a row is inserted into a specified table or when certain table columns are being updated.

A trigger runs automatically when an event occurs. There are three different types of events on which a trigger can run/fired automatically:

DML Triggers in SQL Server

DML triggers in SQL Server are fired when a DML event occurs. i.e. when data is inserted/ updated/deleted in the table by a user.

We can create a DML trigger for a specific event or multiple events. The triggers in SQL Server(DML) fire on events irrespective to the number of rows affected.

These triggers are created at the table level. Upon successful creation of trigger, we can see the triggers by navigating to Triggers folder at table level.

                            CREATE TRIGGER TR_UPD_Locations ON Locations
                            FOR UPDATE 
                            NOT FOR REPLICATION 
                            AS
 
                            BEGIN
                              INSERT INTO LocationHist
                              SELECT LocationID
                                ,getdate()
                              FROM inserted
                            END
                        

Instead of triggers in SQL Server

These triggers are fired before the DML event and the actual data is not modified in the table. For example, if we specify an instead of trigger for delete on a table, when delete statement is issued against the table, the instead of trigger is fired and the T-SQL block inside the triggers in SQL Server is executed but the actual delete does not happen.

DDL Triggers in SQL Server

DDL triggers in SQL Server are fired on DDL events. i.e. against create, alter and drop statements, etc. These triggers are created at the database level or server level based on the type of DDL event.

These triggers are useful in the below cases.

Below is the sample syntax for creating a DDL trigger for ALTER TABLE event on a database w hich records all the alter statements against the table. You can write your custom code to track or audit the schema changes using EVENTDATA().

                           CREATE TABLE TableSchemaChanges (ChangeEvent xml, DateModified datetime)
 
                            CREATE TRIGGER TR_ALTERTABLE ON DATABASE
                            FOR ALTER_TABLE
                            AS
                            BEGIN
 
                                INSERT INTO TableSchemaChanges
                                SELECT EVENTDATA(),GETDATE()
 
                            END
                        

LOGON Triggers in SQL Server

These triggers in SQL Server fire in response to a LOGON event. LOGON triggers fire after successful authentication and before establishing the user session.

LOGON triggers are created at the server level and are useful below cases.

Direct recursion

Direct recursion is a case where the SQL Server trigger on the table is fired and performs an action which again triggers the same trigger.

Direct recursion can be controlled by a database setting RECURSIVE_TRIGGERS. If the setting is on, then the above trigger throws an error.

To change RECURSIVE_TRIGGERS setting using SSMS, navigate to the database, right click on the database and select Properties. Click on Options and change the setting to the option you want.

To set the RECURSIVE_TRIGGERS OFF using T-SQL, use below statement and replace the database name with your database name.

                            // FOR OFF
                                    ALTER DATABASE [AdventureWorks] SET RECURSIVE_TRIGGERS OFF WITH NO_WAIT
                                    GO
                            // FOR ON
                                   ALTER DATABASE [AdventureWorks] SET RECURSIVE_TRIGGERS ON WITH NO_WAIT
                                GO

SQL Server trigger order

SQL Server allows multiple triggers on the table for the same event and there is no defined order of execution of these triggers. We can set the order of a trigger to either first or last using procedure sp_settriggerorder. There can be only one first or last trigger for each statement on a table.

Not For Replication in SQL Server Trigger

NOT FOR REPLICATION indicates that the trigger should not fire when the replication agent syncs the data changes to the subscriber. For example, if you are replicating both Locations and LocationHist. Now when you update a record on Location the trigger is fired, inserts record in the history table. When these changes sync to another end (subscribers) there is no need of trigger to be fired again. So, if we mark the trigger for “NOT FOR REPLICATION” the trigger does not fire when replication agent sync’s the changes and fires only for the data changes done by the user.