Solved

persisted, computed column

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Grid querry results 41 54
SQL Help joining two tables 7 33
SQL Maintenance Plan 3 16
SQL Server Error Log - logging period 1 18
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
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 video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

706 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

14 Experts available now in Live!

Get 1:1 Help Now