April 22, 2008 at 10:41 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 a performance hit when selecting from the table.
Thanks,
Leo
April 22, 2008 at 11:14 am
Are you saying that update trigger is fired when you insert a row to database? Do you call update on this table within insert trigger?
Piotr
...and your only reply is slàinte mhath
April 22, 2008 at 11:23 am
Yes, so the sequence is as this:
1. insert into TableA executed
2. insert trigger fires
3. insert trigger updates Column1 in TableA (update TableA executed)
4. update trigger fires in response to step 3
5. update trigger finishes
6. insert trigger finishes
April 22, 2008 at 12:51 pm
Ok, in lieu of replies I just replaced my "for insert" trigger with "instead of insert" one. The latter doesn't contain update statements and thus doesn't fire the update trigger. Probably not the most elegant solution, but it seems to be working ...
April 22, 2008 at 1:08 pm
May I ask, why do you update a column in a table in the insert trigger? Maybe another approach could be possible?
Piotr
...and your only reply is slàinte mhath
April 22, 2008 at 2:23 pm
Instead of an after/instead of insert trigger, would it be possible to add a default value to the column?
For example:
alter table MyTable
add constraint DF_Date default (getdate()) for DateColumn
That would make it so that column "DateColumn" would end up with the current date and time any time a row is inserted into "MyTable". No trigger needed. (Defaults are faster than triggers.)
You can, of course, use a value other than getdate().
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 22, 2008 at 3:05 pm
There is a varbinary(8) column in TableA, and there is an external system that can't digest it, so I created an extra column (varchar(18)) to contain the hex representation of the varbinary value. Insert trigger ensures that this extra column gets populated. The only other option I could think of was a computed column, and that's dynamic ...
I'm open to any suggestions though 🙂
April 22, 2008 at 3:14 pm
Well in this case instead of trigger is better. If performance is not that important, you can create a view instead of persisting this column and make that external application to read data from that view. The other option is to create a computed persisted column which you can even index if you can make it deterministic. Then you can get rid of triggers.
Piotr
...and your only reply is slàinte mhath
April 22, 2008 at 4:17 pm
Ah, persisted computed column -- I've never used it! That may be just what I need. Do you know if there is a performance impact while running queries that retrieve values from persisted computed columns (non-indexed) vs. regular columns?
April 22, 2008 at 4:37 pm
Since they are calculated only when a column (your varbinary) changes, there should be no performance hit when you read them. There's an article by Andy Warren about them:
http://www.sqlservercentral.com/articles/T-SQL/61764/
Piotr
...and your only reply is slàinte mhath
April 22, 2008 at 4:58 pm
Ok, tried this:
create table tblA (pk int not null primary key identity(1,1), col1 varbinary(8), col2 as convert(varchar(18),replace(upper(sys.fn_varbintohexstr(col1)),'X','x')) persisted)
and got this:
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.
If I don't use "convert" in the computed column, it becomes nvarchar(max), and again the external app is not going to like it.
April 22, 2008 at 6:01 pm
Interesting. I get the same result on my SQL 2008CTP. Database compatibility level doesn't make change. You can create such column as long as it is not persisted. I suspect that this undocumented fn_varbintohexstr has a bug inside..
When I use other function to convert, I can't persist the column because it is considered as nondeterministic.
Do not use this function, it may stop working.
I wonder if one can create a deterministic function converting varbinary to varchar. Anyone?
Piotr
...and your only reply is slàinte mhath
April 23, 2008 at 8:50 am
Well, MS decided to keep this function in SQL 2008, so they probably have some vested interest in it. Since I can't use it to create a persisted computed column, I'll just have to stick to my "instead of" trigger solution ... 🙂
April 23, 2008 at 8:57 am
Probably,
In the end it does the same thing 🙂
Piotr
...and your only reply is slàinte mhath
April 23, 2008 at 8:59 am
Leo Nosovsky (4/22/2008)
Ah, persisted computed column -- I've never used it! That may be just what I need. Do you know if there is a performance impact while running queries that retrieve values from persisted computed columns (non-indexed) vs. regular columns?
Selecting persisted computed columns works the same as any other column. No performance hit.
Of course, update and insert take a slight performance hit, but less than the one caused by a trigger.
I've tested this extensively.
(If you ever end up indexing the column, remove the persistence. Same performance, less disk space.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply