Solved

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

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

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…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

831 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