Inserting Values Into Primary Key and Foreign Key Tables

I have two tables that I would like to insert values into at the same time
and would like help with the SQL statement to do so. One table (Member_Info)
has a PK and the other (Image_Info) a FK. The relationship the two tables
share is through the (E_Mail) column. Example structure:
Member_Info table columns:
First_Name
Last_Name
Birthday
E_Mail (PK)
Image_Info table columns:
E_Mail (FK)
Use
Name
Please Help! Thanks!Use INSERT TRIGGER in Member_Info table
"Willie Davis via webservertalk.com" wrote:

> I have two tables that I would like to insert values into at the same time
> and would like help with the SQL statement to do so. One table (Member_Inf
o)
> has a PK and the other (Image_Info) a FK. The relationship the two tables
> share is through the (E_Mail) column. Example structure:
> Member_Info table columns:
> First_Name
> Last_Name
> Birthday
> E_Mail (PK)
> Image_Info table columns:
> E_Mail (FK)
> Use
> Name
>
> Please Help! Thanks!
>|||Due to not determining what table will be filled by your code and what table
has to be filled "automagicaly", the following just depends on my guesswork.
CREATE TRIGGER TrgIns Member_Info
FOR INSERT
AS
BEGIN
INSERT INTO (EMail, Name)
SELECT EMail, COALESCE(FirstName,'') + ' ' + COALESCE(LastName,'') FROM
Inserted
END
I dont know wheter you Name column will store the First and the Lastname or
just one of it, but you should consider an Update Trigger to update this
information if its changed in the Primary Key Table. (Guess you sure wont
need it, if this just stores the name of the Member, even this is already
stored in the PK Table)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Willie Davis via webservertalk.com" <forum@.nospam.webservertalk.com> schrieb im
Newsbeitrag news:4183b2e7baef434899249fa71b414bf6@.SQ
webservertalk.com...
>I have two tables that I would like to insert values into at the same time
> and would like help with the SQL statement to do so. One table
> (Member_Info)
> has a PK and the other (Image_Info) a FK. The relationship the two tables
> share is through the (E_Mail) column. Example structure:
> Member_Info table columns:
> First_Name
> Last_Name
> Birthday
> E_Mail (PK)
> Image_Info table columns:
> E_Mail (FK)
> Use
> Name
>
> Please Help! Thanks!|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
same time M<<
SQL works with one table at a time. Put you code into a stored
procedure, do two inserts and depend on DRI actions to maintain
integrity.

Inserting values into multiple tables

Hi. I have a primary key field in one table that is a foreign key field in another table. How do I write an INSERT statement that puts the value in both tables? Can this even be done?
On Thu, 20 May 2004 20:56:02 -0700, twright wrote:

>Hi. I have a primary key field in one table that is a foreign key field in another table. How do I write an INSERT statement that puts the value in both tables? Can this even be done?
Hi twright,
Not in one statement. You have to use two INSERT statements. If you want
to be sure that either both or none are executed, whatevert happens,
enclose them in a transaction:
BEGIN TRANSACTION
INSERT FirstTable (KeyColumn)
VALUES (17)
INSERT SecondTable (OtherKeyColumn, ForeignKeyColumn)
VALUES (231, 17)
COMMIT TRANSACTION
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Inserting NULL when foreign key gets deleted.

I have a couple of tables in my database (SQL server 2000)
1. Student -- Columns are as
"StudentID" -- INT
"StudentName" -- NVARCHAR
"DepartmentID" -- INT
2. Department -- Columns are as
"DepartmentID" -- INT
"DepartmentName" -- NVARCHAR
So in the table "Student", the field "DepartmentID" is a foreign key. Now
what I need to do is that when I delete a row in the "Department" table, the
n
the rows in "Student" table where the to be deleted "Department ID" occurs
must *not* get deleted. Instead NULL should be inserted there.
Can this be achieved through database schema? Or is it something that needs
to be done through triggers?
A.
Locate me in the blogosphere: http://spaces.msn.com/aayushpuriIn 2005, you can define SET DEFAULT and SET NULL for a foreign key reference
(and of course CASCADE
and restrict). In 200, you need to write code for this(trigger, for example)
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aayush Puri" <aayushpuri @. h o t m a i l . c o m> wrote in message
news:0F995DF3-EFED-475B-9F22-E96302A58F0B@.microsoft.com...
>I have a couple of tables in my database (SQL server 2000)
> 1. Student -- Columns are as
> "StudentID" -- INT
> "StudentName" -- NVARCHAR
> "DepartmentID" -- INT
> 2. Department -- Columns are as
> "DepartmentID" -- INT
> "DepartmentName" -- NVARCHAR
> So in the table "Student", the field "DepartmentID" is a foreign key. Now
> what I need to do is that when I delete a row in the "Department" table, t
hen
> the rows in "Student" table where the to be deleted "Department ID" occurs
> must *not* get deleted. Instead NULL should be inserted there.
> Can this be achieved through database schema? Or is it something that need
s
> to be done through triggers?
> A.
> --
> Locate me in the blogosphere: http://spaces.msn.com/aayushpuri|||Thanks a lot Tibor for the quick reply. In the meanwhile I also searched a
bit more for the solution to my problem. I got to this article on MSDN -
http://msdn.microsoft.com/library/d...fintegrity.asp.
It explains as to how to implement the "ON DELETE INSERT NULL" functionality
as TRIGGER in SQL server 2000.
A.
Locate me in the blogosphere: http://spaces.msn.com/aayushpuri
"Tibor Karaszi" wrote:

> In 2005, you can define SET DEFAULT and SET NULL for a foreign key referen
ce (and of course CASCADE
> and restrict). In 200, you need to write code for this(trigger, for exampl
e).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Aayush Puri" <aayushpuri @. h o t m a i l . c o m> wrote in message
> news:0F995DF3-EFED-475B-9F22-E96302A58F0B@.microsoft.com...
>
>

Inserting NULL when foreign key gets deleted.

I have a couple of tables in my database (SQL server 2000)
1. Student -- Columns are as
"StudentID" -- INT
"StudentName" -- NVARCHAR
"DepartmentID" -- INT
2. Department -- Columns are as
"DepartmentID" -- INT
"DepartmentName" -- NVARCHAR
So in the table "Student", the field "DepartmentID" is a foreign key. Now
what I need to do is that when I delete a row in the "Department" table, then
the rows in "Student" table where the to be deleted "Department ID" occurs
must *not* get deleted. Instead NULL should be inserted there.
Can this be achieved through database schema? Or is it something that needs
to be done through triggers?
A.
--
Locate me in the blogosphere: http://spaces.msn.com/aayushpuriIn 2005, you can define SET DEFAULT and SET NULL for a foreign key reference (and of course CASCADE
and restrict). In 200, you need to write code for this(trigger, for example).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aayush Puri" <aayushpuri @. h o t m a i l . c o m> wrote in message
news:0F995DF3-EFED-475B-9F22-E96302A58F0B@.microsoft.com...
>I have a couple of tables in my database (SQL server 2000)
> 1. Student -- Columns are as
> "StudentID" -- INT
> "StudentName" -- NVARCHAR
> "DepartmentID" -- INT
> 2. Department -- Columns are as
> "DepartmentID" -- INT
> "DepartmentName" -- NVARCHAR
> So in the table "Student", the field "DepartmentID" is a foreign key. Now
> what I need to do is that when I delete a row in the "Department" table, then
> the rows in "Student" table where the to be deleted "Department ID" occurs
> must *not* get deleted. Instead NULL should be inserted there.
> Can this be achieved through database schema? Or is it something that needs
> to be done through triggers?
> A.
> --
> Locate me in the blogosphere: http://spaces.msn.com/aayushpuri|||Thanks a lot Tibor for the quick reply. In the meanwhile I also searched a
bit more for the solution to my problem. I got to this article on MSDN -
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_refintegrity.asp.
It explains as to how to implement the "ON DELETE INSERT NULL" functionality
as TRIGGER in SQL server 2000.
A.
--
Locate me in the blogosphere: http://spaces.msn.com/aayushpuri
"Tibor Karaszi" wrote:
> In 2005, you can define SET DEFAULT and SET NULL for a foreign key reference (and of course CASCADE
> and restrict). In 200, you need to write code for this(trigger, for example).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Aayush Puri" <aayushpuri @. h o t m a i l . c o m> wrote in message
> news:0F995DF3-EFED-475B-9F22-E96302A58F0B@.microsoft.com...
> >I have a couple of tables in my database (SQL server 2000)
> > 1. Student -- Columns are as
> > "StudentID" -- INT
> > "StudentName" -- NVARCHAR
> > "DepartmentID" -- INT
> > 2. Department -- Columns are as
> > "DepartmentID" -- INT
> > "DepartmentName" -- NVARCHAR
> >
> > So in the table "Student", the field "DepartmentID" is a foreign key. Now
> > what I need to do is that when I delete a row in the "Department" table, then
> > the rows in "Student" table where the to be deleted "Department ID" occurs
> > must *not* get deleted. Instead NULL should be inserted there.
> > Can this be achieved through database schema? Or is it something that needs
> > to be done through triggers?
> >
> > A.
> >
> > --
> > Locate me in the blogosphere: http://spaces.msn.com/aayushpuri
>
>