How can this be done?

[dakota97]

Stored Proc?...Trigger?...Other?

Hi all,

I have a project that Im working on that involves a SQL database with multiple tables that are all linked to a general Users table. The Users table includes an Identity field as the primary key. The other tables use this field as an identifier for the user that the record is associated with.

What method should I use so that if one of our apps inserts a record into a table, the "user information" is added to the Users table first, and then the Identity field is copied and combined with the rest of the information, and inserted into the original table? Im vaguely familiar with stored procedures and triggers, but I have never used either one of them as of yet. Any suggestions/examples/tutorials would be greatly appreciated.

Thanks in advance,

Chris

 

[Machaira]

IMO, a user shouldnt be adding information to a child table unless the relevant record is created in the parent table first.

 

[Joe Mamma]

IMO, a user shouldnt be adding information to a child table unless the relevant record is created in the parent table first.

Actually you can use an "instead of" trigger on the child table to populate the parent, then update the triggering table in same trigger. There is no problem with this and sometimes is the most doirect and efficient approach.

"instead of" triggers are not called recursively so you can update/insert/delete the triggering table without getting into an infinite loop.

For this situation I would use a uniqueidentifier for the key. . . i really dont like indentity fields.

in the trigger, generate a guid via newID. populate parent with guid, use guid in children.

on a tangent. . .

watch out for this novice schema design pattern mistake -

create table Parent(
id int identity(1,1) not null primary key,
uniqueSearchData nvarchar(255) not null unique)
go
create table child(
fk int references parent(id),
dateTimeOfTransaction datetime,
someSearchData nvarchar(255) not null,
primary key (fk, dateTimeOfTransaction)
)
go
create index ix_childlookup on child(someSearchData)

Over time, this can lead to some horrendous performance.

why? create the above schema and generate the resultant script.
see the clustering of the indexes?

by default, the sql engine clusters the primary keys, but you never search on the primary keys in a situtation like this. Also, the foreign keys are never retrieved in a sequential order. You want to cluster the searching fields.