What is Triggers in SQL? | Triggers in SQL

What are the Triggers in SQL?

• A trigger is a special type of stored procedure that automatically gets invoked whenever a particular event takes place in the database server.
• Triggers can be applied on the database or on the table, view, or schema of that database with which the event is related or linked.
• A trigger may get fired when any of these statements or operations are encountered.

1. DML ( data manipulation ) statement:
   It includes various delete, insert or update operations.
   
   
2. DDL (database definition) statement:
   It includes SQL operations like Create, Alter, or Drop.
   
   
3. Database operation:
   It includes ServerError, LogOn, LogOff, StartUp, or ShutDown operations in the database server.

For example, a trigger can be invoked when any changes like a new row being added up, or any column being updated in a certain table are made.

Syntax:

We can create a trigger in the following given manner:

Create trigger <trigger_name>      // creates a trigger with the specified name

[before | After| instead of]          //  specifies when the trigger will be fired

{insert | update| delete}            // specifies at what event  the trigger will be fired

on  [table_name]                         // specifies the table with which event is                     

                                                               associated for the trigger

[for each row]

[trigger_body]                         //specifies the action to be performed by the trigger.

Before and after trigger:

• Before triggers are executed before the event associated with is takes place.
• After triggers are fired after the event associated with it gets executed.
• Instead of triggers are used to create triggers on a view.

EXAMPLE:

Consider a case in which whenever a new record is added in a table, that new record automatically gets recorded in a different table without any manual updating being used on the latter table, i.e, with the help of triggers.

Consider a table with name ‘emp’ has the following data currently:

We want to keep a record of all the new entries that will be added to this table in the future in a separate table.

 Let's create a new and separate table for the same  purpose lets say with the name new_entry:

create table new_entry(Id int, name varchar(20), department int, salary int)

A new table has been created. Now let's create the trigger for the above-mentioned purpose.

create trigger tr_trig 

       on emp

       after insert

       as begin

       declare @Id int, @Name varchar(20),@Department int, @Salary int

       select @id = Id , @Name = name , @Department= department, @Salary = salary from                     inserted

        insert into new_entry values(@id, @name, @Department, @Salary)

        end

• please note that “inserted” is a default table in SQL which contains all the latest and new records that are added in the database recently.

Now let's enter new values in the table employee1

insert into emp values(110,'meenu',222, 54321)

 Explanation: This entry will not only be entered in the ‘emp’ table but also automatically be added into the ‘new_entry’ table with the help of triggers.

Let's have a look at both the tables:

Select * from emp

Select * from new_entry

Here is the result of this query:

The new entry has been added to both the tables successfully.