T-SQL trigger effect a change before calling other code

Question

I have a table, let's call is [MYTABLE], with an FOR INSERT, UPDATE trigger.

The trigger needs to execute a stored procedure, which will do some work based on the changes made to [MYTABLE]. I can't move the stored procedure's code into the trigger.

So far, so good... since triggers execute after the changes are made, the stored procedure has no need to access the [inserted] or [deleted] metatables.

However... the trigger needs to change one additional field (a [LastModified] smalldatetime) so the stored procedure can use that data in its processing. This is not so the stored procedure can see what was inserted/updated... the procedure may do a number of things based on other records that weren't included in the update triggering it.

Problem is, if my trigger changes [LastModified], that will either do nothing at all (if I have recursive triggers turned off), or it will end up calling the stored procedure twice--once because of the original triggering change, and again because of my change to [LastModified].

How can I get around this so (a) [LastModified] gets updated with each change and (b) the stored procedure is only called after it has access to the new value of [LastModified]?

I have two ideas I'm thinking about, but they smell funny, so I'd rather see if there's a more straightforward solution.

Edit:

Ok, here are the solutions I have so far, maybe that will help the discussion:

1. Use two triggers. One, an "INSTEAD OF" trigger, would handle the user's update of the record and would change LastModified, but would RETURN quickly if the update is coming from the SP (it can tell based on what columns are modified). The other would be an "AFTER" trigger, which would call the EXEC. This trigger gets the updates with the LastModified column already applied by the INSTEAD OF trigger. At least I hope that's how it works.

2. Move ModifiedDate to another table. That way, I can have a single AFTER INSERT/UPDATE trigger that, only if the user initiates the INSERT/UPDATE, adds an audit record to the other table and calls the SP. The SP would then modify other records, which would cause the trigger to fire again, but it would quickly recognize the situation and RETURN without doing more work.

The disadvantage of the first solution is that I have to maintain a column list in the trigger so the INSTEAD OF update actually does the work intended (since I'm adding a column to the list, ModifiedDate, I can't just INSERT INTO tbl FROM inserted, I have to specify columns).

Answer 1

Have you tried the IF UPDATE(LastModified) instruction?

CREATE TRIGGER XYZ ON MYTABLE 
FOR INSERT, UPDATE 
AS 
BEGIN 
IF UPDATE(LastModified) 
  RETURN 
ELSE 
  BEGIN
    UPDATE MYTABLE SET LastModified = GETDATE() 
    FROM MYTABLE INNER JOIN INSERTED ON MYTABLE.ID = INSERTED.ID
    EXEC TheStoreProc
  END
END;

Answer 2

I'm not sure I understand the flow you are describing. Is it:

1. Record is updated
2. Update trigger proc is called
3. trigger updates LastModified field
4. trigger calls another proc

that should work fine, as long as the "another proc" does not update the same table, which would kick off the trigger again.

If the "another proc" is updating the table again, you could possibly move those updates into the trigger prior to calling "another proc".

Is that any help?

Ron