How to get resultset from a SQL Server trigger

Question

I do some validations and checks in SQL Server triggers, but I couldn't manage to get a custom select statement from a trigger. I need to get it into a temp table or into some variables, how to do that?

Table structure:

create table Orders
(
    ID int identity(1,1), 
    OrderNo nvarchar(50), 
    Product nvarchar(100), 
    Quantity int
)

My trigger:

create trigger [dbo].[OrderInsertTrigger]
on [dbo].[Orders]    
instead of insert
as
begin
    set nocount on;

    -- Some Checks here for validation..  some codes..

    select 1 as result, 'insert is successful' as message return
end

Insert query to test;

insert into Orders (OrderNo, Product, Quantity) 
    select 123, 'ice cream', 100

I tried sub brackets (just guessing to capture trigger output) but did not work;

 Declare @temp table(bit result, msg nvarchar(200))
 insert into @temp  
 from
 (insert into Orders(OrderNo,Product,Quantity) select 123, ''ice cream'', 100') 
as t

I tried something dynamic SQL but did not work;

 Declare @temp table(bit result, msg nvarchar(200))
 insert into @temp  Exec('insert into Orders(OrderNo,Product,Quantity) select 123, ''ice cream'', 100')

What is the way to capture that custom select statement from the trigger? or is it possible?

EDIT

My goal is actually, I want to do some validations before the CRUD operations to prevent SQL errors like field nullable check, foreign key check, value range check or some custom check according to what customer needs. I want to reflect my custom response to the client from SQL Server (maybe from trigger or ???)

So my question, how to make audits, validations in a efficient way for too many SQL Server tables in a static or dynamic way?

Don't Returning a dataset in a trigger is deprecated and will be removed from SQWL Server. This feels like an XY Problem. — Thom A, Apr 11 '20 at 12:21
I think you don't want to use constraints buy NULL constraint and another type of constraint will be the more true selection for you. On the other hand, if you don't solve your issues with it, you can use the trigger and control them if this validation is not true you can rollback and return an error. As a result, your question can have a very wide range of answer. — Esat Erkec, Apr 11 '20 at 13:21
My opinion is you shouldn't prevent SQL errors but instead handle them appropriately in the app code. That will allow you to leverage declarative constraints and resort to triggers only when they cant do the job. In the case of a trigger rule, raise an error with the needed contextual info and also handle on the app side. — Dan Guzman, Apr 11 '20 at 13:31
I can capture a custom raised error, in a try cath block on the app side, I noticed that idea, But I just remember there are sql table types. Now I have a new idea i will test it, which can be an alternative to tigger logic. My idea is sendig CRUD request (in a temp table) to a sql procedure using sql table types, then make the audit, if passes do the CRUD operation in the same proceudure and return the results from that procedure as an output. I m going to try it — Ali CAKIL, Apr 11 '20 at 15:14

score 2 · Answer 1 · answered Apr 11 '20 at 12:26

The dynamic SQL query in your question will work if you fix the syntax error in the table variable declaration:

DECLARE @temp TABLE(result bit, msg nvarchar(200));
INSERT INTO @temp
    EXEC('insert into Orders(OrderNo,Product,Quantity) select 123, ''ice cream'', 100');
SELECT * FROM @temp;

Be aware that one should not return results sets from a trigger and the ability to do so will be removed in a future SQL Server version. There may be better ways to accomplish the end result for this x-y problem.

it gives the error : An INSERT EXEC statement cannot be nested. I agree with you for X, i feel it seems like a XY problem, I am going to edit my question — Ali CAKIL, Apr 11 '20 at 12:47

How to get resultset from a SQL Server trigger

1 Answers1