Trigger result from inserted / Deleted

Question

Trigger result from inserted / Deleted

Minnu

SSCertifiable

Points: 6451
More actions
November 25, 2015 at 8:11 am

#322551

Hi Team,
using below trigger,
ALTER TRIGGER [dbo].[t_info] ON [dbo].unify_info
FOR update
AS
begin
declare @val1 varchar(100) declare @val2 varchar(100) declare @temp varchar(100)
set @temp='HHT_Report'
set @val1='SELECT ' +@temp+ ' FROM INSERTED'
set @val2='SELECT ' +@temp+ ' FROM DELETED'
EXEC (@val1)
end
--
i want the result of below query's from above trigger.
SELECT HHT_Report FROM INSERTED
SELECT HHT_Report FROM DELETED

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Minnu (11/25/2015)
Hi Team,
using below trigger,
ALTER TRIGGER [dbo].[t_info] ON [dbo].unify_info
FOR update
AS
begin
declare @val1 varchar(100) declare @val2 varchar(100) declare @temp varchar(100)
set @temp='HHT_Report'
set @val1='SELECT ' +@temp+ ' FROM INSERTED'
set @val2='SELECT ' +@temp+ ' FROM DELETED'
EXEC (@val1)
end
--
i want the result of below query's from above trigger.
SELECT HHT_Report FROM INSERTED
SELECT HHT_Report FROM DELETED

Why are you creating a new thread for a trigger on this table? Why dynamic sql? What are you trying to do here? You need to provide a lot more details in your questions. We are really good a t-sql but most of us are not very good at guessing details to help you write code. Please see the first article in my signature. Or this one. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/[/url]

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

Why are you returning results from a trigger? The ability to return results from triggers has been deprecated. From BOL:

Returning Results
The ability to return results from triggers will be removed in a future version of SQL Server. Triggers that return result sets may cause unexpected behavior in applications that are not designed to work with them. Avoid returning result sets from triggers in new development work, and plan to modify applications that currently do this. To prevent triggers from returning result sets, set the disallow results from triggers option to 1.
Logon triggers always disallow results sets to be returned and this behavior is not configurable. If a logon trigger does generate a result set, the trigger fails to execute and the login attempt that fired the trigger is denied.

Also you can't use dynamic sql to access data in the inserted and deleted tables because the dynamic SQL creates a new execution context.

As Sean has asked. If you put let us know what needs to be accomplished we may be able to provide another method.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 3

Jack Corbett (11/25/2015)
Why are you returning results from a trigger? The ability to return results from triggers has been deprecated. From BOL:

There goes yet another great troubleshooting tool. I wish MS would add features instead of taking them away,

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

Jeff Moden (11/25/2015)
Jack Corbett (11/25/2015)
Why are you returning results from a trigger? The ability to return results from triggers has been deprecated. From BOL:
There goes yet another great troubleshooting tool. I wish MS would add features instead of taking them away,

Or even fixing some of the things that have been broken for years and years.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

You could try using the OUTPUT clause in your code, instead of trying to use dynamic sql in your triggers.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 6

Minnu (11/25/2015)
Hi Team,
using below trigger,
ALTER TRIGGER [dbo].[t_info] ON [dbo].unify_info
FOR update
AS
begin
declare @val1 varchar(100) declare @val2 varchar(100) declare @temp varchar(100)
set @temp='HHT_Report'
set @val1='SELECT ' +@temp+ ' FROM INSERTED'
set @val2='SELECT ' +@temp+ ' FROM DELETED'
EXEC (@val1)
end
--
i want the result of below query's from above trigger.
SELECT HHT_Report FROM INSERTED
SELECT HHT_Report FROM DELETED

You want the results from those 2 queries where? hmmmm

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]