cascade update / foreign key

[Gunnar Vøyenli]

Hi!
For the sake of simplicity, I have three tables, Employee, Department and
Work

Employee >---- Department
\ /
\ /
^ ^
Work

The Work table have two columns, empno and depno and consists that the
employee has worked on another department.

Here is my scripts:
create table employee (empno int not null primary key, depno int not null)
create table department (depno int not null primary key)
create table work (empno int not null, depno int not null)

alter table employee add constraint fk_employee_dep artment foreign key
(depno)
references department (depno)
on update cascade

alter table work add constraint fk_work_employe e foreign key (empno)
references employee (empno)
on update cascade

alter table work add constraint fk_work_departm ent foreign key (depno)
references department (depno)
on update cascade

My problem is the last command. SQL Server responds:
Server: Msg 1785, Level 16, State 1, Line 1
Introducing FOREIGN KEY constraint 'fk_work_depart ment' on table 'work' may
cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON
UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

But I want the depno in the work table to be updated when a department.depn o
changes a value.

Does anyone have a suggestion on how to overcome this problem?

Thanks in advance

Best regards,
Gunnar Vøyenli
EDB-konsulent as
NORWAY

[AMIT]

Hi,
SQL statement that you had provided is generating circular refrence in
updates
Workaround is to use triger instead of foreign key integrity.

Comment the one of foriegn key sql and use triger.

Note this trigger are not tested. but is only for your reference and
other work around for problem.

/*alter table work add constraint fk_work_employe e foreign key (empno)
references employee (empno)
on update cascade
go
*/
create trigger trgUpdateEmpNoI nDept
on employee
for update
as
set nocount on

declare @NewEmpno int , @OldEmpno int

select @NewEmpno = inserted.empno from inserted
select @OldEmpno = deleted.empno from deleted

update work set work.empno = @NewEmpno
where work.empno = @OldEmpno
if @@rowcount=0 or @@error<>0
begin
Rollback tran
end

set nocount off
go

create trigger trgCheckEmpExis ts
on Work
For Update
as

set nocount on

declare @NewEmpno int
select @NewEmpno = inserted.empno from inserted

If Not Exists (Select * from employee where empno = @NewEmpno )
Begin
RAISERROR ('Empno do not exists', 16, 1)
Rollback Tran
End
set nocount off

go
Thanks & Regards, Amit

"Gunnar Vøyenli" <gv@edbkonsulen t.no> wrote in message news:<3f******* *@news.broadpar k.no>...

Hi!
For the sake of simplicity, I have three tables, Employee, Department and
Work

Employee >---- Department
\ /
\ /
^ ^
Work

The Work table have two columns, empno and depno and consists that the
employee has worked on another department.

Here is my scripts:
create table employee (empno int not null primary key, depno int not null)
create table department (depno int not null primary key)
create table work (empno int not null, depno int not null)

alter table employee add constraint fk_employee_dep artment foreign key
(depno)
references department (depno)
on update cascade

alter table work add constraint fk_work_employe e foreign key (empno)
references employee (empno)
on update cascade

alter table work add constraint fk_work_departm ent foreign key (depno)
references department (depno)
on update cascade

My problem is the last command. SQL Server responds:
Server: Msg 1785, Level 16, State 1, Line 1
Introducing FOREIGN KEY constraint 'fk_work_depart ment' on table 'work' may
cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON
UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

But I want the depno in the work table to be updated when a department.depn o
changes a value.

Does anyone have a suggestion on how to overcome this problem?

Thanks in advance

Best regards,
Gunnar Vøyenli
EDB-konsulent as
NORWAY

[Gunnar Vøyenli]

Thank you very much!
-Gunnar

"AMIT" <am***@zenithin fotech.com> wrote in message
news:f3******** *************** ***@posting.goo gle.com...

Hi,
SQL statement that you had provided is generating circular refrence in
updates
Workaround is to use triger instead of foreign key integrity.

Comment the one of foriegn key sql and use triger.

Note this trigger are not tested. but is only for your reference and
other work around for problem.

/*alter table work add constraint fk_work_employe e foreign key (empno)
references employee (empno)
on update cascade
go
*/
create trigger trgUpdateEmpNoI nDept
on employee
for update
as
set nocount on

declare @NewEmpno int , @OldEmpno int

select @NewEmpno = inserted.empno from inserted
select @OldEmpno = deleted.empno from deleted

update work set work.empno = @NewEmpno
where work.empno = @OldEmpno
if @@rowcount=0 or @@error<>0
begin
Rollback tran
end

set nocount off
go

create trigger trgCheckEmpExis ts
on Work
For Update
as

set nocount on

declare @NewEmpno int
select @NewEmpno = inserted.empno from inserted

If Not Exists (Select * from employee where empno = @NewEmpno )
Begin
RAISERROR ('Empno do not exists', 16, 1)
Rollback Tran
End
set nocount off

go
Thanks & Regards, Amit

"Gunnar Vøyenli" <gv@edbkonsulen t.no> wrote in message

news:<3f******* *@news.broadpar k.no>...

Hi!
For the sake of simplicity, I have three tables, Employee, Department and Work

Employee >---- Department
\ /
\ /
^ ^
Work

The Work table have two columns, empno and depno and consists that the
employee has worked on another department.

Here is my scripts:
create table employee (empno int not null primary key, depno int not null) create table department (depno int not null primary key)
create table work (empno int not null, depno int not null)

alter table employee add constraint fk_employee_dep artment foreign key
(depno)
references department (depno)
on update cascade

alter table work add constraint fk_work_employe e foreign key (empno)
references employee (empno)
on update cascade

alter table work add constraint fk_work_departm ent foreign key (depno)
references department (depno)
on update cascade

My problem is the last command. SQL Server responds:
Server: Msg 1785, Level 16, State 1, Line 1
Introducing FOREIGN KEY constraint 'fk_work_depart ment' on table 'work' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

But I want the depno in the work table to be updated when a department.depn o changes a value.

Does anyone have a suggestion on how to overcome this problem?

Thanks in advance

Best regards,
Gunnar Vøyenli
EDB-konsulent as
NORWAY