October 12, 2008 at 10:45 pm
Hi All,
I have problem with triggers. I have following two tables.
CREATE TABLE department (
SAPID int identity(1,1) primary key,
UserName varchar (50) NULL ,
Password varchar (50) NULL
)
CREATE TABLE employee (
SAPID int NOT NULL ,
FName varchar (50) NOT NULL ,
MName varchar (50) NULL ,
LName varchar (50) NOT NULL ,
Email varchar (50) NOT NULL , CONSTRAINT SAPID_FK FOREIGN KEY(SAPID)REFERENCES department(SAPID) )
If I delete any row from department table it gives error because we can not delete rows from master table when there are related records in child table.
I can not use on delete cascade because I have few more tables and relations. When I tries to use on delete cascade it gives cycle or multiple path error. If I try to do it using after delete trigger, that trigger is not getting fired because database is not allowing records to be delete from master i.e. department table because of foreign key constraint.
Please tell me how I can delete rows from master and child tables when I tries to delete any row from master table. How we can do it using INSTEAD OF trigger.
=======================================
[font="Tahoma"]"Dont let someone be priority in your life if you are simply option for someone."[/font]
October 13, 2008 at 12:48 am
Try changing the foreign key to ON DELETE SET NULL. You should then be able to delete via the trigger. The reason you can't now is because the trigger fires after the foreign keys have been checked.
It's odd that you're getting a multiple path error. A well-designed database shouldn't have that problem. Any chance you can post the rest of the references so we can take a look and maybe offer suggestions to get around this problem?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 13, 2008 at 1:10 am
Many thanks for reply.
For your information I'm working on SQL Server 2000 and I think there is no ON DELETE SET NULL. Could you please help me in this regard?
=======================================
[font="Tahoma"]"Dont let someone be priority in your life if you are simply option for someone."[/font]
October 13, 2008 at 1:28 am
Please post SQL 2000 related questions in the SQL 2000 forums in the future. If you post in the 2005 forums, people are going to assume you are using 2005 and give you 2005-specific solutions.
Since you're on 2000, you'll probably have to go with an instead of trigger so that the deletes of the child table are done before the deletes from the parent. Are the deletes always been done from a stored procedure? If so, put the delete of child rows in there and forget about the trigger.
Can you post the rest of the tables so we can see if there's a way to resolve the multiple path error?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 14, 2008 at 7:25 am
Instead of trigger would be my choice as well.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply