Wednesday, 15 February 2012

How to Create Triggers in sql server 2008


What is a trigger?
________________________________

                Trigger is a Special kind of Stored Procedure that is invoked whenever an 
Attempt is made to modify the data in the table the trigger will protects

                 A table Can have three trigger actions :
                        1.Update,
                        2.Insert,
                        3.Delete

Only Table owners can create and drop triggers for the table.Trigger is Attached to a table or View
and is fired only when an Insert,Update,Delete Occurs,While stored procedure executes at any time 
while is is called.

     Triggers are Mainly divided in to Two Categories:

      1. After trigger(FOR trigger) -----(AFTER INSERT,AFTER UPDATE,AFTER DELETE)
      2. Instead of Triggers 


A simple Syntax For triggers:
________________________________

             Create Trigger Trigger_Name 
             ON (Table or view) 
             AFTER Insert,Update,Delete
             AS
            
               DECLARE
                 //initialize parameters if any required like (@Empid int;) //
              
             BEGIN 


               // Insert ,Update ,Delete Stamt....//
                  
              PRINT('AFTER InSERT TRIGGER FIRED')
             
              END
               GO

Trigger Example For After Insert Trigger:
------------------------------------------

         First Create a Table With the Name product Having the columns like Below

        Create Table Product 
        (
          Product_id int,
          productName varchar(50),
          Address varchar(50)
        )
         





Trigger for After Insert :
_______________________________________

                    CREATE  TRIGGER Product_trigger
                    ON  dbo.Product
                    AFTER INSERT
                    AS 
                    DECLARE 
                    @Product_id int,
                    @productName varchar(50),
                    @Address varchar(50)
                    BEGIN
                Insert into  Product (Product_id,ProductName,Address)values(@Product_id,@ProductName,@Address)

                    END
                    GO


                 In the Above dbo.Product is a table Name And  Product_trigger is Trigger Name.


How to Eanble/Disable Trigger Associated Table:
________________________________________________

           Alter Table Table_Name  ENABLE/DISABLE Trigger ALL   -----(It is For All Tables In a Data Base)


            Alter Table Table_Name ENABLE/DISABLE Trigger Trigger_Name ( It is only For A Particuler 
                                                                           Trigger Assosiated Table )



              Ex: Alter Table Product ENABLE Trigger Product_trigger


Difference Between AFTER TRIGGERS AND INSTEAD TRIGGERS :
_________________________________________________________

    1.  Instead Triggers Only single Implementation is possible on table .Where as After Triggers Support a multiple triggers to a single Table
   
    2. Insted Triggers Applied on View .where As After Triggers Cant be Applied on View.

0 comments:

Post a Comment