Error in Checking Conditions

[S2009]

Hi

all,


I am creating a project for Library System(WINDOWS APPLICATION ).

Front End: Visual Studio - C# Programming Language
Back End : SQL SERVER 2005.

I have a table in which I maintain Inventory and Books related Details. Now at the time of issuing book I want to check whether the Inventory of the book is greater than 1.

If the Inventory is Greater than 1, it should add NumberOfBooks Issued by 1 and Subtract Inventory by 1. To perform this task I have created the following Trigger.

TRIGGER CREATION:

    create TRIGGER trg_InsertInBookIssuedDetails ON BOOKISSUEDDETAILS
    FOR INSERT
    AS
    begin
    declare
    @UserName nvarchar(50),
    @BookID int,     
    @Inventory int
    begin try
    begin tran
    if exists(SELECT * FROM LIBRARYBOOKDETAILS WHERE BookID = @BookID and Inventory >=1)    
    begin   
    SELECT @UserName = IssuedTo,@BookID = BookID FROM BOOKISSUEDDETAILS
    UPDATE NewUserRegister SET NumberOfBooksIssued = NumberOfBooksIssued+1 WHERE UserName = @UserName
    UPDATE LIBRARYBOOKDETAILS SET Inventory = Inventory - 1 WHERE BookID= @BookID
    commit tran
    end
    else
    begin
    raiserror('Books Unavailable',16,-1)
    end
    end try   
    begin catch
    raiserror('Books UnAvailable Or Cannot Issue More Than 3 Books',16,-1)
    rollback tran
    end catch
    end

When I execute the above statements I am getting commands executed Successfully.

And the Coding for Issue Button is as follows:

        SqlConnection objSqlConnection = new SqlConnection();
        string connectionStringSettings = "Data Source = .; Initial Catalog = LibrarySystemManagement; Integrated Security = SSPI";
        private void btnIssue_Click(object sender, EventArgs e)
        {
            if (txtIssuedTo.Text.Trim() == "")
            {
                MessageBox.Show("Issued To Field Cannot be Blank", "Error Information", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            else
            {
                try
                {
                    objSqlConnection.ConnectionString = connectionStringSettings;
                    objSqlConnection.Open();
                    SqlDataAdapter objSqlDataAdapter = new SqlDataAdapter("select * from BOOKISSUEDDETAILS", objSqlConnection);
                    SqlCommandBuilder objSqlCommandBuilder = new SqlCommandBuilder(objSqlDataAdapter);
                    DataSet objDataSet = new DataSet("BOOKISSUEDDETAILS");
                    objSqlDataAdapter.Fill(objDataSet, "BOOKISSUEDDETAILS");
                    DataRow objDataRow = objDataSet.Tables["BOOKISSUEDDETAILS"].NewRow();
                    objDataRow[1] = txtBookID.Text;
                    objDataRow[2] = System.DateTime.Now;
                    objDataRow[3] = txtIssuedTo.Text;
                    objDataRow[4] = System.DBNull.Value;
                    objDataSet.Tables["BOOKISSUEDDETAILS"].Rows.Add(objDataRow);
                    objSqlDataAdapter.Update(objDataSet, "BOOKISSUEDDETAILS");
                    objSqlConnection.Close();
                    MessageBox.Show("Books Issued Successfully", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                catch (SqlException e1)
                {
                    MessageBox.Show(e1.Source + "\n" + e1.Message);
                    objSqlConnection.Close();
                }
                catch (Exception e2)
                {
                    MessageBox.Show(e2.Message + "\n" + e2.Source);
                    objSqlConnection.Close();
                }
            }
        }

Now when I execute the Windows Application I m getting the Error Message irrespective of the inventory and NumberOfBooksIssued:

.Net SqlClient DataProvider
Books UnAvailable or cannot Issue morethan 3 books.
The transaction has ended in Trigger. Batch has been aborted.

For example.

Even if the number of books issued is lessthan 3 and inventory is Greater Than 1 I am getting the Error Message as mentioned in the raiserror statement.

Can anyone tell me what changes I should make to perform the desired task?

Please help me out!!

Thanks in advance!!

[vuyiswam]

First of All

This is not what i would like to see in Forums. People Posting code without Formatting it. Before you post you must put the code in the code tags, this is damaging our eyes when we are trying to read this.

Format and we will help

[Alsvha]

It is pretty difficult to help when you do not show us the actual error message, but only the message you write yourself.

What actual error does it show?

[ComITSolutions]

in your trigger where are you assiging value to value to variables @UserName nvarchar(50), @BookID int and @Inventory int after declaration??

declare
    @UserName nvarchar(50),
    @BookID int,     
    @Inventory int
    begin try
    begin tran
    if exists(SELECT * FROM LIBRARYBOOKDETAILS WHERE BookID = @BookID and Inventory >=1)

That is before using @bookid variable. Since No value is assigned it will contain null and execution control will never enter into true part your code.

Do you have any idea about deleted and inserted are logical (conceptual) tables which are used in triggers?

Hope this hint will help you

I still do not preffer using triggers, because they are resource intensive!!