views:

370

answers:

5

Hi folks,

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

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

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 kickng of another trigger agian?

A: 

You can have a separate NULLABLE column indicating whether the UniqueTitle was set.

Set it to true value in a trigger, and have the trigger do nothing if it's value is true in "INSERTED"

DVK
Why the down-vote?
DVK
+5  A: 

I see three possibilities:

  1. Disable trigger recursion:

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
  1. 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.

  2. 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
Rodrigo
Quick question. U're using _BEFORE_ instead of _AFTER_. Will this still give me the new Inserted (Identity) ID value? Or does that only get created on an _AFTER_ ??
Pure.Krome
Fixed. Fire the trigger after the insert/update
Rodrigo
Nope - not working. I'm doing an insert but the Update(UniqueTitle) must think it's an update... ???
Pure.Krome
More on 'IF UPDATE(..)' :: http://msdn.microsoft.com/en-us/library/aa258254%28SQL.80%29.aspx .. Quote => "ests for an INSERT or UPDATE action to a specified column.." :( no can do, then.
Pure.Krome
The UPDATE(Column) will be true if a value have been set for the column in the instruction that "triggered" the trigger.
Rodrigo
A: 

use trigger_nestlevel

Michael Buen
+1  A: 
ALTER DATABASE <dbname> SET RECURSIVE_TRIGGERS OFF

RECURSIVE_TRIGGERS { ON | OFF }

ON Recursive firing of AFTER triggers is allowed.

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.

Remus Rusanu
A: 

I think i got it :)

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
Pure.Krome