Post
Ask Question

How to Create a Trigger in SQL

SQL Trigger

 
The SQL Trigger creates a DML, DDL or logon trigger. A trigger is a special type of stored procedure that automatically runs when an event occurs in a SQL database server.
 
DML triggers run 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. These triggers fire when any valid event fires, whether table rows are affected or not. For more information, see DML triggers.
 
DDL triggers run in response to a variety of Data Definition Language (DDL) events. These events primarily correspond to Transact-SQL CREATE, ALTER, and DROP statements, and certain system stored procedures that perform DDL-like operations.
 
Logon triggers fire in response to the LOGON event that's raised when a user's session is being established. You can create triggers directly from SQL statements or from methods of assemblies that are created in the Microsoft .NET Framework Common Language Runtime (CLR) and uploaded to an instance of SQL Server. SQL Server lets you create multiple triggers for any specific statement 
 
Similarly, if you write a Trigger for a delete operation on a table, it creates a table in memory named DELETED and then deletes the row.
 
Syntax  
  1. CREATE trigger employeeDetail1    
  2. ON EmpoyeeDetail        
  3. AFTER INSERT, UPDATE         
  4. AS print ('you can not insert, update and delete this table EmployeeDetails');        
  5. rollback;     

The DML Trigger with a reminder message   

 
The following DML trigger prints a message to the client when anyone tries to add or change data in the EmployeeDetail table in the Sample database.
 
Syntax  
  1. CREATE trigger reminder1  
  2. ON EmpoyeeDetail      
  3. AFTER INSERT, UPDATE       
  4. AS print ('you can not insert, update and delete this table EmployeeDetails');      
  5. rollback;   

The DML Trigger with a reminder e-mail message

 
The following example sends an e-mail message to a specified person (Rahul) when the EmployeeDetail table changes.
 
Syntax
  1. CREATE TRIGGER reminder2      
  2. ON EmployeeDetails      
  3. AFTER INSERT, UPDATE, DELETE       
  4. AS      
  5.    EXEC msdb.dbo.sp_send_dbmail      
  6.         @profile_name = 'Sample Administrator',      
  7.         @recipients = '[email protected]',      
  8.         @body = 'Don''t forget to print a report for the Details.',      
  9.         @subject = 'Reminder';      
  10. GO      
Example
 
 

Why and When to use a Trigger?

 
We use a trigger when we want some event to happen automatically on certain desirable scenarios.
 
Let's see an example
 
You have a table that changes frequently, now you want to know how many times and when these changes take place. In that case you can create a trigger that will insert the desired data into another table whenever any change in the main table occurs.
 

The DML AFTER Trigger to enforce a business rule between the purchase order header and vendor tables

 
The DML AFTER trigger can enforce a business rule.  Because CHECK constraints reference to only the columns on which the column-level or table-level constraint is defined, you must define any cross-table constraints (in this case, business rules) as triggers. The following example creates a DML trigger in the Sample database. This trigger checks to make sure the credit rating for the vendor is good (not 5) when there's an attempt to insert a EmpName  into the EmployeeDetail table. To get the credit rating of the vendor, the Vendor table must be referenced. If the credit rating is too low a message appears and the insertion doesn't happen.
 
Syntax 
  1. CREATE TRIGGER Purchasing.LowCredit ON Purchasing.PurchaseOrderHeader      
  2. AFTER INSERT      
  3. AS      
  4. IF (ROWCOUNT_BIG() = 0)    
  5. RETURN;    
  6. IF EXISTS (SELECT *      
  7.            FROM Purchasing.PurchaseOrderHeader AS p       
  8.            JOIN inserted AS i       
  9.            ON p.PurchaseOrderID = i.PurchaseOrderID       
  10.            JOIN Purchasing.Vendor AS v       
  11.            ON v.BusinessEntityID = p.VendorID      
  12.            WHERE v.CreditRating = 5      
  13.           )      
  14. BEGIN      
  15. RAISERROR ('A vendor''s credit rating is too low to accept new      
  16. purchase orders.', 16, 1);      
  17. ROLLBACK TRANSACTION;      
  18. RETURN       
  19. END;      
  20. GO           
  21. INSERT INTO Purchasing.PurchaseOrderHeader (RevisionNumber, Status, EmployeeID,      
  22. VendorID, ShipMethodID, OrderDate, ShipDate, SubTotal, TaxAmt, Freight)      
  23. VALUES (      
  24. 2      
  25. ,3      
  26. ,261      
  27. ,1652      
  28. ,4      
  29. ,GETDATE()      
  30. ,GETDATE()      
  31. ,44594.55      
  32. ,3567.564      
  33. ,1114.8638 );      
  34. GO      

Using a database-scoped DDL Trigger

 
The following example uses a DDL Trigger to prevent any synonym in a database from being dropped.
 
Syntax
  1. CREATE TRIGGER safety       
  2. ON DATABASE       
  3. FOR DROP_SYNONYM      
  4. AS       
  5. IF (@@ROWCOUNT = 0)    
  6. RETURN;    
  7.    RAISERROR ('You must disable Trigger "safety" to remove synonyms!', 10, 1)      
  8.    ROLLBACK      
  9. GO      
  10. DROP TRIGGER safety      
  11. ON DATABASE;      
  12. GO       

Using a server-scoped DDL trigger

 
This example uses a DDL trigger to print a message if any CREATE DATABASE event occurs on the current server instance, and uses the EVENTDATA function to retrieve the text of the corresponding Transact-SQL statement. For more examples that use EVENTDATA in DDL triggers.
 
Syntax 
  1. CREATE TRIGGER ddl_trig_database       
  2. ON ALL SERVER       
  3. FOR CREATE_DATABASE       
  4. AS       
  5.     PRINT 'Database Created.'      
  6.     SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')      
  7. GO      
  8. DROP TRIGGER ddl_trig_database      
  9. ON ALL SERVER;      
  10. GO      

Using a logon trigger 

 
The following logon trigger example denies an attempt to log in to SQL server as a member of the login_test login if there are already three user sessions running under that login.
 
Syntax 
  1. USE SAMPLE  ;      
  2. GO      
  3. CREATE LOGIN login_test WITH PASSWORD = '3KHJ6dhx(0xVYsdf' MUST_CHANGE,      
  4.     CHECK_EXPIRATION = ON;      
  5. GO      
  6. GRANT VIEW SERVER STATE TO login_test;      
  7. GO      
  8. CREATE TRIGGER connection_limit_trigger      
  9. ON ALL SERVER WITH EXECUTE AS 'login_test'      
  10. FOR LOGON      
  11. AS      
  12. BEGIN      
  13. IF ORIGINAL_LOGIN()= 'login_test' AND      
  14.     (SELECT COUNT(*) FROM sys.dm_exec_sessions      
  15.             WHERE is_user_process = 1 AND      
  16.                 original_login_name = 'login_test') > 3      
  17.     ROLLBACK;      
  18. END;      
  19.    

Viewing the events that cause a trigger to fire

 
The following example queries the sys.EmployeeDetails and sys.EmployeeDetail catalog views to determine which Transact-SQL language events cause trigger safety to fire. The trigger safety is created in example D found above.3
 
Syntax
  1. SELECT TE.*      
  2. FROM sys.EmployeeDetails AS TE      
  3. JOIN sys. EmployeeDetail AS T ON T.object_id = TE.object_id      
  4. WHERE T.parent_class = 0 AND T.name = 'safety';      
  5. GO      

Summary

 
In this article, you learned how to use a SQL  Create Trigger statement with various options.


Similar Articles