How to prevent update trigger from firing on insert

Last Post 24 Apr 2008 08:17 AM by SQLUSA. 8 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
leowest
New Member
New Member

--
22 Apr 2008 09:03 AM
I have an insert trigger on TableA that updates Column1 in TableA. I also have an update trigger on TableA that I don't want to fire, or at least not to execute certain code, when a new record is inserted in the table. How can this be achieved?

I don't want to use a computed column instead of the update statement in the insert trigger -- the value has to be static, and I don't want performance hit when selecting from the table.

Thanks,
Leo
SwePeso
New Member
New Member

--
22 Apr 2008 12:51 PM
The real question is, what do the two different triggers do?

What does the INSERT trigger do?
What does the UPDATE trigger do?
leowest
New Member
New Member

--
23 Apr 2008 09:36 AM
create table [tblA] ([col_vb] varbinary(8), [col_str] varchar(18))

[col_vb] stores timestamps from another db
[col_str] has to be updated on insert to store a string with hex representation of [col_vb]

I'm using this to get a hex string from varbinary:
replace(upper(sys.fn_varbintohexstr([col_vb])),'X','x')

So far, I tried the following:

    1. Using "instead of insert" trigger to update [col_str]. It works, but I'll have to maintain triggers if anything changes.
    2. Making [col_str] a computed column. It works, but 1) there is a performance hit and 2) sys.fn_varbintohexstr actually returns nvarchar(max) -- not what I need.
    3. Making [col_str] a persisted computed column. "Create table" blows up, returning the following:

      Msg 211, Level 23, State 107, Line 1
      Possible schema corruption. Run DBCC CHECKCATALOG.
      Msg 0, Level 20, State 0, Line 0
      A severe error occurred on the current command. The results, if any, should be discarded.

SwePeso
New Member
New Member

--
23 Apr 2008 10:33 AM
So why do you need an UPDATE trigger?
leowest
New Member
New Member

--
23 Apr 2008 11:22 AM
To populate [col_str] on insert. I put the following in the insert trigger:

update [tblA]
set [col_str] = replace(upper(sys.fn_varbintohexstr([col_vb])),'X','x')
from [tblA] a join inserted i on a.[pk] = i.[pk]

This update statement fires the update trigger on [tblA] -- that's what I'd like to avoid.
leowest
New Member
New Member

--
23 Apr 2008 11:28 AM
Sorry, I misread your question. I need the update trigger -- there are certain things that are supposed to happen only on update.
leowest
New Member
New Member

--
24 Apr 2008 07:27 AM
I want to reliably intercept insert statements no matter where they come from (a stored proc, an ad-hoc SQL, etc.)
SQLUSA
New Member
New Member

--
24 Apr 2008 08:17 AM
So just exit the UPDATE trigger if INSERT is being performed.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/order2005grandslam
SwePeso
New Member
New Member

--
24 Apr 2008 12:26 PM
quote:

Originally posted by: SQLUSA
So just exit the UPDATE trigger if INSERT is being performed.
Excellent idea!
So how would you implement this check?


You are not authorized to post a reply.

Acceptable Use Policy