"Prabhat" <not_a_mail@hotmail.com> wrote in message news:eGbp8wy7DHA.3380@tk2msftngp13.phx.gbl...
> Hi ALL!
>
> I have 2 tables.
> 1) Dept = The Master Table (DeptID, DName, Loc) where DeptID is the Primary
> Key
> 2) Emp = The Detail Table(EmpID, DeptID, EName, Sal) Where EmpID is the
> Primary Key And DeptID is the Foreign Key to Dept(DeptID)
>
> I have a Stored Procedure to Delete the Record from DEPT Table.
> The Below is the Code Snipet of that:
>
> -----------------
> DELETE FROM DEPT WHERE DEPTID = @DEPTID
>
> SET @ERR_NUM = @@ERROR
> IF (@ERR_NUM <> 0) GOTO HANDLE_ERROR
> --------------
>
> But My problem is that If I Delete a Record from Dept Table which has a
> Detail record in EMP table then My Constraint Executes and Raise the ERROR.
> That I am Not able to Catch in my Stored procedure. So How Do I Handle Such
> Type of Error in Stored Procedure?
>
> Please Help.
>
> Thanks
> Prabhat
>
>
>

"Andrew John" <aj@DELETEmistrose.com> wrote in message
news:%23pj2YG07DHA.2432@TK2MSFTNGP10.phx.gbl...
> Prabhat,
>
> Either remove the foreign key and use triggers to enforce referential
integrity
> or better change the foreign key definition to perform delete cascades.
Something
> like:
>
> create table Dept
> (
> DeptID int primary key,
> DName varchar(30),
> Loc varchar(30)
> )
>
> create table Emp
> (
> EmpID int primary key,
> DeptID int foreign key references Dept(DeptID) ON DELETE CASCADE,
> EName varchar(30),
> Sal money
> )
>
> Or just tick the appropriate box in Enterprise Manager on the foreign key.
>
> Regards
> AJ
>
> "Prabhat" <not_a_mail@hotmail.com> wrote in message
news:eGbp8wy7DHA.3380@tk2msftngp13.phx.gbl...
> > Hi ALL!
> >
> > I have 2 tables.
> > 1) Dept = The Master Table (DeptID, DName, Loc) where DeptID is the
Primary
> > Key
> > 2) Emp = The Detail Table(EmpID, DeptID, EName, Sal) Where EmpID is the
> > Primary Key And DeptID is the Foreign Key to Dept(DeptID)
> >
> > I have a Stored Procedure to Delete the Record from DEPT Table.
> > The Below is the Code Snipet of that:
> >
> > -----------------
> > DELETE FROM DEPT WHERE DEPTID = @DEPTID
> >
> > SET @ERR_NUM = @@ERROR
> > IF (@ERR_NUM <> 0) GOTO HANDLE_ERROR
> > --------------
> >
> > But My problem is that If I Delete a Record from Dept Table which has a
> > Detail record in EMP table then My Constraint Executes and Raise the
ERROR.
> > That I am Not able to Catch in my Stored procedure. So How Do I Handle
Such
> > Type of Error in Stored Procedure?
> >
> > Please Help.
> >
> > Thanks
> > Prabhat
> >
> >
> >
>
>

"Prabhat" <not_a_mail@hotmail.com> wrote in message news:%23jvjXH%237DHA.2656@TK2MSFTNGP11.phx.gbl...
> Thanks John,
> But I don't want to Cascade the Detail record. I Just wanted to Restrict
> deletion of the Master Record if Detail Exists.
>
> Thanks
> Prabhat
>
> "Andrew John" <aj@DELETEmistrose.com> wrote in message
> news:%23pj2YG07DHA.2432@TK2MSFTNGP10.phx.gbl...
> > Prabhat,
> >
> > Either remove the foreign key and use triggers to enforce referential
> integrity
> > or better change the foreign key definition to perform delete cascades.
> Something
> > like:
> >
> > create table Dept
> > (
> > DeptID int primary key,
> > DName varchar(30),
> > Loc varchar(30)
> > )
> >
> > create table Emp
> > (
> > EmpID int primary key,
> > DeptID int foreign key references Dept(DeptID) ON DELETE CASCADE,
> > EName varchar(30),
> > Sal money
> > )
> >
> > Or just tick the appropriate box in Enterprise Manager on the foreign key.
> >
> > Regards
> > AJ
> >
> > "Prabhat" <not_a_mail@hotmail.com> wrote in message
> news:eGbp8wy7DHA.3380@tk2msftngp13.phx.gbl...
> > > Hi ALL!
> > >
> > > I have 2 tables.
> > > 1) Dept = The Master Table (DeptID, DName, Loc) where DeptID is the
> Primary
> > > Key
> > > 2) Emp = The Detail Table(EmpID, DeptID, EName, Sal) Where EmpID is the
> > > Primary Key And DeptID is the Foreign Key to Dept(DeptID)
> > >
> > > I have a Stored Procedure to Delete the Record from DEPT Table.
> > > The Below is the Code Snipet of that:
> > >
> > > -----------------
> > > DELETE FROM DEPT WHERE DEPTID = @DEPTID
> > >
> > > SET @ERR_NUM = @@ERROR
> > > IF (@ERR_NUM <> 0) GOTO HANDLE_ERROR
> > > --------------
> > >
> > > But My problem is that If I Delete a Record from Dept Table which has a
> > > Detail record in EMP table then My Constraint Executes and Raise the
> ERROR.
> > > That I am Not able to Catch in my Stored procedure. So How Do I Handle
> Such
> > > Type of Error in Stored Procedure?
> > >
> > > Please Help.
> > >
> > > Thanks
> > > Prabhat
> > >
> > >
> > >
> >
> >
>
>

"Andrew John" <aj@DELETEmistrose.com> wrote in message
news:%238Ga4LB8DHA.2720@TK2MSFTNGP09.phx.gbl...
> Prabhat,
>
> Then something like:
>
> Delete d
> from Dept d
> left outer join Emp e
> on e.DeptID = d.DeptID
> where e.DeptID is NULL
> and d.DeptID = @DeptID
>
> or use NOT EXISTS
>
> DeleteDept
> where .DeptID = @DeptID
> and not exists ( Select DeptID from Emp where DeptID = @DeptID )
>
> Regards
> AJ
>
> "Prabhat" <not_a_mail@hotmail.com> wrote in message
news:%23jvjXH%237DHA.2656@TK2MSFTNGP11.phx.gbl...
> > Thanks John,
> > But I don't want to Cascade the Detail record. I Just wanted to Restrict
> > deletion of the Master Record if Detail Exists.
> >
> > Thanks
> > Prabhat
> >
> > "Andrew John" <aj@DELETEmistrose.com> wrote in message
> > news:%23pj2YG07DHA.2432@TK2MSFTNGP10.phx.gbl...
> > > Prabhat,
> > >
> > > Either remove the foreign key and use triggers to enforce referential
> > integrity
> > > or better change the foreign key definition to perform delete
cascades.
> > Something
> > > like:
> > >
> > > create table Dept
> > > (
> > > DeptID int primary key,
> > > DName varchar(30),
> > > Loc varchar(30)
> > > )
> > >
> > > create table Emp
> > > (
> > > EmpID int primary key,
> > > DeptID int foreign key references Dept(DeptID) ON DELETE CASCADE,
> > > EName varchar(30),
> > > Sal money
> > > )
> > >
> > > Or just tick the appropriate box in Enterprise Manager on the foreign
key.
> > >
> > > Regards
> > > AJ
> > >
> > > "Prabhat" <not_a_mail@hotmail.com> wrote in message
> > news:eGbp8wy7DHA.3380@tk2msftngp13.phx.gbl...
> > > > Hi ALL!
> > > >
> > > > I have 2 tables.
> > > > 1) Dept = The Master Table (DeptID, DName, Loc) where DeptID is the
> > Primary
> > > > Key
> > > > 2) Emp = The Detail Table(EmpID, DeptID, EName, Sal) Where EmpID is
the
> > > > Primary Key And DeptID is the Foreign Key to Dept(DeptID)
> > > >
> > > > I have a Stored Procedure to Delete the Record from DEPT Table.
> > > > The Below is the Code Snipet of that:
> > > >
> > > > -----------------
> > > > DELETE FROM DEPT WHERE DEPTID = @DEPTID
> > > >
> > > > SET @ERR_NUM = @@ERROR
> > > > IF (@ERR_NUM <> 0) GOTO HANDLE_ERROR
> > > > --------------
> > > >
> > > > But My problem is that If I Delete a Record from Dept Table which
has a
> > > > Detail record in EMP table then My Constraint Executes and Raise the
> > ERROR.
> > > > That I am Not able to Catch in my Stored procedure. So How Do I
Handle
> > Such
> > > > Type of Error in Stored Procedure?
> > > >
> > > > Please Help.
> > > >
> > > > Thanks
> > > > Prabhat
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>