view and Trigger

Question

Post reply

view and Trigger

dr_csharp

SSCommitted

Points: 1765
More actions
December 17, 2007 at 11:16 pm

#180003

hello my friends
i have a SQL Database with 2 tables : Company and Product
Fields in Company :
- CompanyID ( PK )
- Name
- RegDate
Fields in Product :
- CompanyID ( FK )
- ProductName
its clearly definite that one company can have more than one product so there's a one to many relation.
how can i fill both table ? i mean i fill Company and company's products in one time !!?
i hear that i should define a view with joining this two tables and define a trigger on them to fill Company table
and then select CompanyID and Insert products on product table .
i'll appreciate if you help me on this !

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

Hi

From where will u get the data. R u porting from some other SQL db, or a text file or what?

Generall speaking insert the companies first and then insert one comapnies products. A procedure should be able to do this and so would SSIS.

Triggers is a option that i rarely use but i may be wrong .

"Keep Trying"

dr_csharp SSCommitted Points: 1765 More actions · Answer 2

Chirag (12/18/2007)
Hi
From where will u get the data. R u porting from some other SQL db, or a text file or what?
Generall speaking insert the companies first and then insert one comapnies products. A procedure should be able to do this and so would SSIS.
Triggers is a option that i rarely use but i may be wrong .

i get data from user in my Application . let say the user enter some specifications such : CompanyNam,Company Telephones,.. in my Application . when press Reg in my application i should insert data to SQL database .

i know that i can do these with procedure with 2 parameters such CompanyName,telephone and do like below :

declare @Id int

INSERT INTO A (CompanyName)VALUES('Iran');

SELECT @Id=@@IDENTITY

INSERT INTO B (CompanyID,Tel)VALUES(@Id,'0912')

but im confused about these that why i should use VIEW and TRIGGER or is there any advantage to use these !??

ianT SSCrazy Points: 2956 More actions · Answer 3

I'd put the inserts into a transaction.

Cannot see any reason to use a view or triggers here...

ChiragNS One Orange Chip Points: 26137 More actions · Answer 4

Hi

I dont think u need views or triggers. Procedure should do the trick. Procedures give much better control over things than triggers.

"Keep Trying"

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

dr_csharp (12/18/2007)

declare @Id int
INSERT INTO A (CompanyName)VALUES('Iran');
SELECT @Id=@@IDENTITY
INSERT INTO B (CompanyID,Tel)VALUES(@Id,'0912')

Make sure you understand the difference between @@IDENTITY and SCOPE_IDENTITY before rolling this code out into a live environment.

John

dr_csharp SSCommitted Points: 1765 More actions · Answer 6

Make sure you understand the difference between @@IDENTITY and SCOPE_IDENTITY before rolling this code out into a live environment.
John

is there any difference between @@IDENTITY and SCOPE_IDENTITY except working scope ?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

Not from my reading of it, no. But study the @@IDENTITY topic in Books Online carefully, and maybe carry out a few tests as well.

John