How to read the content of PRINT statement of a trigger from SQL Server, and display it in a view?

Question

I have a trigger:

CREATE TRIGGER [dbo].[trg_Book_Insert_amount] ON [dbo].[BOOKS]
FOR INSERT
AS
    declare @amount int;
    SELECT @amount = i.amount FROM inserted i;
    IF(@amount >50 OR @amount <0)
    BEGIN
        PRINT 'AMOUNT MUST BE A VALUE BETWEEN 0 AND 50'
        ROLLBACK
    END

I want to show that notification ('AMOUNT MUST BE A VALUE BETWEEN 0 AND 50') if user of my website tried to insert with an invalid value of @amount. Could you suggest a solution for me?

I'm sorry if there is already a method, I've tried to search but have not found anything.

This may help: http://stackoverflow.com/questions/1880471/capture-stored-procedure-print-output-in-net — Kieren Johnstone, May 25 '15 at 12:59
Additionally, this is a bit of an extreme way of stopping data getting into your database. It's a fairly sure way of making sure data in the DB follows the rules, but is there honestly no case where the 'amount' should be out of that range (for example, a manual correction or override)? — Kieren Johnstone, May 25 '15 at 13:00
@KierenJohnstone Thank you for your link, may in WebForm it's helpful but I'm using MVC 4 Razor and it seems that there is no SqlInfoMessageEventArgs class for me, or (maybe) we can use but I still dont know how. Can you suggest me an approach? — Noob From Mars, May 25 '15 at 13:10
it's very bad that you have business logic in trigger.If you can, avoid it in trigger (and trigger itself, if possible). — Anton Putau, May 25 '15 at 13:57
This should raise an error rather than being a print statement. And once you accept that a simpler and more efficient way would be to add a check constraint to the column. — Martin Smith, May 25 '15 at 16:09
Oh and by the way your trigger is broken for multirow inserts anyway. — Martin Smith, May 25 '15 at 16:19
@MartinSmith yes, but people will insert from Razor View and I think just one row can be inserted when they hit the button. — Noob From Mars, May 25 '15 at 22:22

score 4 · Accepted Answer · edited May 26 '15 at 11:17

I think you can't use "print" to return any value You have to use "select" instead:

SELECT 'AMOUNT MUST BE A VALUE BETWEEN 0 AND 50'

Then when run insert query, be sure that you execute commend by use ExecuteScalar.

But I don't prefer above solution, while you are using MVC4 please use data annotation as below :

On the class file of the Book, make use for data annotations:

   using System.ComponentModel.DataAnnotations;

And change the field declaration to :

  [Range(0,50,ErrorMessage="Amount must be between 0 and 50")]
   public int Amount { get; set; }

Now on the view add this after Amount field:

  @Html.ValidationMessageFor(model => model.Amount, "", new { @class = "text-danger" })

And in your controller you must check if the current ModelState is valid.

public ActionResult Post(CreateBook book)
{
    if (ModelState.IsValid) // this will check your model for validations like range, required and so on 
    {

    }
}

More reading here: https://msdn.microsoft.com/en-us/library/dd901590%28VS.95%29.aspx?f=255&MSPPError=-2147217396

And about validation using data annotations here: http://www.asp.net/mvc/overview/older-versions-1/models-%28data%29/validation-with-the-data-annotation-validators-cs

And get rid of that trigger. Non-validated data mustn't get throught your business to the database.

Thank you, I understood what I did wrong. Your method works fine. — Noob From Mars, May 26 '15 at 15:37

How to read the content of PRINT statement of a trigger from SQL Server, and display it in a view?

1 Answers1