Writing an Instead Of trigger for a table update

Question

What I need to do is when an update runs on my table, I need to try it, and if it works let the update happen and then run some additional code. If it errors, I need it to run some different code.

I am trying to do this by writing an "instead of" trigger for the table update. The idea is for it to try to update the table within a TRY-CATCH and perform the needed code depending on what happens.

The problem is getting the update to happen in the TRY block.  If I use the Update command, since the trigger is replacing the Update, it will just go into a recursive loop, right?  But if I try to use the data in the INSERTED and DELETED tables to manually do the update, won't I have a problem inserting the identity key? I can't just insert a new record as I need to keep the integrity of the existing ID. 

Any suggestions are welcome.

Answer 1

Using INSTEAD OF triggers do not call themselves when you UPDATE/INSERT.

Answer 2

I don't believe an instead of action will continue after an error, I would assume you would need to write a lot of defensive code to trap for potential errors to get something like this to work:

use tempdb
GO
create table tableName(PK int identity constraint tablenamepk primary key,
colname char(20))
GO
create table logtable(message varchar(max))
GO
Create trigger trgTableName  on tableName
INSTEAD OF update
as
--I am not longer in the SQL 2000 museum, Erland, although it was a wonderful stay
begin try
update tableName set colname=i.colname
from tableName 
join inserted i on i.Pk=tableName.pk
end try
begin catch
select Error_number()
insert into logtable(message) values(ERROR_MESSAGE())
end catch
print 'more processing goes here'
 
 insert into tableName(colname) values(REPLICATE('X',20))

update tableName set colname=replicate('Y',20)
update tableName set colname=replicate('Y',22)
GO
select * from tableName 
GO
select * from logtable 
GO

Answer 3

So what do you want to do really?
I don't think the INSTEAD OF trigger idea is going to work out. In a trigger XACT_ABORT is ON by default, so the transaction will be doomed when come to the CATCH handler. You can include a SET XACT_ABORT OFF in the trigger, but still there are far too many errors that doom the transaction for no reason to make this a viable option.
Or do you want to trap a specific error?

Answer 4

>> What I need to do is when an update runs on my table, I need to try it, and if it works let the update happen and then run some additional code. If it errors, I need it to run some different code. <<

This is not how you're supposed to write SQL. It's a declarative language, and you want to use procedural code. Bad data in your schema is supposed to be kept out by constraints not repaired after the fact. This is a kludge. Since you fail to post DDL, as per basic netiquette, were going to have some problems helping you.

When we created the instead of triggers in SQL, their purpose was to allow updatable views. Updatable views cannot be done deterministically with declarative referential integrity. If you want to look at that, it's a good computer science theorem.

>> .. won't I have a problem inserting the identity key? I can't just insert a new record [sic] as I need to keep the integrity of the existing ID <<

There's no such thing as an identity key. The identity table property is not even a real column, but account of attempted insertions into a given table that is proprietary to the old Sybase/SQL Server product. It is not relational at all! Also, you use the word "record" instead of the correct term "row"; you're still thinking in terms of procedural code with sequential files and sequential processing. This is sort of like trying to use Chinese verb tenses while speaking German with a Swedish accent :) You can force it to work, but you always look incompetent in your code will never run well

Why don't you follow forum rules, post DDL and sample data and then show us what you've done? 

Based on nothing you told us, my guess would be that your UPDATE statement should include a SET clause with lots of CASE expressions in it.

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 5

Hi GretchenF,

Please share us more detailed information about your requirement like your table structure or your trigger query or your expected result or some sample data. So that it will help us understand your issue more clearly. 

Thanks,
Xi Jin.

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].