What is trigger

Why use triggers?

Triggers can be used to audit changes to the database
We can set up triggers on tables to record every change to every record in the table. Typically, this is accomplished by copying the record before it’s modified to a different table. A table we might call historical data.

Triggers can also be used to ensure the integrity of our data
SQL Server has some other ways to do this such as checks and constraints. But those are designed to be pretty simple. They check for a data type or they might check to make sure some value is greater than or less than some other value. Triggers allow for more robust logic. We can generate data integrity rules that implement complex business logic, that can reference multiple tables based on the value in those tables, decide if a certain piece of data is valid or invalid. Triggers can also be used to block mistakes. Triggers can undo an attempt to change data. They can even undo an attempt to change the structure of the database. So something like dropping a table can be blocked by a trigger.

Creating trigger in SSMS

Step-1

Creating trigger in ssms Step 1

Step-2

Creating trigger in ssms Step 2

Creating trigger using T-SQL

CREATE TRIGGER [schema_name.]trigger_name
ON table_name
AFTER  {[INSERT],[UPDATE],[DELETE]}
[NOT FOR REPLICATION]
AS
{sql_statements}

Details: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql

Example

CREATE TRIGGER reminder  
ON Sales.Customer  
AFTER INSERT, UPDATE, DELETE   
AS  
   EXEC msdb.dbo.sp_send_dbmail  
        @profile_name = 'AdventureWorks2012 Administrator',  
        @recipients = 'danw@Adventure-Works.com',  
        @body = 'Don''t forget to print a report for the sales force.',  
        @subject = 'Reminder';  
GO

After trigger

Creating after trigger using T-SQL

CREATE TRIGGER [dbo].[CategoryDeactivation]
ON [dbo].[Categories]
AFTER UPDATE
AS
BEGIN 
	DECLARE @isActive AS bit

	SELECT @isActive = Active
	FROM Inserted

	IF (@isActive = 0)
		UPDATE Products
		SET Active = 0
		WHERE CategoryID IN (SELECT CategoryID FROM Inserted)

END

Instead of trigger

Creating instead of trigger using T-SQL

CREATE TRIGGER [dbo].[CategoryDelete]
ON [dbo].[Categories]
INSTEAD OF DELETE
AS
BEGIN 
		UPDATE Categories
		SET Active = 0
		WHERE CategoryID IN (SELECT CategoryID FROM Deleted)
END

Database level trigger

Creating database trigger using T-SQL

CREATE TRIGGER safety 
ON DATABASE 
FOR DROP_TABLE, ALTER_TABLE 
AS 
   PRINT 'You do not have permission to drop or alter tables!' 
   ROLLBACK;