"Erland Sommarskog" <esq***@sommarskog.se> wrote in message
news:Xns983187EA23FB7Yazorman@127.0.0.1...
> Robert Bravery (m*@u.com) writes:
> > Can I have an insert statement in an instead of insert trigger,
> > inserting into the same table as the insert trigger. Would this trigger
> > be repeatidly run
>
> This covered in the topic of CREATE TRIGGER in Books Online:
>
>    If an INSTEAD OF trigger defined on a table executes a statement
>    against the table that would usually fire the INSTEAD OF trigger again,
>    the trigger is not called recursively. Instead, the statement is
>    processed as if the table had no INSTEAD OF trigger and starts the
>    chain of constraint operations and AFTER trigger executions. For
>    example, if a trigger is defined as an INSTEAD OF INSERT trigger for a
>    table, and the trigger executes an INSERT statement on the same table,
>    the INSERT statement executed by the INSTEAD OF trigger does not call
>    the trigger again. The INSERT executed by the trigger starts the
>    process of performing constraint actions and firing any AFTER INSERT
>    triggers defined for the table.
>
> Note that this does not apply if you call a stored procedure which inserts
> into the table. In that case you will get an error. (Unless you are using
> a very old version of SQL 2000, where this condition was silently
ignored.)
>
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx