Triggers in SQL SERVER

Definition: Triggers can be classified as a special type of stored procedure that executes automatically in response to some triggering action like INSERT, UPDATA, DELETE etc

Types of Triggers

1. DML Triggers
   
   1. AFTER / FOR Trigger
      
      1. AFTER DML INSERT Trigger
      2. AFTER DML DELETE Trigger
      3. AFTER DML UPDATE Trigger
   2. Instead of Trigger
      
      1. Instead of INSERT trigger
      2. Instead of DELETE trigger
      3. Instead of UPDATE trigger
2. DDL Triggers
3. LOGON Triggers

Note: Trigger uses two SQL tables called Inserted and Deleted

Inserted Table

[Inserted table is a special table used by sql server to keep a copy of a row that you have just inserted that is used by a trigger that]

Deleted Table

[Deleted table is a special table used by SQL server to keep a copy of a row that you have just deleted that is used by a trigger and the structure of deleted table is identical to the structure of the actual from which you have just deleted]

Syntax of Triggers in SQL Server

[AFTER/FOR TRIGGER]

CREATE TRIGGER <TriggerName>
ON <TableName>
FOR INSERT/DELETE/UPDATE
AS
BEGIN
<your SQL Statements>
END

[INSTEAD OF TRIGGER]

CREATE TRIGGER <TriggerName>
ON <TableName>
INSTEAD OF INSERT/DELETE/UPDATE
AS
BEGIN
<your SQL Statements>
END

[DROP THE TRIGGER]

DROP TRIGGER <TriggerName>

[ALTER THE TRIGGER]

ALTER TRIGGER <TriggerName>
ON <TableName>
FOR INSERT/DELETE/UPDATE
AS
BEGIN
<your SQL Statements>
END

OR

ALTER TRIGGER <TriggerName>
ON <TableName>
INSTEAD OF INSERT/DELETE/UPDATE
AS
BEGIN
<your SQL Statements>
END

Example of AFTER / FOR Trigger

create table emptab1
(
emp_no int constraint pk1 primary key,
branch varchar(3),
emp_name varchar(20),
ac_type varchar(10),
salary int
)

create table dttab
(
datentime varchar(100)
)
select * from emptab1
select * from dttab

We have the two blank tables named emptab1 and dttab

[Insert the data in one table and automatically some valid data will be inserted in another table]

Query 1a: Whenever we insert a row in emptab1, the trigger gets fired and date and time to which it is inserted, must be added in dttab table

-- create a trigger first--

create trigger tr_emptab1_forinsert
on emptab1
for insert
as
begin
insert into dttab values('new row is inserted in emptab1 at' + cast(getdate() as varchar(20)));
end

--Now insert the data in emp_tab1 automatically trigger gets fired and dttab will be inserted--

insert into emptab1 values(101,'B1','Jatin Nagpal','CA', 20000)
select * from emptab1
select * from dttab