What is trigger

• A trigger is a special type of stored procedure that automatically runs when an event occurs in the database server
• From: https://www.sqlservertutorial.net/sql-server-triggers/
  • SQL Server triggers are special stored procedures that are executed automatically in response to events (database object event, database event, and server event)
  • SQL Server provides three type of triggers:
    • Data manipulation language (DML) triggers which are invoked automatically in response to INSERT, UPDATE, and DELETE events against tables
    • Data definition language (DDL) triggers which fire in response to CREATE, ALTER, and DROP statements
    • Logon triggers which fire in response to LOGON events
• Nested trigger: actions of one trigger cause the next trigger to fire is called nested triggers (can benested up to 32)
• More:
  • https://www.essentialsql.com/what-is-a-database-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

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

• It’s called this because it executes immediately after an insert, update or DELETE statement. The trigger allows the original statement to occur with no modifications. Then it immediately takes over and starts doing some work
• Every ‘after’ trigger is related to one table and is going to react to actions only on that table
• Inserted is a temporary table that’s only available inside of the trigger. It’s automatically created by the machine and it holds all of the new information that someone is trying to put into the table

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

• These triggers are executed as an alternative to an INSERT, UPDATE, or DELETE statement
• These triggers completely block the original statement leaving the data unchanged. However, inside the body of the trigger we can choose to modify data and we usually do
• The deleted table is a temporary table automatically created by the trigger that contains the data the user was trying to delete

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

• Trigger can be: table-level (CRUD), database-level (database events)
• These trigger fire whenever a statement is issued that would change the structure of that database

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;