TRIGGER IN SQL SERVER

    TRIGGER IN SQL SERVER:-

        TRIGGER  are logic which can be fire's whenever we are doing INSERT ,UPDATE    ,DELETE 

          operators on TABLE.

       

         2 different type of TRIGGER in SQL SERVER :-

             a.  AFTER TRIGGER :-AFTER TRIGGER can be execute after the INSERT  ,UPDATE, DELETE  operators happen on table 

              b.  INSERTED OF TRIGGER :- INSERTED OF TRIGGER can skip  INSERT ,UPDATE, DELETE   and it can be execute before the  update of  table

              

      For creating a TRIGGER we need 2 Tables 

     In 1st Table we will perform all the operators like INSERT UPDATE DELETE and In      2nd Table  TRIGGER will fire and provide us DATETIME for every INSERT ,UPDATE    ,DELETE queries

     a. ]  For creating a AFTER TRIGGER we need 2 table       

            our 1st table name is StudentRecord and 2nd Table name is StudentTriggerData 

             in StudentRecord table we have 3 field StudID, Studname ,StudAge 

          In StudentTriggerData we have 1 field RecordofStudents

       For writing Query of TRIGGER in StudentRecord we need to Right click on StudentRecord  then                                                                                                                 select trigger then New Trigger

 

            In New Trigger we will write query for INSERT ,UPDATE ,DELETE operators 

            (*)  Codes for AFTER TRIGGER

CREATE TRIGGER Studtrigger                 (provide name of triggerTable)

   

ON  StudentRecord                                     (table name of which table we r doing Insert ,update ,delete)

  

 AFTER  INSERT,DELETE,UPDATE        (here we r doing After Trigger )

AS 

BEGIN

insert into StudentTriggerData (RecordOfStudents) values(GETDATE());

                                  

                                                  ( whenever we r doing insert,update,delete 

                                                                             it fires StudentTriggerData and provide

                                                                              DATE n TIME of record on every 

                                                                               insert ,update ,delete  )

END

GO

Now we  add record on our StudentRecord table by right click on table and select  (Edit Top 200 Record)

                      

  Adding some Records 

  

Trigger will  invoke and provide  DATE n TIME in StudentTriggerData Tabel

      b.]   For creating a INSTEAD OF TRIGGER  :- we just need to mentioned Instead of at the place                                                                                              of   After and Instead of will skip the operators                                                                                            and not allow to add the record on table but it                                                                                              will add the Date n Time

  

           (*)  Codes for   INSTEAD OF TRIGGER

              

  

   CREATE TRIGGER Studtrigger                                               (provide name of TriggerTable)

   ON  StudentRecord                                   (table name of which table we r doing Insert ,update ,delete)

   Instead of  INSERT,DELETE,UPDATE                                    (here we r doing Instead of Trigger) 

   AS 

  BEGIN

insert into StudentTriggerData( RecordOfStudents ) values(GETDATE());

                                  

             

   END

   GO

 

    Whenever we are adding records instead of will not allowing to adding the records

  

But the DATE n TIME will be added of  Instead of Record in StudentTriggerData