Error in Trigger Using TRY CATCH AND IF ELSE


Hi all,

I am creating a trigger which should be triggered on insert to BOOKISSUEDETAILS table. I want to alter the table LIBRARYBOOKDETAILS table only if the Inventory column value is greater than 1.

Initially I have created the trigger in the following way. But now I want to alter the Trigger to include the condition(Inventory > 1) and use the Try catch block to raise error.

I used the following code snippet to alter the trigger, but it is generating the error:

alter trigger trg_InsertInBookIssuedDetails on BOOKISSUEDDETAILS
for insert
as
begin
declare
@UserName nvarchar(50),
@BookID int,
@ReturnMessage nvarchar(50)
begin try
begin tran
select @UserName = IssuedTo,@BookID = BookID from BOOKISSUEDDETAILS
update NewUserRegister set NumberOfBooksIssued = NumberOfBooksIssued+1 where UserName = @UserName
if (select Inventory from LIBRARYBOOKDETAILS WHERE BookID = @BookID)>1
begin
update LIBRARYBOOKDETAILS set Inventory = Inventory - 1 where BookID= @BookID
commit tran
end
end try
else
begin
raiserror('Books UnAvailable Or Cannot Issue More Than 3 Books',16,-1)
rollback tran
end
end
end

Error Message:

Msg 156, Level 15, State 1, Procedure trg_InsertInBookIssuedDetails, Line 19
Incorrect syntax near the keyword 'else'.
Msg 156, Level 15, State 1, Procedure trg_InsertInBookIssuedDetails, Line 25
Incorrect syntax near the keyword 'end'.


Can anyone let me know the error and the changes I should make to perform the deed task with condition.

Please help me out in identifying the error.

Posted On: Monday 19th of November 2012 11:49:09 PM Total Views:  38
View Complete with Replies

RELATED TOPICS OF Microsoft SQL Server PROGRAMMING LANGUAGE




Query string sysntax errors

I am trying to doa query from php... I am using SQL Server I am trying to get something liek this..where I am usign varaivles for the , = signs These are the variables: varpropertyType = Townhome varPropertyFactor = >, , or
VIEWS ON THIS POST

215

Posted on:

Monday 19th November 2012
View Replies!

Syntax error converting the varchar value 'SELECT

hi, I was wondering if some one could help me or check my stored procedure for me, i keep getting this error:- Syntax error converting the varchar value 'SELECT DISTINCT dbo.tbl_Work_Requests.Work_Request_PK, dbo.tbl_Work_Request_Status.Work_Request_Status_Ti tle, dbo.tbl_Line_Of_Business.Line_Of_Business_Name, dbo.tbl_Work_Requests.Work_Request_Number, dbo.tbl_Work_Requests.Work_Request_Title, dbo.tbl_Work_Requests.Work_Request_Rank, This is my stored procdure:- Code: CREATE PROCEDURE FilterProcMain ( @ReferenceNumber varchar(25)=null, @Title varchar(1000)=null, @owner int=null, @status int=null, @team int=null, @lineofbusiness int=null, @requestor varchar(50)=null ) AS Set NoCount ON If (@ReferenceNumber = '') AND(@ReferenceNumber 'N/A') SET @ReferenceNumber = NULL If (@Title = '') AND(@Title 'N/A') SET @Title = NULL If (@owner = '') AND (@owner 1000000) SET @owner = NULL If (@status = '') AND (@status 1000000) SET @status = NULL If (@team = '') AND (@team 1000000) SET @team = NULL If (@lineofbusiness = '') AND (@lineofbusiness 1000000) SET @lineofbusiness = NULL If (@requestor = '') AND(@requestor 'N/A') SET @requestor = NULL /* Variable Declaration */ Declare @SQLQuery AS Varchar(4000) Set @SQLQuery = 'SELECT DISTINCT dbo.tbl_Work_Requests.Work_Request_PK, dbo.tbl_Work_Request_Status.Work_Request_Status_Title, dbo.tbl_Line_Of_Business.Line_Of_Business_Name, dbo.tbl_Work_Requests.Work_Request_Number, dbo.tbl_Work_Requests.Work_Request_Title, dbo.tbl_Work_Requests.Work_Request_Rank, dbo.tbl_Work_Requests.Date_Requested, dbo.tbl_Work_Requests.Work_Request_Requestor, dbo.tbl_Work_Requests.Line_Of_Business_FK, dbo.tbl_Work_Requests.Work_Request_Status_FK FROM dbo.tbl_Line_Of_Business left JOIN dbo.tbl_Work_Request_Status left JOIN dbo.tbl_Work_Request_Tasks left JOIN dbo.tbl_User_Groups left JOIN dbo.tbl_Users ON dbo.tbl_User_Groups.User_Group_PK = dbo.tbl_Users.User_Group_FK ON dbo.tbl_Work_Request_Tasks.User_ID_FK = dbo.tbl_Users.User_ID_PK right JOIN dbo.tbl_Work_Requests ON dbo.tbl_Work_Request_Tasks.Work_Request_FK = dbo.tbl_Work_Requests.Work_Request_PK ON dbo.tbl_Work_Request_Status.Work_Request_Status_PK = dbo.tbl_Work_Requests.Work_Request_Status_FK ON dbo.tbl_Line_Of_Business.Line_Of_Business_PK = dbo.tbl_Work_Requests.Line_Of_Business_FK WHERE (1=1) ' If @ReferenceNumber Is Not Null Set @SQLQuery = @SQLQuery + ' AND Work_Request_Number like %' + @ReferenceNumber + '% ' If @Title Is Not Null Set @SQLQuery = @SQLQuery + ' AND Work_Request_Title like %' + @Title + '% ' If @owner Is Not Null Set @SQLQuery = @SQLQuery + ' AND user_id_fk = ' + @owner +'' If @status Is Not Null Set @SQLQuery = @SQLQuery + ' AND Work_Request_Status_FK = '+ @status + '' If @team Is Not Null Set @SQLQuery = @SQLQuery + ' AND user_group_fk = '+ @team + '' If @lineofbusiness Is Not Null Set @SQLQuery = @SQLQuery + ' AND Line_Of_Business_FK = '+ @lineofbusiness + '' If @requestor Is Not Null Set @SQLQuery = @SQLQuery + ' AND Work_Request_Requestor like %' + @requestor + '%' Execute FilterProcMain @SQLQuery If @@ERROR 0 GoTo ErrorHandler Set NoCount OFF Return(0) ErrorHandler: Return(@@ERROR) GO
VIEWS ON THIS POST

225

Posted on:

Monday 19th November 2012
View Replies!

Union giving me error msg about distinct use

when i try to run this following query, i get an error message: Microsoft OLE DB Provider for SQL Server error '80040e14' The ntext data type cannot be selected as DISTINCT because it is not comparable. /resultados_termo.asp, line 176 the query: select * from view_veiculos where ativo='1' and ( nome_marc like '%fiat%' or nome_mod like '%fiat%' or estado like '%fiat%' or cidade like '%fiat%' or ano like '%fiat%' ) and ( nome_marc like '%brava%' or nome_mod like '%brava%' or estado like '%brava%' or cidade like '%brava%' or ano like '%brava%' ) and ( nome_marc like '%2004%' or nome_mod like '%2004%' or estado like '%2004%' or cidade like '%2004%' or ano like '%2004%' ) union select * from view_veiculos where ativo='1' and ( nome_marc like '%fiat%' or nome_mod like '%fiat%' or estado like '%fiat%' or cidade like '%fiat%' or ano like '%fiat%' ) and ( nome_marc like '%brava%' or nome_mod like '%brava%' or estado like '%brava%' or cidade like '%brava%' or ano like '%brava%' ) union select * from view_veiculos where ativo='1' and ( nome_marc like '%fiat%' or nome_mod like '%fiat%' or estado like '%fiat%' or cidade like '%fiat%' or ano like '%fiat%' ) union select * from view_veiculos where ativo='1' and ( nome_marc like '%brava%' or nome_mod like '%brava%' or estado like '%brava%' or cidade like '%brava%' or ano like '%brava%' ) and ( nome_marc like '%2004%' or nome_mod like '%2004%' or estado like '%2004%' or cidade like '%2004%' or ano like '%2004%' ) union select * from view_veiculos where ativo='1' and ( nome_marc like '%brava%' or nome_mod like '%brava%' or estado like '%brava%' or cidade like '%brava%' or ano like '%brava%' ) union select * from view_veiculos where ativo='1' and ( nome_marc like '%2004%' or nome_mod like '%2004%' or estado like '%2004%' or cidade like '%2004%' or ano like '%2004%' ) when i use UNION ALL, i get repeated rows. i need the select distinct on it. please, help.
VIEWS ON THIS POST

403

Posted on:

Tuesday 20th November 2012
View Replies!

Microsoft OLE DB Provider for ODBC Drivers error '80040e31'

I keep getting the following error: Microsoft OLE DB Provider for ODBC Drivers error '80040e31' [Microsoft][ODBC SQL Server Driver]Timeout expired /admin/functions_web.asp, line 277 This application has been up for 5 years and we never had any problems and all of a sudden we have started getting this error. We have the latest MDAC version. I have increased the scripting and connection time and we still get this error. Please help!
VIEWS ON THIS POST

193

Posted on:

Tuesday 20th November 2012
View Replies!

Error converting data type varchar to int error?

l, hope you can help. got this error: Microsoft OLE DB Provider for SQL Server (0x80040E07) Error converting data type varchar to int. /admin/stores/index.asp, line 520 but no idea what to do what it is line 520 is below: Code: Sub UpdateZoneDisplay() sSQL = "EXEC spUpdateZones " & iStoreID & ",'" & sfk_zoneID & "," & sfk_storeID & "'" SET oRS = oDB.execute(sSQL) istoreID = oRS("storeID") End Sub any help would be brill kind regards MG
VIEWS ON THIS POST

246

Posted on:

Tuesday 20th November 2012
View Replies!

ASP syntax error when move from Access to SQL Server

,
VIEWS ON THIS POST

225

Posted on:

Tuesday 20th November 2012
View Replies!

Syntax error converting datetime from character string

I urgently need some help I have written a .NET windows service that processes some XML's and updates some fields in an SQL Server 2000 DB. It works ok on my workstation. But at the Client's site it causes an exception with the following error: "Syntax error converting datetime from character string" I heard it might have something to do with regional settings for the workstation and SQL Server Language Settings but im not sure. Could somebody please help with this issure!
VIEWS ON THIS POST

360

Posted on:

Tuesday 20th November 2012
View Replies!

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

hi ,i don know shd i ask this question here but i just post because i really cant find a solution,because whole project i done in my local pc but now i transfered to server it face problem and i need to pass up the project today. i got an error message when i try to access the page, www.woodlandssec.sg/index.asp Microsoft OLE DB Provider for ODBC Drivers error '80004005' [MySQL][ODBC 3.51 Driver]Access denied for user: 'ODBC@CAPITAL' (Using password: NO) /db.asp, line 11 i included this db.asp in my index.asp and this is the coding. and what if want to host the mysql server on the same server i host the asp pagesall i need is to replace IP for localhost with the IP address of the server this is the whole coding for my db.asp
VIEWS ON THIS POST

215

Posted on:

Tuesday 20th November 2012
View Replies!

Triggers on errors

We have a DB with (what should be) a unique set of data defined by three fields. The database is so poorly designed that there is nothing in place to constrain these items to be unique and something is taking advantage of the fact. I need to identify what. I can set up a constraint forcing the fields to be unique, but, is it possible to set up a trigger that will 1. fire when that constraint is violated 2. write a record to a log table 3. display an error message 4. carry on without inserting the record
VIEWS ON THIS POST

152

Posted on:

Tuesday 20th November 2012
View Replies!

Help with login failed error

hi, On a file server, I set up a connection to my sql server 2005 database, that resides on my machine. I tested the connection and that seems ok. However, when I try to connect to the database with a login, it tells me "LOGIN FAILED FOR USER domain NAME\ server name & "$" I am not sure what to do here. I think I need to add another user, but I don't know how I should do that or what the settings should be. Can somebody help
VIEWS ON THIS POST

244

Posted on:

Tuesday 20th November 2012
View Replies!

[Microsoft][SQL Native Client]TCP Provider: Timeout error [258]

hello , I am new in this pls help me to overcome this error..... Seering this error teh past few days and normal site across a lot of different pages. , i am using sql server 2005 [Microsoft][SQL Native Client]TCP Provider: Timeout error [258] error categorey : Microsoft OLE DB Provider for ODBC Drivers
VIEWS ON THIS POST

507

Posted on:

Tuesday 20th November 2012
View Replies!

Insert trigger causes "the data was added to the database but ..." error

dear friends ! could you please help me ! i have a sql server 2005 database and an ms access project. as soon as i added this trigger to a table SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER trg_insert_building ON tbl_building AFTER INSERT AS declare @buildind_id int BEGIN SET NOCOUNT ON; select @building_id = inserted.id from inserted insert into tbl_bf (building_id, foundation_id) select @building_id as bilding_id, id from tbl_foundation END GO i started getting the following message: microsoft office access: the data was added to the database but the data won't be displayed in the form because it doesn't satisfy the criteria in the underlying record source.
VIEWS ON THIS POST

219

Posted on:

Tuesday 20th November 2012
View Replies!

CDO error

I am trying to write a stored procedure that will send an email whenever a certain field in the database is changed. I am using CDO procedures for this. Here is my code (abbreviated) CREATE PROCEDURE dbo.sendMail @from VARCHAR(100), @to VARCHAR(100), @subject VARCHAR(200), @mBody VARCHAR(1000) AS DECLARE @MailID int DECLARE @err int DECLARE @sSource int DECLARE @sDescription VARCHAR(100) DECLARE @sMsg VARCHAR(200) EXEC @err = sp_OACreate 'CDONTS.NewMail', @MailID OUT EXECUTE sp_OAGetErrorInfo @MailID, @sSource OUT, @sDescription OUT SET @sMsg = ' Error # = ' + CAST(@err AS VARCHAR(20)) + '. Description = ' + @sDescription RAISERROR (@sMsg, 16, 1) GO I call this stored procedure through a trigger that looks like this CREATE TRIGGER tr_Categories_1 ON [dbo].[Categories] FOR INSERT, UPDATE, DELETE AS IF UPDATE(CategoryName) BEGIN EXECUTE sendMail 'xyz@abc.com', 'xyz@abc.com', '', 'The field has changed' END However, when I update the table I get the following error Server: Msg 50000, Level 16, State 1, Procedure sendMail, Line 16 Error # = -2147221005. Description = Invalid class string I am not sure why I am getting this error, and documentation on the internet is not helping.
VIEWS ON THIS POST

240

Posted on:

Tuesday 20th November 2012
View Replies!

Microsoft OLE DB Provider for SQL Server error '80040e21'

When I try to insert a record into a table I get the following error. Microsoft OLE DB Provider for SQL Servererror '80040e21' Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done It's in SQL server. Here's the asp code to insert the record. Code: Testcase Anyone who can help I am very new to asp. I tried solving it with a trigger but it won't work cause the record can't be inserted without primary key. Thanx in advance Mojito
VIEWS ON THIS POST

176

Posted on:

Tuesday 20th November 2012
View Replies!

ASP SQL installation error problem

Error installing aspportal.net on godaddy SQL database: "Microsoft OLE DB Provider for SQL Server error '80040e37' Invalid object name 'siteoptions'. D:\HOSTING\xxxxx\CONTENT\HOME\../../content/stats/incstats.asp, line 4 " Please help. We're stuck!
VIEWS ON THIS POST

324

Posted on:

Tuesday 20th November 2012
View Replies!

Help with error 18456 severity 18, state 8

hi, I have a sql 2005 db on a server. When users use this database, they all end up getting this message: error 18456 severity=18, state=8 along with their login and client address information. I was reading somewhere that maybe it has something to do with filesharing But I don't understand why. Another person said it's because the log file is getting too large and boots the user out. I don't know where to look. The guy who wrote the software says to limit the log files, but I don't think that's solving the problem. Any ideas
VIEWS ON THIS POST

138

Posted on:

Tuesday 20th November 2012
View Replies!

SQL Server error '80040e14'

Hi I'm getting this error in my search.asp Microsoft OLE DB Provider for SQL Servererror '80040e14' Line 1: Incorrect syntax near '/'. /include/search.asp, line 184 Am not sure where the error is....am fairly new at asp so please help!
VIEWS ON THIS POST

105

Posted on:

Tuesday 20th November 2012
View Replies!

Catching MSSQL errors gracefully

I have a code which serves up SQL records as XML. Problem is, sometimes this code catches. Obviously, there should be no SQL syntax errors, but in the event that there is, howto halt the error reporting directly to screen Code: [ODBC SQL Server Driver] [SQL Server] "I hate you, human" An ASP Classic solutiono please.
VIEWS ON THIS POST

137

Posted on:

Tuesday 20th November 2012
View Replies!

Internal server error 500

, Any one here can help with a SQL prblm The setup is a Windows2000 with IIS cluster and a BE SQL DB The following message appears on clients browser: "Internal server error 500" "Exception:Exception" "The maximum concurrent connections exceeded.Please try again later" NOTE: A limited number of clients can connect
VIEWS ON THIS POST

273

Posted on:

Tuesday 20th November 2012
View Replies!

I/O error (bad page ID) detected during read at offset 0x0000000f3e4000

Can anyone help me with this error Microsoft OLE DB Provider for ODBC Drivers (0x80004005) [Microsoft][ODBC SQL Server Driver][SQL Server]I/O error (bad page ID) detected during read at offset 0x0000000f3e4000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\RVHS_database.mdf'. What should i do to recover my database Please help me out
VIEWS ON THIS POST

178

Posted on:

Tuesday 20th November 2012
View Replies!