insert query
-
Hi All, I have 2 table with Primary and foreign key constrains. where primary key is a identity column. if I insert any value in primary table..child table foreign key value should be inserted automatically.. Is there any way of using it with out triggers? how to do the same for delete? What is the traditional method...? Sorry if this is a very basic question!!!
Ramkumar ("When you build bridges you can keep crossing them. ") http://ramkumarishere.blogspot.com
-
Hi All, I have 2 table with Primary and foreign key constrains. where primary key is a identity column. if I insert any value in primary table..child table foreign key value should be inserted automatically.. Is there any way of using it with out triggers? how to do the same for delete? What is the traditional method...? Sorry if this is a very basic question!!!
Ramkumar ("When you build bridges you can keep crossing them. ") http://ramkumarishere.blogspot.com
You could create a stored procedure to create both the parent and child record, then standardize on that for creating parent records in your application. Delete would be handled through a foreign key constraint with "cascade delete" option. This means that when the parent record is deleted, then all of the child records would be deleted.
-
You could create a stored procedure to create both the parent and child record, then standardize on that for creating parent records in your application. Delete would be handled through a foreign key constraint with "cascade delete" option. This means that when the parent record is deleted, then all of the child records would be deleted.
Thanks for your time. Do u have any sample stored Procedure or Article related to Insert(with pk as identity column). goggled it..but could not find proper solution.
Ramkumar ("When you build bridges you can keep crossing them. ") http://ramkumarishere.blogspot.com
-
Thanks for your time. Do u have any sample stored Procedure or Article related to Insert(with pk as identity column). goggled it..but could not find proper solution.
Ramkumar ("When you build bridges you can keep crossing them. ") http://ramkumarishere.blogspot.com
Do you mean, like using SCOPE_IDENTITY()? It stores the last inserted primary key value in the current context
DECLARE @Last_PK_Table1 AS INT
INSERT INTO Table1(field1, field2) VALUES ('Field1', 'Field2')
SET @Last_PK_Table1 = SCOPE_IDENTITY()
INSERT INTO Table2(Table1_PK, OtherField, OtherField2) VALUES (@Last_PK_Table1, 'field1', 'field testing')thankx