Solved

persisted, computed column

Posted on 2013-01-16
5
447 Views
Last Modified: 2013-01-16
Simple table with two ID's that come from two other tables.  IF SecondID is NULL, I want it to DEFAULT to the FirstID.  At first I thought I would do an AFTER INSERT trigger, but I really do try to avoid triggers, whenever I can.

So, I thought I'd try the computed column, persisted.


CREATE TABLE [dbo].[table](
      [uniqueID] [int] IDENTITY(1,1) NOT NULL,
      [FirstID] [int] NOT NULL,
      [SecondID] AS (CASE  
            WHEN [SecondID] IS NULL THEN [FirstID] ELSE [SecondID] END) PERSISTED,

My error:

Msg 402, Level 16, State 1, Line 7
The data types void type and void type are incompatible in the is operator.
Msg 1911, Level 16, State 1, Line 2
Column name 'SecondID' does not exist in the target table or view.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.

Please let me know, if you can.
0
Comment
Question by:dbaSQL
  • 3
  • 2
5 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 38782668
you cannot do this with a computed column, at least not in the column itself.
a computed column is computed, and cannot store data "directly". it will only evaluate to the expression.

so, you could have ThirdID column
CREATE TABLE [dbo].[table](
      [uniqueID] [int] IDENTITY(1,1) NOT NULL,
      [FirstID] [int] NOT NULL,
      [SecondID] [int] NULL,
      [ThirdID] AS (CASE  
            WHEN [SecondID] IS NULL THEN [FirstID] ELSE [SecondID] END) PERSISTED, 

Open in new window


note: the expression could be simplified to :
    [ThirdID] AS ISNULL( [SecondID] , [FirstID] ) PERSISTED, 

Open in new window

0
 
LVL 17

Author Comment

by:dbaSQL
ID: 38782693
yep, i was just reading the same on a sqlservercentral post.  i don't want the third value, though.  as much as i don't like them, I'm thinking the after insert trigger is the way to go.  would you agree, angeliii?


CREATE TRIGGER [dbo].[trigger] ON [dbo].[table] AFTER INSERT
AS

      BEGIN
            UPDATE table
            SET SecondID = FirstID
                                   FROM inserted
            WHERE table.uniqueID = inserted.uniqueID
            AND inserted.SecondID IS NULL
      END

GO
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38782708
yes
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 38782727
thank you, sir
0
 
LVL 17

Author Closing Comment

by:dbaSQL
ID: 38782729
very quick feedback, thank you.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now