Home > Database, SQL Server > What are triggers?

What are triggers?

Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table.

In SQL Server 6.5 you could define only 3 triggers per table, one for INSERT, one for UPDATE and one for DELETE. From SQL Server 7.0 onwards, this restriction is gone, and you could create multiple triggers per each action. But in 7.0 there’s no way to control the order in which the triggers fire. In SQL Server 2000 you could specify which trigger fires first or fires last using sp_settriggerorder

Triggers can’t be invoked on demand. They get triggered only when an associated action (INSERT, UPDATE, DELETE) happens on the table on which they are defined.

Triggers are generally used to implement business rules, auditing. Triggers can also be used to extend the referential integrity checks, but wherever possible, use constraints for this purpose, instead of triggers, as constraints are much faster.

Till SQL Server 7.0, triggers fire only after the data modification operation happens. So in a way, they are called post triggers. But in SQL Server 2000 you could create pre triggers also.

Refer SQL Server 2000 books online for triggers.

Categories: Database, SQL Server
  1. November 10, 2006 at 12:29 am

    Nice blog, Keep up the good content

  2. November 10, 2006 at 2:02 am

    Thanks Josh!!

  3. Murugan
    January 18, 2007 at 3:25 am

    Very nice and good explanation. Clearly easy to observation of concept.

  4. Reddy
    June 1, 2007 at 7:29 am

    very gud explanation….i got it easily

  5. vikrant
    October 8, 2007 at 9:46 am

    nice content, very easy to understand

  6. Ankur
    March 15, 2008 at 7:29 am

    So Useless it is,focuss on point

  7. Ankur
    March 15, 2008 at 7:32 am

    I love you supriya bhayana

  8. Gulla
    March 27, 2008 at 1:35 am

    not an elegant explanation.. 😦

  9. Pranjal
    April 25, 2008 at 2:19 pm

    good for the basic but not for more……..

  10. jojo
    May 30, 2008 at 11:22 am

    This content are not sufficient and a bit confusing …………….pls be specific in some term.

  11. fayaz ahmad
    March 29, 2009 at 1:52 pm

    this contect not a specific one it should be more informative and ellobrated with a nice example

  12. venkatakumar
    September 19, 2009 at 4:40 am

    Good content , easy for starters………………….

    • Sasi
      May 30, 2011 at 9:46 am

      poda it is not good. i have some more good explanation abt it…..

  13. Bond
    October 7, 2009 at 9:31 am

    sicne this information is fair enough to brief about trigger, still there are verious types of trgiggers which they failed to explain

  14. November 27, 2009 at 8:59 am

    nice and easy explanation for triggers .

  15. Pradeep kumar
    April 29, 2010 at 5:25 am

    NIce work… the basic idea about triggers are explained well. Post more about about triggers in SQL Server 2005/2008

  16. September 16, 2010 at 8:58 am

    Nice info 🙂

  17. SRIRAm
    October 11, 2010 at 7:09 pm

    yap, easy to understand……

  18. March 31, 2011 at 1:23 pm

    It makes me happy bcoz i could solve solve my query…thanks a lot

  19. jatin
    July 27, 2011 at 5:25 pm

    developer can’t go with this sooory…

  20. nivvya
    January 18, 2013 at 4:57 pm

    not bad !!!!

  21. nivya
    January 18, 2013 at 4:58 pm

    nivvya :
    not bad !!!!

  22. July 7, 2014 at 8:08 pm

    Just desire to say your article is as astonishing.

    The clearness to your submit is simply nice and i can think
    you’re a professional in this subject. Well with your permission allow me to grasp your feed
    to keep up to date with impending post. Thanks one million and please continue
    the enjoyable work.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: