sql - How do I prevent a database trigger from recursing?

Question

I've got the following trigger on a table for a SQL Server 2008 database. It's recursing, so I need to stop it.

After I insert or update a record, I'm trying to simply update a single field on that table.

Here's the trigger :

ALTER TRIGGER [dbo].[tblMediaAfterInsertOrUpdate] 
   ON  [dbo].[tblMedia]
   BEFORE INSERT, UPDATE
AS 
BEGIN
    SET NOCOUNT ON

    DECLARE @IdMedia INTEGER,
        @NewSubject NVARCHAR(200)   

    SELECT @IdMedia = IdMedia, @NewSubject = Title
    FROM INSERTED

    -- Now update the unique subject field.
    -- NOTE: dbo.CreateUniqueSubject is my own function. 
    --       It just does some string manipulation.
    UPDATE tblMedia
    SET UniqueTitle = dbo.CreateUniqueSubject(@NewSubject) + 
                      CAST((IdMedia) AS VARCHAR(10))
    WHERE tblMedia.IdMedia = @IdMedia
END

Can anyone tell me how I can prevent the trigger's insert from kicking off another trigger again?

Answer 1

Not sure if it is pertinent to the OP's question anymore, but in case you came here to find out how to prevent recursion or mutual recursion from happening in a trigger, you can test for this like so:

IF TRIGGER_NESTLEVEL() <= 1/*this update is not coming from some other trigger*/

MSDN link