"CJM" <cjmnews04@newsgroup.nospam> wrote in message news:%23UkDUPWWGHA.3448@TK2MSFTNGP03.phx.gbl...
>I have table where I want one column (EquivNo) to default to the primary key column (PartID) when
>the row is inserted. This value may be updated later, but at the point where the row is created, I
>want the values to be the same. The PartID column is an Identity column so I don't know the value
>in advance.
>
> As far as I understand it, you can't have a calculated value as the default value for a column;
> can somebody confirm this?
>
> So I have thought of two alternatives:
> 1) Create a trigger that takes the identity value for PartID and updates the EquivNo column
>
> [How do I know what the inserted PartID is within the trigger?]
>
> 2) The stored procedure inserts the row on the first pass, and uses Scope_Identy() to update the
> EquivNo in an update statement.
>
> I was just wondering about the relative merits of these two solutions. And are there any better
> alternatives?
>
> Thanks in advance
>
> Chris
>

"Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in
message news:%23C2r4bWWGHA.5096@TK2MSFTNGP03.phx.gbl...
> An advantage of using 1 is that the modifications are performed in the
> same transaction. The trigger code is very straight forward:
>
> USE tempdb
>
> drop table t
> GO
> create table t(c1 int identity primary key, c2 int NULL)
> GO
> CREATE TRIGGER tr ON T FOR INSERT
> AS
> UPDATE t SET c2 = c1
> WHERE EXISTS
> (
> SELECT *
> FROM inserted AS i
> WHERE i.c1 = t.c1
> )
> GO
>
> insert into t(c2) VALUES(NULL)
> insert into t(c2) VALUES(NULL)
> insert into t(c2) VALUES(NULL)
> insert into t(c2) VALUES(NULL)
>
> SELECT * FROM t
>
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
>
> "CJM" <cjmnews04@newsgroup.nospam> wrote in message
> news:%23UkDUPWWGHA.3448@TK2MSFTNGP03.phx.gbl...
>>I have table where I want one column (EquivNo) to default to the primary
>>key column (PartID) when the row is inserted. This value may be updated
>>later, but at the point where the row is created, I want the values to be
>>the same. The PartID column is an Identity column so I don't know the
>>value in advance.
>>
>> As far as I understand it, you can't have a calculated value as the
>> default value for a column; can somebody confirm this?
>>
>> So I have thought of two alternatives:
>> 1) Create a trigger that takes the identity value for PartID and updates
>> the EquivNo column
>>
>> [How do I know what the inserted PartID is within the trigger?]
>>
>> 2) The stored procedure inserts the row on the first pass, and uses
>> Scope_Identy() to update the EquivNo in an update statement.
>>
>> I was just wondering about the relative merits of these two solutions.
>> And are there any better alternatives?
>>
>> Thanks in advance
>>
>> Chris
>>
>

"Uri Dimant" <u***@iscar.co.il> wrote in message news:OxFvq8WWGHA.3800@TK2MSFTNGP03.phx.gbl...
> Tibor
>> An advantage of using 1 is that the modifications are performed in the
>> same transaction. The trigger code is very straight forward:
>
> Just keep thinking if the user inserts a new row (one transaction) , the
> trigger is fired and open another transaction, am I right?
> If the trigger fails that an identity peroperty is already in the table , so
> actually in my opinion that using
> a stored procedure  will be more useful in terms of performans as well as
> more secure.
>
>
> CREATE PROC myproc
> AS
> DECLARE @idnt INT
> BEGIN TRAN
>
> INSERT INTO T1 VALUES (.....)
> SELECT @idnt =SCOPE_IDENTITY()
> INSERT INTO T2 SELECT @idnt
> --Error handler here
> COMMIT
>
>
> "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in
> message news:%23C2r4bWWGHA.5096@TK2MSFTNGP03.phx.gbl...
>> An advantage of using 1 is that the modifications are performed in the
>> same transaction. The trigger code is very straight forward:
>>
>> USE tempdb
>>
>> drop table t
>> GO
>> create table t(c1 int identity primary key, c2 int NULL)
>> GO
>> CREATE TRIGGER tr ON T FOR INSERT
>> AS
>> UPDATE t SET c2 = c1
>> WHERE EXISTS
>> (
>> SELECT *
>> FROM inserted AS i
>> WHERE i.c1 = t.c1
>> )
>> GO
>>
>> insert into t(c2) VALUES(NULL)
>> insert into t(c2) VALUES(NULL)
>> insert into t(c2) VALUES(NULL)
>> insert into t(c2) VALUES(NULL)
>>
>> SELECT * FROM t
>>
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>>
>> "CJM" <cjmnews04@newsgroup.nospam> wrote in message
>> news:%23UkDUPWWGHA.3448@TK2MSFTNGP03.phx.gbl...
>>>I have table where I want one column (EquivNo) to default to the primary
>>>key column (PartID) when the row is inserted. This value may be updated
>>>later, but at the point where the row is created, I want the values to be
>>>the same. The PartID column is an Identity column so I don't know the
>>>value in advance.
>>>
>>> As far as I understand it, you can't have a calculated value as the
>>> default value for a column; can somebody confirm this?
>>>
>>> So I have thought of two alternatives:
>>> 1) Create a trigger that takes the identity value for PartID and updates
>>> the EquivNo column
>>>
>>> [How do I know what the inserted PartID is within the trigger?]
>>>
>>> 2) The stored procedure inserts the row on the first pass, and uses
>>> Scope_Identy() to update the EquivNo in an update statement.
>>>
>>> I was just wondering about the relative merits of these two solutions.
>>> And are there any better alternatives?
>>>
>>> Thanks in advance
>>>
>>> Chris
>>>
>>
>
>

"CJM" <cjmnews04@newsgroup.nospam> wrote in message news:%23lbLUbXWGHA.924@TK2MSFTNGP03.phx.gbl...
> Surely the transaction issue is a red herring? That is, we can explicitly define what is and isn't
> included in the transaction (Begin/Commit/Rollback) anyway.
>

> I have table where I want one column (EquivNo) to default to the primary key
> column (PartID) when the row is inserted. This value may be updated later,
> but at the point where the row is created, I want the values to be the same.
> The PartID column is an Identity column so I don't know the value in
> advance.
>
> As far as I understand it, you can't have a calculated value as the default
> value for a column; can somebody confirm this?
>
> So I have thought of two alternatives:
> 1) Create a trigger that takes the identity value for PartID and updates the
> EquivNo column
>
> [How do I know what the inserted PartID is within the trigger?]
>
> 2) The stored procedure inserts the row on the first pass, and uses
> Scope_Identy() to update the EquivNo in an update statement.
>
> I was just wondering about the relative merits of these two solutions. And
> are there any better alternatives?
>
> Thanks in advance
>
> Chris

"CJM" <cjmnews04@newsgroup.nospam> wrote in message
news:%23UkDUPWWGHA.3448@TK2MSFTNGP03.phx.gbl...
>I have table where I want one column (EquivNo) to default to the primary
>key column (PartID) when the row is inserted. This value may be updated
>later, but at the point where the row is created, I want the values to be
>the same. The PartID column is an Identity column so I don't know the value
>in advance.
>
> As far as I understand it, you can't have a calculated value as the
> default value for a column; can somebody confirm this?
>
> So I have thought of two alternatives:
> 1) Create a trigger that takes the identity value for PartID and updates
> the EquivNo column
>
> [How do I know what the inserted PartID is within the trigger?]
>
> 2) The stored procedure inserts the row on the first pass, and uses
> Scope_Identy() to update the EquivNo in an update statement.
>
> I was just wondering about the relative merits of these two solutions. And
> are there any better alternatives?
>
> Thanks in advance
>
> Chris
>

"CJM" <cjmnews04@newsgroup.nospam> wrote in message news:u$0KGgXWGHA.1348@TK2MSFTNGP05.phx.gbl...
>
> "Uri Dimant" <u***@iscar.co.il> wrote in message news:%23E1lfUWWGHA.3800@TK2MSFTNGP03.phx.gbl...
>> CJM
>> Second one seems to be good for you.
>>
>>
>
> I'm erring this way, if only because it's a simple and uncomplicated solution.
>

"CJM" <cjmnews04@newsgroup.nospam> wrote in message news:ugPszwYWGHA.5096@TK2MSFTNGP03.phx.gbl...
>
> "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in message
> news:eOoNfjXWGHA.3440@TK2MSFTNGP02.phx.gbl...
>> Did you look at the trigger code I posted? What part of that code did you find complicated? I'm
>> not trying to defend my proposal, I just want to make sure you see the simplicity of the trigger
>> alternative.
>>
>
> lol. Yes I did... It's just that I haven't used triggers much in the past. So I'm debating whether
> to use this as trigger-practice, or to stick with what I know...
>
> Snippet:
>
> Alter TRIGGER SetEquivNo
>   ON PartMaster
>   For INSERT
> AS
> BEGIN
>
> SET NOCOUNT ON;
>
> UPDATE PartMaster P
> SET P.EquivNo = i.PartID
> WHERE EXISTS
>   (
>   SELECT I.PartID
>   FROM Inserted AS I
>   WHERE I.PartID = P.PartID
>   )
> END
>
>
> At the moment, it reckons there is a problem after the 'Update PartMaster P' line - Incorrect
> Syntax! What have I missed?
>
>> Oh, and I fully agree with David's point regarding IDENTITY should be complemented with a natural
>> key and identity not be exposed to users, btw.
>>
>
> This field is not exposed to the user. But even if it was, there is no natural key to choose...
>
> This column is used to link several PartNos together to show that they are effectively
> equivalent - Parts with the same EquivNo are 'equivalent'. The user doesnt see it or need to know
> about it.
>
> I *could* have created another table to record equivalences, such that EquivNo *would* have been a
> foreign key to the 'Equivalences' table, but given the limited use of this column, there would
> have been no other benefit to this method.
>
> Cheers
>
> Chris
>