Skip to content

Triggers

TRIGGERS :

Triggers are used for log purpose or Audit purpose.

For security purpose we are using Triggers.

Types :

  1. After triggers or For triggers.
  2. Instead of Triggers.
After triggers or For triggers :

These triggers will fire after insert or delete or update data in a table.

There are Magic Tables (virtual tables) in SQL Server that hold the temporal information of recently inserted and recently deleted data in the virtual table. INSERTED and DELETED are two types of magic tables in SQL Server.

Note :

In case of Insert ---inserted records will goes into Inserted magic table.

In case of Delete----deleted records will goes into deleted magic table.

In case of Update----old records will goes into deleted magic table, new records will goes into inserted magic table.

Syntax :

1
2
3
4
5
6
7
8
9
crete trigger triggername
on tablename
for insert/delete/update
as
begin 

---sql audit statements
(select * from inserted  or 
select * from deleted )
For insert :

ex :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
create trigger Tr_Employee_Forinsert
on employee
for insert 
as 
begin

     insert into employee_audit(id,name,salary,inserted_date)
     select id,name,salary,getdate() as inserted_date from inserted

end
For delete :

ex :

1
2
3
4
5
6
7
8
9
create trigger tr_Employee_ForDelete
on employee
for delete 
as
begin

    insert into employee_audit(id,name,salary,deleted_date)
    select id,name,salary,getdate() as deleted_date from deleted
end
For update :

ex:

1
2
3
4
5
6
7
8
9
create trigger tr_Employee_ForUpdate
on employee
for update 
as
begin

    insert into employee_audit(id,name,salary,updated_date)
    select id,name,salary,getdate() as updated_date from deleted
end

INSTEAD OF TRIGGER :

These are fire instead of triggering actions .

INSTEAD OF INSERT :

ex:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
create trigger tr_Employee_ForUpdate
on vw_emp_dept
instead of insert 
as
begin

   declare @deptid int
     select @deptid = deptno from DEPT
     join inserted on DEPT.dname=inserted.dname

    if (@deptid is null)
    begin
      select @@error 
    return
    end

        insert into EMP (empno,ename,job,mgr,sal,dept_no)
        select empno,ename,job,mgr,sal,@deptid from inserted

end

INSTEAD OF INSERT :

ex:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
create trigger tr_Employee_ForUpdate
on vw_emp_dept
instead of delete 
as
begin
     delete EMP from EMP
     join deleted on EMP.empno=deleted.empno


end

INSTEAD OF DELETE :

EX :

1
2
3
4
5
6
7
create trigger tr_emp_dept_instead1
on vw_emp_dept
as
begin 
     delete emp from emp
     join deleted on emp.empno=deleted.empno
end

INSTEAD OF UPDATE :

EX :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
create trigger tr_vw_emp_dept_insteadofupdate
on vw_emp_dept
as
begin
     if(update(dname))
     begin
          declare @deptid int

          select @deptid = deptno from dept
          join inserted on inserted.dname=dept.dname

        if (@deptid is null)
        begin 
          select @@error
          return
        end

        update emp set dept_no = @deptid from emp
        join deleted on  emp.empno=deleted.empno
      end
end