December 17, 2013 at 5:30 am
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.
December 17, 2013 at 5:37 am
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
December 17, 2013 at 5:43 am
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.
December 17, 2013 at 5:55 am
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
December 17, 2013 at 6:11 am
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
January 1, 2014 at 12:46 pm
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
Change is inevitable... Change for the better is not.
January 1, 2014 at 12:47 pm
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
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy