Solved

how to return exception or boolean or string from trigger in sql server 2005

Posted on 2011-03-18
3
477 Views
Last Modified: 2013-12-17
hi,

I have sql instead of trigger to eliminate a record to be inserted unless some condition is true,
if record is not inserted, I want to add something to the trigger to notify my program that this record has not been inserted, for example, to return string "not_inserted" or boolean or even generating exception (looks like when there is error in inserting record) or any other thing

I have this code in trigger:

if @nRow=0
Begin
   Insert into ProductType (EnProductType, ArProductType, EnDescription, ArDescription)
   Select dbo.TRIM(EnProductType), dbo.TRIM(ArProductType), EnDescription, ArDescription from Inserted
end
else
// what to add here to return false or exception or string to notify my program?
0
Comment
Question by:njgroup
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 11

Accepted Solution

by:
JoeNuvo earned 200 total points
ID: 35170360
try put this after else

RAISERROR ('Message Here', 16, 10)

Open in new window

0
 
LVL 9

Assisted Solution

by:sarabhai
sarabhai earned 150 total points
ID: 35171489
this is a sample code for

CREATE TRIGGER trg_sample on T_Table
FOR INSERT
AS
BEGIN TRY
--Statements
RAISERROR('oops', 0, -1)
END TRY
BEGIN CATCH
PRINT 'oops'
SELECT 1 AS Exception
END CATCH
go
0
 
LVL 3

Assisted Solution

by:kumarnimavat
kumarnimavat earned 150 total points
ID: 35171655
You may try using the below given query that uses try and catch constructs. Put your code in between the BEGIN TRY and END TRY

BEGIN TRY
     if @nRow=0
Begin
   Insert into ProductType (EnProductType, ArProductType, EnDescription, ArDescription)
   Select dbo.TRIM(EnProductType), dbo.TRIM(ArProductType), EnDescription, ArDescription from Inserted
end
else
END TRY

BEGIN CATCH
     SELECT
          ERROR_NUMBER() as ErrorNumber,
          ERROR_MESSAGE() as ErrorMessage;
END CATCH;
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question