INSTEAD OF TRIGGER

Question

INSTEAD OF TRIGGER

eletuw

SSCrazy

Points: 2464
More actions
September 5, 2003 at 1:43 pm

#84328

I need to audit a table using instead of update trigger. Since this update trigger will fire instead of the actual update, how do I get the update done once the trigger has fired. The table has about 20 columns with identity insert and some text columns.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

ray_higdon SSCarpal Tunnel Points: 4589 More actions · Answer 1

Can you explain why you would not allow the update so that you code everthing in the trigger? If you are doing this for auditing reasons, you can allow the updte to occur and just insert into a second table the audit event.

Ray Higdon MCSE, MCDBA, CCNA

------------

Ray Higdon MCSE, MCDBA, CCNA

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 2

You can perform the update within the INSTEAD OF trigger itself. So long as you don't have recursive triggers turned on. See the example in Books Online for INSTEAD OF INSERT where the INSERT is called within the trigger itself to put the values in the table.

I'm curious like Ray. Are you auditing on the text columns? If not, you are probably better off auditing using the standard AFTER triggers as Ray has suggested.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/

K. Brian Kelley
@kbriankelley

Antares686 SSC Guru Points: 125444 More actions · Answer 3

quote:
I need to audit a table using instead of update trigger. Since this update trigger will fire instead of the actual update, how do I get the update done once the trigger has fired. The table has about 20 columns with identity insert and some text columns.

I would also like to know what you want to accomplish. Many times you can look at if UPDATE(col_name) in the trigger and make an additional UPDATE to handle your things without the instead of. Also as bkelley points out if you are making an update based on the update watch you don't block yourself. You can also chek with @@nestlevel. See BOL for items mentioned.

eletuw SSCrazy Points: 2464 More actions · Answer 4

I am logging changes to all columns and the text columns make it necessary for me to use instead of trigger. If a user updates jsut one column out of the 20 how do I handle appplying the update after the trigger has fired.

ray_higdon SSCarpal Tunnel Points: 4589 More actions · Answer 5

You don't have to use an instead of trigger to track all changes, you can right the audit part in a standard trigger, How are you auditing and I'd reccomend we look at writing a for insert,update trigger. If you share your DDL and DML to code audits, I'm sure myself or someone else in forums can help.

Ray Higdon MCSE, MCDBA, CCNA

------------

Ray Higdon MCSE, MCDBA, CCNA

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 6

If he's having to audit a text field, the the INSTEAD OF trigger is necessary, unfortunately. The AFTER trigger doesn't allow references to text, ntext, and image columns but the INSTEAD OF trigger does.

As far as handling the update after the trigger fires, the inserted and deleted tables should contain all columns for the rows that change. The deleted table will contain the rows as they exist before the operation, the inserted table contains the rows as they would be after the update. You can call an UPDATE statement within the trigger using the inserted table as the source (I'm making the assumption the primary key columns didn't change). Take a look at Example C for the UPDATE command in Books Online. It's calling UPDATE using info from another table.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/

K. Brian Kelley
@kbriankelley

ray_higdon SSCarpal Tunnel Points: 4589 More actions · Answer 7

Ah, I did not know that about text columns, shows how often I work with them, thanks for the enlightenment Brian.

Ray Higdon MCSE, MCDBA, CCNA

------------

Ray Higdon MCSE, MCDBA, CCNA

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 8

quote]If the compatibility level is 80 or higher, SQL Server allows the update of text, ntext, or image columns through the INSTEAD OF trigger on tables or views.[/quote]

eletuw SSCrazy Points: 2464 More actions · Answer 9

Thanks to you all. It is clear that I can not do the update after using the instead of trigger. it is much easier to handle an instead of insert.

An instead of delete is also difficult to handle if the whole row in the table is not deleted. That is if only a column valus is changed.

This table holds client details and any column change must be audited. Since the columns include text columns I need to use an instead of trigger.