Automatically insert into a 2nd table when a new data is added into another table

Question

question

tsaliki asked Aug 3, '12

Automatically insert into a 2nd table when a new data is added into another table

i have a table Companiesdata CREATE TABLE [dbo].[Companiesdata] ( [Company Name] nvarchar(255), [Industry] varchar(40), [ParentId] int NULL, ) the records are CompanyName Industry Parent ID XYZ technologies Media 1 apple Technologies pharma 1 EGG BANK Software 2 abc Technologies Networking 4 PQR Technnologies Marketing 5 i have other table create table dbo.companycategories ( categoryID int identity(1,1), Industry varchar(40) ) Now i want a procedure to automatically insert data into companycategories table if i inserted in companiesdata table. i.e my output should be If i insert into companiesdata values ('ijk technologies','finance',4) then the industry field should automatically get inserted into companycategories table select * from companycategories CategoryID Industry 1 Finance

insert table procedure

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2012-08-03T11:27:47Z

Pavel Pawlowski answered Aug 3, '12

You can use AFTER INSERT TRIGGER. See [CREATE TRIGGER (Transact-SQL)][1] on MSDN. [1]: http://msdn.microsoft.com/en-us/library/ms189799.aspx

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

tsaliki commented · Aug 06, 2012 at 05:08 AM

hey pavel thankyou .. will check it out ..

0 ·

Answer 2 · 2012-08-06T05:31:02Z

Scot Hauder answered Aug 6, '12

Instead of the trigger solution I would recommend you create a stored procedure that inserts CompaniesData and CompanyCategories at the same time. All inserts to CompaniesData should be through this stored procedure.

5

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

tsaliki commented · Aug 06, 2012 at 05:33 AM

scot i didnt get u.can u please write a store procedure for the above one ?

0 ·

Usman Butt commented · Aug 06, 2012 at 09:59 AM

Although we do the same for some databases, but Triggers tends to be the safest approach to handle cases like manual INSERT etc.

0 ·

sravan.434 commented · Aug 06, 2012 at 10:43 AM

Yes creating triggers tends to be fastest than stored procedures

0 ·

Usman Butt commented · Aug 06, 2012 at 11:07 AM

Sorry but I would disagree a bit. They can be fastest on some occasions but not always. Triggers are generally called evil because of the additional problems it can cause which can also include performance issues.

0 ·

Pavel Pawlowski commented · Aug 06, 2012 at 11:38 AM

As mentioned it depends how the db is used and accessed. Triggers are for sure the safest way, if users (developers) have dirrect access to the uderlying tables as in that scenario TRIGGERS will solve every possible way of inserts/updates. On the other way, if access to the underlying tables is limited and possible only using the views, functions and stored procedures then with proper procedures and security design, it will sever the same way as TRIGGERS and the insets/updates of other tables can be optimized based on the operations done by the stored procs.

0 ·

question