DDL Triggers in SQL Server – SQL Server Tutorial

DDL Triggers in SQL Server :-

  • It is introduced from sql server 2005.
  • This triggers are created to control DDL operations like create,alter,drop.

Syntax :–  create trigger <name>

                        On database

                    After create ,alter,drop

                  As

                 Begin

Statements

               End

Example  :-   Create trigger trg10

                        On database

                         After create create_table

                          As

                           Begin

                          Print ‘table is created’

                                    End.

                                      ↓

              Insert      table xyz(x int)

                                      ↓

                   Table is created

How many instead of insert trigger can be created on a table ?

Answer:- one

Drop trigger trg1 

DML Triggers in SQL Server – SQL Server Tutorial

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.

Database Triggers in SQL Server – SQL Server Tutorial

Database Triggers in SQL Server:-

  • A database trigger is also a ‘named block’ like procedures executed implicitly.

Procedures in SQL Server :                          

  1. Called explicitly                               
  2. Procedure can be created  without a table.
  3. Procedure accepts ‘parameters’.
  4. procedure returns a value.

Triggers in SQL Server:

  1. Called implicitly.
  2. Trigger can’t be created without a table.
  3. Trigger won’t accept parameters.
  4. Trigger won’t return a value.

Triggers are mainly created for:

  • To implement complex business rules & validations.
  • To do auditing.
  • To provide security.

Business rules are Two Types:

Declarative : Integrity constraints.

Procedural:  Database triggers.

Trigger Types in SQL Server  :-

It can be divided into two types :

  1. DML trigger
  2. DDL triggers

Table Valued Functions in SQL Server – SQL Server Tutorial

Table Valued Functions in SQL Server  :-

  • These functions returns set of records  i:e table.
  • The return type of this functions must be “table”.
  • The return expression is  ”select statement”.

Syntax :-

               Create/alter function <name> (parameters)

                  Returns table

                   As

             Return(select statement).

Example  :-

           Create function getemp(@d  int)

           Returns table

                As

           Return(select * from emp where deptno=@d)

How to execute the function:

{The table valued functions are called in from clause of select statement}

Select * from dbo.getemp(10)

 

{i:e  it executes the all employees worked in 10th department}

Drop procedure in SQL Server  :-

      Drop procedure <name>

Drop function in SQL Server :-

           Drop function<name>

Function Types in SQL Server – SQL Server Tutorial

Function Types in SQL Server   :-

It can be divided into two types

  1. Scalar valued
  2. Table valued

Scalar Valued function in SQL Server:-

These functions returns only one value of type int,varchar date time etc.

Syntax  :–

                 Create/alter function <name>(parameters)

                     Returns <type>

                          As

                       Begin

                       Statements

                    Return<exper>

                         End.

Example  :-

            Create function addnum(@x int,@y int)

                  Returns  int

                      As

                    Begin

                   Declare @z int

                Set @z=@x+@y

                   Return @ z.

                  End.

Executing the function using select statement  :

Select dbo.addnum(10,20)

 Executing the function from another tsql block :

               Declare @ a  int,@b int,@c int

                    Set @a=30

                     Set @b=50

                       Set @c=dbo.addnum(@a,@b)

                   Print @c

Program  :

Create a function to calculate employee experience?

                    Create function expr( @e int)

                       Returns int

                            As

                         Begin

                       Declare  @doj datetime,@x int

                  Select      @doj=hiredate  from    emp

                                             Where empno=@e

                        Set @x =abs(datediff(yy,getdate(),@doj))

                        Return @x

                           End

                                  ↓

                           Select dbo.expr(7566)

                                   ↓

Write a program to print all employee names and their experience?

                                  ↓

Select ename,dbo.expr (empno) from exp.

Write a program to calculate the employee experience,if experience≤30 then increment the emp salary by 10% otherwise delete the record from table :-

                   Declare c1 cursor for

                        Select empno from emp                           →hiredate

                 Declare @eno int,@x int                                →@doj date time

                     Open c1

                Fetch next from c1 into @eno                                  →@doj

                While(@@fetch_status=0)

                     Begin

                      Set @x=dbo.expr(@eno)

                        If @x<=30

                    Update emp set sal=sal*1.1

                             Where emp no=@eno

                              Else

                          Delete from emp where empno=@eno

                       Fetch next from  c1 into @eno

                               End

                         Close c1.

Example  :-

                    Create function expr(@doj datetime)

                    Returns int

                     As

                    Begin

                     Declare @x int

               Set @x=abs[datediff(yy,getdate(),@doj))

                  Return @x

                   End

Acct_mast:

          Accno    accname     bal

Acc_trans:-

Trid       ttype tdate         tamt         accno.

procedure for new account  :-

Create  procedure    new_acct(@ a   int,@n  varchar(20),@b  money)

                        As

                       Begin

              Insert into acct_mast values(@a,@n,@b)

                 End.

                    ↓

Exec   new_acct 1,’A’,5000.

Procedure for {closing} close_acct :-

              Create procedure close_acct(@a int)

                    As

                   Begin

                Delete from   acct_mast where accno=@a

                   End.

Procedure for credit  :-

            Create procedure credit(@a int,@amt money)

               As

              Begin

             Update acct_mast set bal=bal+@amt

                             Where accno=@a.

              Insert into acct_trans(ttype,tdate,tamt,acno)

                                      Values (‘D’ ,getdate(),@amt,@a)

                       End.

Procedure for Debit  :-

  Create procedure debit(@a  int,@amt money)

   As

  Begin

Update acct_mast set bal=bal-@amt

                    Where accno=@a

  Insert into acct_trans(ttype,tdate,tamt,accno)

           Values(‘w’,getdate ( ),@amt,@a)

  End.

Procedure for money transfer :-

          Create  procedure transfer(@s  int,@t int,@amt money)

            As

     Begin

     Update acct-mast set bal=bal-@amt  where accno=@s

      Update acct-mast  set bal =bal+@amt  where accno=@t.

      Insert into acct_trans(ttype,tdate,tamt,accno)values 

                                      (‘w’,getdate ( ),@amt,@s).

         Insert into acct-trans(ttype,tdate,tamt,accno)

                   Values(‘D’,getdate ( ),@amt,@t).

                End.

@@Rowcount  :-

It returns no. of records effected by the sql statement.

         →update

                @c1=@@rowcount

                  Update

                @c2=@@rowcount

                    Insert

                    Insert

                If @c1=@c2

               Commit

                 Else

                  Rollback.

Procedure for balance  :

          Create function balance(@a  int)

              Returns money

            As

         Begin

      Declare @bal money

    Select  @bal =bal from acct-mast

                      Where accno=@a

         Return @bal

          End.