trigger for insert - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

trigger for insert

Author

Topic

devs123
Starting Member

7 Posts

Posted - 2008-05-04 : 23:11:46

I have an EMPLOYEE table and DEPARTMENT table, i have to create one to many relationship where one employee can be in many departments, please guide me

create table employee (e_id INTEGER, e_fname varchar(30), e_lname varchar(30), d_id INTEGER references department(d_id))

create table department (d_id INTEGER, d_name varchar(30))

but here the problem would be while inserting into employee table since i have no primary key... do i need to use trigger to insert the right values.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-04 : 23:50:48

A trigger does not make sense to use here.

1. Create a primary key on e_id in employee and d_id in department
2. Remove d_id from employee table
3. Create a new table, perhaps named employee_department or similar. Columns would be e_id and d_id and both columns would comprise the composite primary key
4. Add a row into employee_department for each employee to department relationship

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

devs123
Starting Member

7 Posts

Posted - 2008-05-05 : 00:17:48

thank you very much tkizer for a fast reply, ok so you mean to say one to many relationship is not good in this case.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-05 : 01:20:05

That isn't what I said. I've shown how to implement the one to many relationship.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-05 : 01:21:52

quote:
Originally posted by devs123

thank you very much tkizer for a fast reply, ok so you mean to say one to many relationship is not good in this case.

What Tara suggested was to use a new table to reflect this one to many relationship.That table will have a composite primary key (e_id,d_id) comprising of primary keys of two involved tables (e_id for employee and d_id for department)

devs123
Starting Member

7 Posts

Posted - 2008-05-05 : 08:16:32

ok, thank you both. i want to understand very clearly the about table relationships can you please give a link or suggest any book which explains it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-05 : 08:36:58

http://www.dotnetjohn.com/articles.aspx?articleid=127

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-05-05 : 09:37:20

And just to be reeeally picky this isn't actually a one-to-many relationship but a many-to-many, since there are probably more than one employee and more than one departement. You'll be better off searching for how to resolve a many-to-many relationship, which is actually what Tara illustrated.

--
Lumbago

devs123
Starting Member

7 Posts

Posted - 2008-05-05 : 10:23:11

ok, i m getting confused now, when tara suggested to have another table with composite key i thought it must be many to many relationship, but when i asked about it tara said no its one to many.. i m a newbie in sql, so i searched in google and found that only in many to many relationship we have a junction table with composite key, please correct me if i m wrong ...
and know what i was asked this question in one of my interview, and that interviewer told me its one to many relationship,,, now i guess that person itself did not frame the question properly.. i did not hear back from them but

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-05-05 : 10:39:24

Your example is a classic many-to-many relationship and the common solution is to create a junction table with a composite key, exactly as you said. I do however think it's quite uncommon that an employee can belong to more than one department, and maybe this is where some of the confusion is...? If however the employee actually can belong to more than one department it is for sure many-to-many. The table setup could be something like this:


create table employee (
  e_id int IDENTITY(1, 1) PRIMARY KEY CLUSTERED, 
  e_fname varchar(30), 
  e_lname varchar(30)
)

create table department (
  d_id int IDENTITY(1, 1) PRIMARY KEY CLUSTERED, 
  d_name varchar(30)
)

create table department_employee (
  d_id int references department(d_id),
  e_id int references employee(e_id)
)

I guess the only place for a trigger would be on the employees table to add a new employee to a default department, but I'd code that in a procedure instead. I don't like triggers all that much...

EDIT: I read Taras response again and I belive she ment that her *solution* is a one-to-many relationship, which it is (or two actually).

--
Lumbago

devs123
Starting Member

7 Posts

Posted - 2008-05-05 : 10:46:56

thank you Lumbago, so which means there cannot be a one to many relationship right

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources