Post reply

How to prevent insert/update trigger sequence?

  • April 23, 2008 at 11:48 am

    #806209

    Thanks for the info, GSquared 🙂

    Computed columns (persisted or indexed) definitely have merit, but it looks like they are not going to help me in this case 🙁

  • April 23, 2008 at 11:50 am

    #806211

    Thanks, Piotr -- I learned something new, even though it's not applicable in this case 🙂

  • April 23, 2008 at 12:30 pm

    #806235

    I just tried this:

    CREATE TABLE [dbo].[tblA](

    [pk] [int] IDENTITY(1,1) PRIMARY KEY,

    [col1] [varbinary](8) NULL,

    [col2] AS (CONVERT([varchar](18),[col1],0)),

    [col3] AS ([sys].[fn_varbintohexstr]([col1])),

    [col4] AS (CONVERT([varchar](max),[sys].[fn_varbintohexstr]([col1]),0)),

    [col5] AS (CONVERT([varchar](18),[sys].[fn_varbintohexstr]([col1]),0)),

    [col6] AS (upper(CONVERT([varchar](18),[sys].[fn_varbintohexstr]([col1]),0))))

    Then:

    insert into dbo.tbla (col1)

    select number

    from dbo.numbers

    where number between 1 and 100

    It worked just fine. No error message.

    I'm using:

    Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

    The error comes up when you try to persist the column. I also tried indexing the column, and that didn't work either. Will it work without being persisted?

    - 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 23, 2008 at 1:46 pm

    #806291

    It would -- as long as there is no performance hit while retrieving data from a table that has a computed column that is not persisted and not indexed.

  • April 23, 2008 at 2:17 pm

    #806303

    Leo Nosovsky (4/23/2008)

    It would -- as long as there is no performance hit while retrieving data from a table that has a computed column that is not persisted and not indexed.

    You'll have to test it for your use. It can work nicely. It can also be a problem. Depends on the use.

    - 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 5 posts - 16 through 19 (of 19 total)

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