A trigger is a special kind of stored procedure, which executes automatically, when an Insert, update and delete event fires on the table. When a user tries to modify the data through DML(Data Manipulation language), statement at that point of time it makes trigger fire.

Let us see it with an example,

First, we can create two new tables, the first one is 'Employee', that is master table and another one is the trigger reflaction table, that is 'EmployeeTriggerAction'. We will see, when a user enters a new record in Employee Master table, at the same time trigger will be fired and we will insert that table's primary ID and Action name, which is fired.
  1. 1) Employee
  3. CREATE TABLE [dbo].[Employee](
  4. [Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
  5. [Name] [nvarchar](50) NULL,
  6. [Salary] [nvarchar](50) NULL,
  7. GO
  1. 2) EmployeeTriggerAction
  3. CREATE TABLE [dbo].[EmployeeTriggerAction]
  4. (
  5. [InsertedId] [int] NULL,
  6. [ActionFired] [nvarchar](50) NULL
  7. )
  8. Go
Now, let us create Insert trigger for Employee Master table.
  1. CREATE TRIGGER trg_InsertEmployee
  2. ON [dbo].[Employee]
  3. AFTER INSERT
  4. AS
  5. DECLARE @EmpId int
  6. BEGIN
  7. SELECT @EmpId=Id FROM INSERTED
  9. -- HERE WE CHECK FOR ID NOT NULL
  10. IF(@EmpId IS NOT NULL)
  11. BEGIN
  12. -- IF @EmpId IS NOT NULL THEN WE WILL INSERT THAT ID WITH ACTION NAME IN A NEW TABLE
  13. PRINT 'AFTER INSERT TRIGGER FIRED'
  14. INSERT INTO EmployeeTriggerAction(InsertedId,ActionFired) VALUES (@EmpId,'INSERT')
  15. END
  16. END
  17. GO
Now, let us insert new record in Employee Master table.
  1. INSERT INTO Employee(Name,Salary) VALUES ('Ravi','20000')
  1. SELECT * FROM Employee
  2. SELECT * FROM EmployeeTriggerAction
Hope it will be helpful for the beginners.