Inserting same identity value into two linked tables

Hi,
I have two tables which has 2 linked fields, as David Portas posted in
'Field value determines whether there is extra info that should be held
about that record':
CREATE TABLE events (event_no INTEGER NOT NULL PRIMARY KEY, event_type
INTEGER NOT NULL CHECK (event_type BETWEEN 1 AND 10 /* however many
different types you have */), UNIQUE (event_no, event_type) /* ...
other columns for events */) ;
CREATE TABLE type1_events (event_no INTEGER NOT NULL PRIMARY KEY,
event_type INTEGER NOT NULL DEFAULT (1) CHECK (event_type=1), FOREIGN
KEY (event_no, event_type) REFERENCES events (event_no, event_type) ON
DELETE CASCADE /* ... other columns for event type 1 */) ;
I've set the event_no identity property in table 'events' to true. I have a
stored procedure to insert data to both tables at once (using 2 INSERT INTO
statements coming one after another).
The problem is that currently I'm using ADO to generate the new ID (By
selecting MAX of the event_no, adding 1 to the MAX value, and using the
result as the new event_no) which is sent as a parameter to the stored
procedure, which uses it in the 2 INSERT INTO statements.
This method is working fine by now, but could potentially cause problems.
How can generate a new value for that event_no identity column, then use it
in both the INSERT INTO statements?
I've read about @.@.IDENTITY, but I'm not sure about how to use it. I've also
read that there are better options, but these are not relevant since I'm
using SQL Server 7.
Kind Regards,
Amir.Amir wrote:
> Hi,
> I have two tables which has 2 linked fields, as David Portas posted in
> 'Field value determines whether there is extra info that should be held
> about that record':
> CREATE TABLE events (event_no INTEGER NOT NULL PRIMARY KEY, event_type
> INTEGER NOT NULL CHECK (event_type BETWEEN 1 AND 10 /* however many
> different types you have */), UNIQUE (event_no, event_type) /* ...
> other columns for events */) ;
> CREATE TABLE type1_events (event_no INTEGER NOT NULL PRIMARY KEY,
> event_type INTEGER NOT NULL DEFAULT (1) CHECK (event_type=1), FOREIGN
> KEY (event_no, event_type) REFERENCES events (event_no, event_type) ON
> DELETE CASCADE /* ... other columns for event type 1 */) ;
> I've set the event_no identity property in table 'events' to true. I have
a
> stored procedure to insert data to both tables at once (using 2 INSERT INT
O
> statements coming one after another).
> The problem is that currently I'm using ADO to generate the new ID (By
> selecting MAX of the event_no, adding 1 to the MAX value, and using the
> result as the new event_no) which is sent as a parameter to the stored
> procedure, which uses it in the 2 INSERT INTO statements.
> This method is working fine by now, but could potentially cause problems.
> How can generate a new value for that event_no identity column, then use i
t
> in both the INSERT INTO statements?
> I've read about @.@.IDENTITY, but I'm not sure about how to use it. I've als
o
> read that there are better options, but these are not relevant since I'm
> using SQL Server 7.
> Kind Regards,
> Amir.
Use @.@.IDENTITY to retrieve the last inserted IDENTITY value. Like this
for example:
CREATE TABLE events (event_no INTEGER IDENTITY NOT NULL PRIMARY KEY,
event_name VARCHAR(10) NOT NULL UNIQUE, event_type INTEGER NOT NULL
CHECK (event_type BETWEEN 1 AND 10), UNIQUE (event_no, event_type) ) ;
INSERT INTO events (event_name, event_type)
VALUES ('foo',1);
SELECT @.@.IDENTITY AS last_identity_value ;
David Portas
SQL Server MVP
--|||Thanks, David!
Kind Regards,
Amir.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1137159522.649050.309090@.g47g2000cwa.googlegroups.com...
> Amir wrote:
> Use @.@.IDENTITY to retrieve the last inserted IDENTITY value. Like this
> for example:
> CREATE TABLE events (event_no INTEGER IDENTITY NOT NULL PRIMARY KEY,
> event_name VARCHAR(10) NOT NULL UNIQUE, event_type INTEGER NOT NULL
> CHECK (event_type BETWEEN 1 AND 10), UNIQUE (event_no, event_type) ) ;
> INSERT INTO events (event_name, event_type)
> VALUES ('foo',1);
> SELECT @.@.IDENTITY AS last_identity_value ;
> --
> David Portas
> SQL Server MVP
> --
>