SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to Read tables from Inserted table (trigger)


How to Read tables from Inserted table (trigger)

Author
Message
Minnu
Minnu
Mr or Mrs. 500
Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)

Group: General Forum Members
Points: 559 Visits: 950
Hi Team,

am having a table
Table : Test

ID Name Desc
1 D Desk
2 D Desk1
3 X TBT1
4 Y TXT


having trigger on above table for insert and update, when am update above table.

udpate Test set name='DIT' where name='D'

when i execute above update stmt, two records are changed, how to print the two 'ids'

from inserted table am getting only first id.
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28449 Visits: 39963
yeah syntax is important here;

you already identified that you know the a trigger should refer to the virtual inserted or deleted tables;

the key is that the original table needs to join to inserted and/or deleted in order to properly update/

most likely, what you want is this inside your trigger


CREATE TRIGGER TR_Test_IU ON Test
FOR INSERT,UPDATE
AS
BEGIN
UPDATE MyTarget
set MyTarget.Name='DIT'
FROM Test MyTarget
INNER JOIN INSERTED
ON Test.ID = INSERTED.ID
WHERE name='D'
END --TRIGGER



Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Minnu
Minnu
Mr or Mrs. 500
Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)

Group: General Forum Members
Points: 559 Visits: 950
Hi,

Thank you for Speedy response.

my question is, when i print the values from trigger

print @id
print @name
Print '--'

am getting
1
D
--

but as per my table id's 1 and 2 are updting, i want 1 and 2 values to be print as output.

Please suggest.
twin.devil
twin.devil
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2383 Visits: 2674
It not a very good idea to print data in Triggers, If you want to know how many updated are you should have a log table in which multiple rows can get inserted and can be reviewed. Saying so if you still want to print the result set you can do the following


Declare @inserted table ( id int, Name varchar(200))
Declare @v_print as varchar(500) = ''

insert into @inserted
Select 1, 'DT' union all
select 2, 'DT'

Select @v_print = @v_print +'ID:'+ Cast(id as varchar(20)) + ', Name = ' + Name + '
'
from @inserted

print @v_print


Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28449 Visits: 39963
Minnu (12/17/2013)
Hi,

Thank you for Speedy response.

my question is, when i print the values from trigger

print @id
print @name
Print '--'

am getting
1
D
--

but as per my table id's 1 and 2 are updting, i want 1 and 2 values to be print as output.

Please suggest.


triggers in SQL have to assume they are touching more than one row of data; as soon as you assign a value(ie SELECT @id = ID FROM INSERTED) you break that rule; if there are multiple rows in the insert/update, you end up getting the first or last row of the data assigned to the variable...and you don't know which one it was.

so for a spot check during development, yeah maybe i'd see a variable, but it should not exist once your testing is complete.
in general, if i see a variable in a trigger, i assume it's written incorrectly.
this is one of the first spot checks i run when reviewing a new-to-me database

--triggers have variables defined?
select * from sys.triggers where object_definition(object_id) like '%@%'



if this command was inserted, which values, woudl you think appear in your print statments? there are four values to choose from for each column:

INSERT INTO Test([ID],[Name],[Desc])

SELECT '1','D','Desk' UNION ALL
SELECT '2','D','Desk1' UNION ALL
SELECT '3','X','TBT1' UNION ALL
SELECT '4','Y','TXT'



Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87248 Visits: 41113
twin.devil (12/17/2013)
It not a very good idea to print data in Triggers...


Except for troubleshooting, apparently MS agrees because they've deprecated the ability to return output of a trigger to the screen. Such functionality will be removed in a future version of T-SQL.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87248 Visits: 41113
Minnu (12/17/2013)
Hi Team,

am having a table
Table : Test

ID Name Desc
1 D Desk
2 D Desk1
3 X TBT1
4 Y TXT


having trigger on above table for insert and update, when am update above table.

udpate Test set name='DIT' where name='D'

when i execute above update stmt, two records are changed, how to print the two 'ids'

from inserted table am getting only first id.


I'm not sure there's a need for the trigger. What does it do? If it's simply returning this output, then use the OUTPUT clause of the INSERT statement instead.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search