4 Answers
4

This will prevent a trigger fired to call another trigger or calling itself again. To do this, execute this command:

ALTER DATABASE MyDataBase SET RECURSIVE_TRIGGERS OFF
GO

Use a trigger INSTEAD OF UPDATE, INSERT
Using a INSTEAD OF trigger you can control any column being updated/inserted, and even replacing before calling the command.

Control the trigger by preventing using IF UPDATE
Testing the column will tell you with a reasonable accuracy if you trigger is calling itself. To do this use the IF UPDATE() clause like:

ALTER TRIGGER [dbo].[tblMediaAfterInsertOrUpdate]
ON [dbo].[tblMedia]
FOR INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON
DECLARE @IdMedia INTEGER,
@NewSubject NVARCHAR(200)
IF UPDATE(UniqueTitle)
RETURN;
-- What is the new subject being inserted?
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

OFF Only direct recursive firing of AFTER triggers is not allowed. To
also disable indirect recursion of
AFTER triggers, set the nested
triggers server option to 0 by using
sp_configure.

Only direct recursion is prevented when RECURSIVE_TRIGGERS is set to OFF.
To disable indirect recursion, you
must also set the nested triggers
server option to 0.

The status of this option can be determined by examining the
is_recursive_triggers_on column in the
sys.databases catalog view or the
IsRecursiveTriggersEnabled property of
the DATABASEPROPERTYEX function.

When the title is getting 'updated' (read: inserted or updated), then update the unique subject. When the trigger gets ran a second time, the uniquesubject field is getting updated, so it stop and leaves the trigger.

Also, i've made it handle MULTIPLE rows that get changed -> I always forget about this with triggers.

ALTER TRIGGER [dbo].[tblMediaAfterInsert]
ON [dbo].[tblMedia]
FOR INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON
-- If the Title is getting inserted OR updated then update the unique subject.
IF UPDATE(Title) BEGIN
-- Now update all the unique subject fields that have been inserted or updated.
UPDATE tblMedia
SET UniqueTitle = dbo.CreateUniqueSubject(b.Title) +
CAST((b.IdMedia) AS VARCHAR(10))
FROM tblMedia a
INNER JOIN INSERTED b on a.IdMedia = b.IdMedia
END
END