What is Trigger in SqlServer

Trigger:

           A Trigger is a named T-SQL block like procedure.It is executed Implicitly.

     important steps: -

• Trigger can't be create without a table
• Trigger won't accept parameter.
• Trigger can't return values
• Trigger can't be called from front-end application

       Triggers are two types:

1. DML Triggers
2. DDL Triggers

     Triggers are created 

• To implement complex business rules.
• To provide security.
• To do complex validations.
• to do auditng.              

   DML Triggers : 

                             These Triggers are created on perticular table.These triggers are automatically executed whenever user performs DML operations over the table. By using these triggers we can control DML operations.

Syntax : 

           create |alter trigger<name> on <tabname>

           after|instead of insert,update, delete

           as

           begin

            ------ statements----------

           end 

       

       After Trigger: If the trigger is after trigger.the trigger code is executed automatically after the DML operations.

      examples: Create a trigger don't not allow any transaction on sunday on emp table ?

              create trigger trg1 on emp

             after insert,update,delete

             as 

             begin

             if datename(dw,getdate())='sunday'

             begin

               rollback

             raiserror('not allowed on sunday',15,1)

              end

              end

 check it:  update emp set comm=500 where deptno=10     (this query don't executing on Sunday).
 Instead of Trigger : These triggers are executed automatically instead of executing the DML operations.
 Example : Create a trigger don't to allow  more than four employees in a department?
                create trigger t1 on emp
                instead of insert
                as
                begin
                 declare @eno int, @dno int ,@cnt int
                      select @eno,@dno=dno from inserted (This is one of the type in magic table)
                       select @cnt=count(*) from emp where dno=@dno
                    if @cnt=4
                     raiserror('maximum four employees',15,1)
                     else
                      insert into emp values(@eno,@dno)
                     end


     DDL Triggers  :
                                    These triggers are created on database. These triggers are executed whenever user performs DDL operations. By using this triggers we can control the DDL operations.
               Syntax:
                          create trigger<name> on database
                          for DDL_operation  (Note: In DDL command any operations can be performed)
                          as
                          begin
                           ----Statement---------
                          end


         Example:
                       create trigger t2 on database
                        for drop_table
                         as
                        begin
                       print 'table droped successfully'
                       end