Solved

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

Posted on 2011-03-18
3
471 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
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

910 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now