Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Query about sql server trigger

Answered Query about sql server trigger

  • Sunday, March 03, 2013 7:16 PM
     
      Has Code
    Sign In to Vote
    0

    i knew that sql server trigger has no any before or after trigger but i was searching google and found many links regarding sql server after trigger. here is one sample of sql server trigger

    CREATE TRIGGER [dbo].[insupddel_yourTable] ON [yourTable]
FOR INSERT, UPDATE, DELETE
AS
BEGIN
-- Code as follows
    END

    1) here nothing specified for before or after. so just tell me when this trigger will fire ?? does this trigger fire before updating table or after updating table ? tell the default nature of  sql server trigger firing sequence when no after & before will be specified.

    here i am pasting another trigger sample

    CREATE TRIGGER dbo.TableName_IUD
ON dbo.TableName
AFTER INSERT, UPDATE, DELETE
AS 
BEGIN
 -- HERE is code
END

    2) the above trigger seems that it will fire after updating table. am i right ?

    from which sql server version the after and before trigger is available ? please discuss the nature of after and before trigger ?

    3) also tell me when one should use instead of trigger ? when instead of trigger is required. please explain with sample code. thanks

     

All Replies

  • Sunday, March 03, 2013 7:55 PM
     
     Answered
    Sign In to Vote
    0

    SQL Server has always had AFTER triggers, at least as long I as I have worked with the product. The keyword AFTER was not permitted until SQL 2000, though. These two are equivalent:

    CREATE TRIGGER tri ON tbl FOR INSERT
    CREATE TRIGGER tri ON tbl AFTER INSERT

    It is correct that SQL Server does not have BEFORE triggers, but there is INSTEAD OF triggers. INSTEAD OF triggers were added to permit update of views that were not updatable directly.

    INSTEAD OF triggers are a bit like BEFORE triggers, but since you have to redo the statement, they are less appealing, unless you actually want to modify the statement that fired the trigger. Or if you have something as brutal as:

    CREATE TRIGGER tri ON tbl INSTEAD OF DELETE AS
      ROLLBACK TRANSACTION
      RAISERROR('Deletes are ont permitted on this table, period!', 16, 1)
      RETURN

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
     
  • Monday, March 04, 2013 9:46 AM
     
     Answered
    Sign In to Vote
    0
    You can find and example of INSTEAD OF trigger here: http://sqlandme.com/2011/05/02/inserting-to-a-view-instead-of-trigger-sql-server/

    - Vishal

    SqlAndMe.com