Infinite Looping Trigger

I have an after update/insert trigger on table x. In this trigger I need to check if a certain column has been updated which is simple enough doing a deleted/inserted table comparison. However, if a certain column has been changed to a certain value I need to update the inserted row in table x. Doing so obviously creates a loop. The issue is I am not having any luck trapping the loop at the start of the trigger.

The SQL I'm using is below, apologies for the awful temp table name.

FOR INSERT, UPDATE
AS 
BEGIN

SELECT  i1.wo_id,
        i1.wo_status,
        i1.wo_link_type,
        i1.wo_link 
INTO    #x_MLN16901 
FROM    inserted AS i1
WHERE   i1.wo_status = 7

DELETE 
FROM    #x_MLN16901 
WHERE   #x_MLN16901.wo_id IN
            (
            SELECT  d1.wo_id
            FROM    deleted AS d1
            WHERE   d1.wo_status <> 7 OR
                    d1.wo_link_type <> 'PM'
            ) OR
        #x_MLN16901.wo_id IN
            (
            SELECT  i2.wo_id
            FROM    inserted AS i2
            WHERE   i2.wo_link_type <> 'PM'
            )


IF  (SELECT COUNT(*) FROM #x_MLN16901) = 0
    RETURN

UPDATE  workorders
SET     workorders.wo_action_date = GETDATE()
WHERE   workorders.wo_id IN
            (
            SELECT  wo_id
            FROM    #x_MLN16901
            )

I understand this last update statement is the cuplrit, but I can not think of a way of doing this differently, or the best way to trap the loop at the beginning of the trigger.

I'm toying with the idea of using a global temp table and checking the inserted record set at the trigger start, exiting the trigger if the rows already exist in the global temp 开发者_如何转开发table. I'm assured by colleagues that this is likely over-complicating matters and a either won't work, or a simpler answer exists?

Any help is greatly appreciated. Tommy

---

Since you're only referencing three fields from the table (wo_status, wo_link_type, and wo_link), I assume you only need to fire this trigger when one of those are updated. So, change the update portion of the trigger so that it only fires if those specific fields are updated. Since you last update does not change any of those, this will remove the recursion.

The syntax is for update of <field1>, <field2>, .... See Oracle's documentation.

---

Can you not "switch off" the recursice trigger before you update teh value on the main table causing the inital trigger?

I believe you can.

Have a look at doing so on the secondary trigger.