How to use Triggers in Sql Server | What are different Type of Triggers
Triggers are used to execute a batch of SQL code when insert or update or delete commands are executed against a table. It can be executed automatically on insert, delete and update operations.
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. There are 4 types of DML triggers and they are:
CREATE TRIGGER TR_UPD_Admissions ON admissions FOR UPDATE NOT FOR REPLICATION AS BEGIN INSERT INTO Enquries SELECT studentname,getdate() FROM inserted END
Instead of Trigger
These triggers are fired before the DML event and the actual data is not modified in the table.
A typical example of using an INSTEAD OF trigger is to override an insert, update, or delete operation on a view.
Suppose, an application needs to insert new admission into the admission table. However, the new admission should be stored in another table called admission_payments for checking whether payment actually received from student, before creating a new tuple admission table.
CREATE TRIGGER TR_INS_Admissions ON admissions INSTEAD OF INSERT AS BEGIN SET NOCOUNT ON; INSERT INTO admission_payments ( student_name, amount ) SELECT i.student_name, amount FROM inserted i WHERE i.student_name NOT IN ( SELECT student_name FROM admissions ); END
List DML triggers in a database?
Select * from sys.objects where type='tr'
Where can we can see DML Triggers
Enabling and disabling DML triggers on a table
Navigate to triggers folder at the table level (as shown in image above), Right click on desired trigger and Click on Enable/Disable to Enable or disable the trigger using SSMS.
Enable / Disable through TSql
DISABLE TRIGGER TR_INS_Admissions on admissions ENABLE TRIGGER TR_INS_Admissions on admissions
Delete DML triggers on a table
Navigate to triggers folder at the table level (as shown in image above), Right click on desired trigger and select delete using SSMS.
Delete Trigger through TSql
DROP TRIGGER TR_INS_Admissions
DDL triggers are fired on DDL events like on 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.
Prevent changes to the database schema
Audit database schema changes
To respond to a change in the database schema
ALTER TABLE Trigger
records all the alter statements against the table. You can write your custom code to track or audit the schema changes using EVENTDATA().
CREATE TRIGGER TR_ALTERTABLE ON DATABASE FOR ALTER_TABLE AS BEGIN INSERT INTO TblSchemaChanges SELECT EVENTDATA(),GETDATE() END
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.
To audit login activity
To control the login activity
CREATE TRIGGER [track_logins] ON ALL SERVER FOR LOGON AS BEGIN INSERT INTO LoginActivity SELECT EVENTDATA(),GETDATE() END
Direct recursion Trigger
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.
CREATE TRIGGER TR_UPD_Admissions ON admission FOR UPDATE AS BEGIN Update admission set DateUpdated =GETDATE() END
Where can we can see DDL Triggers
Expand the database and navigate to Programmability -> Database Triggers.
Enabling and disabling DDL triggers on a table
ENABLE TRIGGER TR_DATABASEEVENTS ON DATABASE DISABLE TRIGGER TR_DATABASEEVENTS ON DATABASE
Delete DML triggers from database
DROP TRIGGER TR_DATABASEEVENTS ON DATABASE
Enabling and disabling LOGON triggers
Use below T-SQL syntax to disable or enable the LOGON trigger.
ENABLE TRIGGER track_logins ON ALL SERVER DISABLE TRIGGER track_logins ON ALL SERVER
Edit RECURSIVE TRIGGERS setting using SSMS
Right click on the database and select Properties. Click on Options and change the setting to the option you want.