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
ChrisCJM
Second one seems to be good for you.
"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
>|||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.ph
x.gbl...
>I have table where I want one column (EquivNo) to default to the primary ke
y column (PartID) when
>the row is inserted. This value may be updated later, but at the point wher
e 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 defaul
t 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 t
he 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 wrote:
> I have table where I want one column (EquivNo) to default to the primary k
ey
> 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 sam
e.
> 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 defaul
t
> 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 t
he
> 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
Is EquivNo a self-referencing foreign key? (please post DDL, then we
won't have to guess). I'm of the opinion that IDENTITY isn't a good
choice to use as a foreign key in the same table. Your problem is just
one of one of the reasons why.
If EquivNo is not a key then I'm not sure why you'd want it to be the
same as the PartID. It's a bad idea to expose IDENTITY columns to
users. From the business perspective it shouldn't matter what value is
assigned to PartID and therefore it shouldn't matter whether it's the
same as EquivNo. I think that your real problem is that you need a more
convenient alternative method to generate an incrementing key for
EquivNo. See the following article for suggestions:
http://www.sqlmag.com/Articles/Arti...8165/48165.html
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||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_karaszi@.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...
>|||> Just keep thinking if the user inserts a new row (one transaction) , the
> trigger is fired and open another transaction, am I right?
No, the code in a trigger is in the same transaction as the statement that f
ired the trigger.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:OxFvq8WWGHA.3800@.TK2MSFTNGP03.phx.gbl
..
> Tibor
> 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_karaszi@.hotmail.nomail.com> wrote i
n
> message news:%23C2r4bWWGHA.5096@.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.|||"Uri Dimant" <urid@.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.|||> 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.
Yes, of course we can. My point (perhaps not explicit enough) was that the t
ransaction handling will
be transparent for those who does INSERTs into the table.
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:%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.
>|||Did you look at the trigger code I posted? What part of that code did you fi
nd complicated? I'm not
trying to defend my proposal, I just want to make sure you see the simplicit
y of the trigger
alternative.
Oh, and I fully agree with David's point regarding IDENTITY should be comple
mented with a natural
key and identity not be exposed to users, btw.
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:u$0KGgXWGHA.1348@.TK2MSFTNGP05.phx.
gbl...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:%23E1lfUWWGHA.3800@.T
K2MSFTNGP03.phx.gbl...
> I'm erring this way, if only because it's a simple and uncomplicated solut
ion.
>
