DML Triggers in SQL Server  :-

  • These triggers are created on particular table.
  • These triggers are created to control DML operators.

Syntax  :-

Create /alter trigger <name>

Or<tablename>/<viewname>

  After/instead of insert,update,delete →triggers event

  As 

Begin

Statements

End.

After triggers in SQL Server :-

These triggers are fired (executed) after the DML operation is executed.

INSTEAD OF triggers in SQL Server :-

These triggers fired instead of DML operation.

Example :-

Create a database trigger don’t allow any DML operation on Sunday.

Create trigger trg1 on emp

  After insert,update,delete

   As

   Begin

If datepart(dw,getdate ( ))=1

Begin

Rollback

Raiserror(‘Sunday not allowed’,15,1)

End

End.

                     ↓

Insert into emp(empno ,ename,sal)values(8800,’’ABC’,4000)

                     ↓

Select * from emp.

Example  :-

So, create a database trigger don’t allow any transaction before 10 A.M?

                     Create trigger trg2 on emp

                   After insert,update,delete

                    As

                   Begin

                If datepart(HH,getdate ( )<10)

                Begin

             Rollback

          Raiserror(‘invalid time’,15,1)

          End

         End.

                          ↓

       Set  implicit transaction on

 Update emp set sal=sal+1000.

Note  :- If table,trigger events are same instead of creating two different triggers write the code in one trigger.

Magic Tables in Triggers :-

Triggers using some tables are called as “magic table”.

        Magic table

       ↓               ↓

Inserted        Deleted

      ↓

Record affected by insert command is copied to inserted table.

Deleted  :-

  • Record affected by delete command is copied to deleted table.
  • The record affected by update command is copied to both inserted table and deleted table.
  • New record is copied to inserted table and old record is copied to deleted table.

 4000 →Update emp set sal=5000 Where empno=719 

Program :-  Create a database trigger to insert record into resign table when employee resigns  :-

Resign table  :-

empno doj Dor

            

Create trigger trg3

On emp

After delete

As

Begin

Declare @eno,int,@doj datetime

Select @eno=empno,@doj=hiredate

  From deleted

Insert into resign values(@eno,@doj,getdate ( ))

End.

                ↓

Delete from emp where empno=7369

                 ↓

Select * from resign.

Example program :-

            To create a trigger to convert  deptname,locations to uppercase when user inserts record into department.

            Create trigger trg4

             On dept

             Instead of insert

             As

             Begin

             Declare @dno int,@dname varchar(20)

                 Declare @loc varchar (20)

        Select @dno=deptno,@dname=dname,@loc =loc

          From inserted

         Insert into dept values(@dno,upper(@dname),upper(@loc))

               ↓

    Insert into emp values(10,’hr’,’hyd’)

                ↓

Select * from emp

                 ↓

10            HR       HYD

Create a database trigger do not to allow more than four employee in a dept.

             Create trigger trg1

            On emp22

     Instead of insert

     As

    Begin

  Declare @eno int,@ename varchar (20)

  Declare  @dno int,@cnt  int

Select @eno=empno ,@ename=ename,

                   @dno=dno from inserted

Select @cnt=count(*) from emp22

                   Where dno=@dno.

If @cnt=4

Raiserror(‘max 4 emps’,15,1)

Else

Insert into emp22 values (@eno,@ename,@dno)

End.

Example  :-

                       Jobs

Job            lowsal          highsal

Clerk         1000             2000

Manager    2001            4000

Salesman   1500            3000

Program  :-

                   Create trigger trg2

                 On emp                              →to check the salary range of employee

               After insert

                As

               Begin

           Declare @job varchar (20),@sal smallmoney

          Declare  @sal smallmoney,@hsal  smallmoney

         Select  @job=job,@sal=sal from inserted

Select @sal=lsal,@hsal=hsal from jobs

                    Where job=@job

If @sal not between @lsal  and @hsal

         Begin

    Rollback

Raiserror(‘sal out of range’,15,1)

End

End.

Auditing in SQL Server  :-

Example  :

         Emp_audit

Uname    operation      dtime        new_eno        new_ename       new_dno    old_eno    old_ename

Dbo           insert         —–               1                    x                           30           null              null

Dbo           delete        ——              null              null                        null           0                 x

Program  :-

          Create table emp_audit(uname varchar(20)),operation varchar(20),otime  datetime,new_eno int,new_ename   varchar(20),new_dno  int,old_eno  int,old_ename varchar(20),old_dno   int)

  •   Create trigger trg3

     On emp22

After insert,update,delete

As

Begin

Declare   @cnt1   int,@cnt2   int

Declare  @eno  int ,@ename varchar (20)

Declare @dno  int

Select   @cnt1=count(*)from inserted

Select @cnt=count(*)from deleted.

Select @cnt2((@cnt1=0)and(@cnt2=0))

Begin

Select @eno=empno,@ename=ename,@dno=dno

   From inserted.

Insert into emp_audit values (user_name ( ),insert,

               Getdate ( ),@eno,@ename,@dno,null,null,null)

End

Else if((@cnt1=0)and(@cnt2=1))

Begin

Select @eno=empno,@ename=ename,@dno=dno

             From deleted.

Insert into emp_audit values(user_name(),’delete’,getdate ( ),null,null,null,@eno,@ename,@dno)

End.

End

{else((@cnt1=1)and (@cnt2=1))

    Begin

Select @eno=empno,@ename=ename,@dno=dno from up

Insert into emp_audit values(user_name ( ),’update’}

Updating complex views  :–

Initial of trigger can be used to update complex views.

Create view v10

As

Select e.empno,e.ename,e.sal,d.deptno,d.dname

From emp e,dept d

Where e.deptno=d.deptno

Insert into v10 values(——-)

      ↓

Here complex views doesn’t allow DML operation.

Program  :-

   Create trigger trg5

On v10

Instead of insert

As

Begin

Declare @eno  int,@ename varchar(20)

Declare @sal smallmoney,@dno int

Declare @dname  varchar (20)

Select @eno=empno,@ename=ename,@sal=sal,

@dno=deptno,@dname=dname

   From inserted.

Insert into dept values(@dno,@dname,null)

Insert into emp(empno,ename,sal,deptno)

           Values(@eno,@ename,@sal,@dno)

End.