Solved

persisted, computed column

Posted on 2013-01-16
5
450 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

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, …
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

808 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