delete record from either both the tables or none (Tranasction)

Question

0.00/5 (No votes)

See more:

This is my Stored procedure, which On specifying the Department ID, it deletes all the records for that particular ID from the Departments table and it also deletes all the records from the Employees table for that particular department ID.

The condition is to delete record from either both the tables or none.

My code deletes record from one table alone. i dont get where the problem occurs.

Note: There is no foreign key between Employees table and department table.
but there is a foreign key between Employees table and Accounts table.

SQL

CREATE PROCEDURE usp_DeleteDepartment (@DepartmentId INT)
AS

BEGIN TRAN

DELETE FROM Departments_Details
WHERE DepartmentId = @DepartmentId

IF (@@ERROR <> 0) GOTO ERROR 

DELETE FROM Employees_Details
WHERE DepartmentId = @DepartmentId

IF (@@ERROR <> 0) GOTO ERROR 

COMMIT TRAN

ERROR:
IF (@@ERROR <> 0) BEGIN
PRINT 'Foreign key constraint error occurred!'
    ROLLBACK TRAN
END

Posted 8-Oct-12 21:17pm

bolshie6

Updated 8-Oct-12 21:19pm

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Bhushan Shah1988 · Accepted Answer · 2012-10-08T21:27:00

Solution 1

you can use try catch instead of @@ERROR.
see below code :

SQL

BEGIN   
   SET NOCOUNT ON;
   BEGIN TRY
      BEGIN TRAN
         /*
            Insert your Code Here.
         */
      COMMIT TRAN
   END TRY
   BEGIN CATCH         
      ROLLBACK TRANSACTION      
   END CATCH
END

Posted 8-Oct-12 21:27pm

Bhushan Shah1988

Comments

bolshie6 9-Oct-12 4:16am

Thank you, I solved it myself. I've also done it in the same way.

bolshie6 · Accepted Answer · 2012-10-08T22:15:00

SQL

ALTER PROCEDURE usp_DeleteDepartment (@DepartmentId INT)
AS

BEGIN TRANSACTION Trans
BEGIN TRY

DELETE FROM Departments_Details
WHERE DepartmentId = @DepartmentId

DELETE FROM Employees_Details
WHERE DepartmentId = @DepartmentId

COMMIT TRANSACTION Trans
END TRY

BEGIN CATCH
    ROLLBACK TRANSACTION Trans
END CATCH