Infinite Looping Trigger

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

Sorry, being simple and can't see a way to reply to you guys. Thanks for your answers thus far.

@astander:

That's an interesting point, I'll have to look at it. I can't imagine I can disable a trigger while it's active/in-use but its worth a try. I'll let you know if I find anything worthwhile.

@Donnie:

I considered this myself, at first I simply used something similar to what is described here. After some testing however I came to realise that this built-in function considers an attempt to update the same information to a given column as an update which unfortunately the software using this database does consistently.

To explain breifly: If I use the following at the head of my trigger:

IF NOT UPDATE(a) THEN
RETURN

The following examples I believe describe the behavior

    CREATE TABLE test
(
a AS INT,
b AS INT
)
GO

INSERT INTO test SELECT 1, 2

--this will pass the above catch, which is fine

UPDATE test SET b = 3

--this will not pass the above catch, which is also fine

UPDATE test SET a = 1

--this will unfortunately pass the test, even though column a is already 1, which is why I need the awkward temp - inserted - deleted table comparisons.