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

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?
njgroupAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
JoeNuvoConnect With a Mentor Commented:
try put this after else

RAISERROR ('Message Here', 16, 10)

Open in new window

0
 
sarabhaiConnect With a Mentor Commented:
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
 
kumarnimavatConnect With a Mentor Commented:
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
All Courses

From novice to tech pro — start learning today.