How to Read tables from Inserted table (trigger)

Question

Post reply

How to Read tables from Inserted table (trigger)

Minnu

SSCertifiable

Points: 6451
More actions
December 17, 2013 at 5:30 am

#286167

Hi Team,
am having a table
Table : Test
IDNameDesc
1DDesk
2DDesk1
3XTBT1
4YTXT
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.

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

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

Lowell SSC Guru Points: 323524 More actions · Answer 1

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 SSCertifiable Points: 6451 More actions · Answer 2

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.

Umer Akram SSC-Insane Points: 22209 More actions · Answer 3

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 SSC Guru Points: 323524 More actions · Answer 4

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 SSC Guru Points: 1004601 More actions · Answer 5

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.

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)

Jeff Moden SSC Guru Points: 1004601 More actions · Answer 6

Minnu (12/17/2013)
Hi Team,
am having a table
Table : Test
IDNameDesc
1DDesk
2DDesk1
3XTBT1
4YTXT
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.

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)

How to Read tables from Inserted table (trigger)

Cookies on SQLServerCentral