Click here to monitor SSC
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
Old Hand
Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)

Group: General Forum Members
Points: 305 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
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14955 Visits: 38950
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
Old Hand
Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)

Group: General Forum Members
Points: 305 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
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1376 Visits: 2597
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
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14955 Visits: 38950
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-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45047 Visits: 39898
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45047 Visits: 39898
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

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