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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply