Results 1 to 2 of 2

Thread: Triggers

  1. #1
    Join Date
    Oct 2002

    Unanswered: Triggers


    I've worked with MySQL and Access backwards and forwards, and I know enough about SQL server to be productive (and dangrous!). I'm working on a VB.NET application for my company, and came across triggers in SQL Server 2000, which just might eliminate the need for me to finish this app.

    Is there somewhere that explains in detail (in an easy to understand way) what each part of a trigger is, and what happens for each one?

    Any help is appreciated .


  2. #2
    Join Date
    Dec 2002
    Czech Republic

    Re: Triggers

    --From BOL, modified

    CREATE TRIGGER trigger_name
    ON { table | view }
    [ WITH ENCRYPTION ] -- optional, trigger (TR) code cannot be seen simply
    { FOR | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } -- You can use AFTER as a replacement of FOR
    [ NOT FOR REPLICATION ] -- optional, replication will not fire trigger
    { sql query}

    In { sql query}:

    You can use special psedofunctions for update TR:
    UPDATE( column )

    Special tables (historic and futuristic views on modified object):
    for insert TR - inserted
    for delete TR - deleted
    for update TR - inserted,deleted
    These views are special

    ROLLBACK --When triggers are fired, new transaction is started, rollback will cancel all changes, but trigger code will continue processing code.
    RETURN statement will exit TR

    SQLSERVER2K is equiped with 2 types of triggers:
    AFTER TRIGGER -- trigger is fired after operation, but can perform rollback on it
    INSTEAD TRIGGER -- operation is virtual, trigger can perform any action

    BEFORE TRIGGER -- not implemented, use instead trigger
    SELECT TRIGGER -- not implemented, use SP

    There can be only one instead trigger for each operation and many after triggers.
    You can set first,middle,last order for after trigger by SP sp_settriggerorder,
    there can be one first, one last and many middle per one triggered object, default is middle.
    One triggered object can have up to 2,147,483,647-1 triggers (in 2,147,483,647 objects per database limit).

    'recursive triggers' db-option can change behavior of triggers; 32 recursive levels maximum.

    Trigger is fired only if user has permissions to perform operation without trigger !!!
    Rights in TR are derived from sa account with special exceptions.
    If you use dynamic code, user rights are used.

    Outputing statements SELECT,PRINT use only in debug
    Use RAISERROR to report error to user
    Add "SET NOCOUNT ON" after "AS", it will reduce data send to client.
    Fully qualify objects to avoid recompilation sysobjects -> dbo.sysobjects
    @@NESTLEVEL, TRIGGER_NESTLEVEL() will help with recursion


    --For more see BOL "CREATE TRIGGER" topic and "inserted tables" topic and other sources
    --Send me new info found.
    Last edited by ispaleny; 01-02-03 at 22:00.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts