Hi!

You are absolutely right!

but here is the correct version that works with multiple rows

 UPDATE [Table_Name]

 SET field1=i.field1,

field1=i.field1,

        'Put Here all the fields of the table
conn_string=i.conn_string,
  last_modified=getdate()  
 FROM inserted i
 INNER JOIN f6bo_Projectos  ON i.id_projecto = f6bo_Projectos.id_projecto

opss ... posted in the middle of editing

here is the general version:

 UPDATE [Table_Name]

 SET field1=i.field1,

       field1=i.field1,

        'Put Here all the fields of the table
        last_modified=getdate()  
 FROM inserted i
 INNER JOIN [Table_Name] ON i.id= [Table_Name].id

Thanks for your remark.

Tiago

Many people are confused with this type of triggers (INSTEAD OF). This article is just another example.

The "solution" suggested in the article has a number of deficiencies.

The first was picked up as it was not able to handle multi-row updates.

Secondly, even after the suggested correction, the trigger will only do the job if the Id column's value is not changed. Consider this example:

CREATE TABLE Inmate( Id INT, Nickname VARCHAR(30), DateChanged DATETIME)

INSERT Inmate( Id, Nickname ) VALUES( 1, 'Rocky' )

-- Create INSTEAD OF UPDATE trigger on Inmate ...

UPDATE Inmate SET Id = 2 WHERE Id = 1

As a result, there will be 1 row in the table -

1, 'Rocky', NULL

The update statement from the trigger will not update this row because Inserted.Id = 2 and the join with Inmate is empty.

INSTEAD OF triggers are a delicate tool and should only be used when required. In this case - a normal AFTER trigger would do the job.

UPDATE Inmate SET DateChanged = GETDATE()

FROM Inmate

INNER JOIN Inserted ON Inmate.Id = Inserted.Id

From the article:

One might be tempted to think that we would just need to change the last_modified field at the end of the trigger with the actual time and date and the magic would be done. But it's not so simple! The normal triggers which are called AFTER Triggers do not allow you to change the data on the virtual tables. So SQL Server 2000 has extended the power of triggers by letting you create a second kind of trigger, called an INSTEAD OF.

The author states -correctly- that AFTER triggers don't allow modification of the virtual tables. But he goes on to suggest that such modification is allowed in an INSTEAD OF trigger. This is nonsense - as the code he posted proves, the changes are made to the base table, not to the virtual tables. That can be done from both an AFTER and an INSTEAD OF trigger.

Two weaknesses in the suggested code have already been pointed out: the code fails on multi-row updates; the revised code (posted up-thread) fails if the primary key is changed. Let me add a third: the use of proprietary T-SQL code where ANSI standard code works just as well.

CREATE TRIGGER TriggerName ON [Table_Name]
AFTER UPDATE
AS
   UPDATE [Table_Name]

      SET  last_modified = CURRENT_TIMESTAMP 'this will do the magic
   WHERE EXISTS
 (SELECT *
   FROM   inserted
   WHERE inserted.id = [Table_Name].id)

If the table has a multi-column key, simply extend the WHERE clause of the subquery to include all the columns in the key.

Best, Hugo

In our application we use AFTER triggers to maintain an audit trail. We need to maintain a history of changes to the data. We know that this causes a huge performance degradation. Is there a better alternative to this solution?

Has anyone implemented stored procedures to improve performance? And does it really speed things up?

The way I look at it the following steps would be involved:

1. Select the row to update

2. Compare the changed values

3. Update the record

4. Insert the audit record.

Any suggestions

Thanks

You can use stored procedure if all your Create/Update/delete operations happen through stored procedures only. 

Definitly it will improve the performance.  I have implemented certain operations (not auditing) through  triggers and later moved it to stored procedures.  I can see the difference.

If you are inserting the audit record to another table, I believe stored procedure may be the best option as you know (from the stored procedure parameters) what fields you are going to update. Specially on insert, without touching the virtual / actual table you can insert the new record to the audit table.

> Not always! update on blob fields (text, ntext or image) will not fire triggers (This is applicable to both

> instead of and after triggers)

That's not true; I implemented audit triggers on tables with both text and image columns in them. The problem is that you can't refer to binary columns in the inserted and deleted tables. What you *can* do is join the inserted or deleted table to the base table and select data from the base table for the insert statement. In the case of the delete trigger, join deleted to the base audit table and select the newest audit row (for a particular ID) to get the final 'D' snapshot for that row.