Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to Read tables from Inserted table (trigger) Expand / Collapse
Author
Message
Posted Tuesday, December 17, 2013 5:30 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 1:09 AM
Points: 227, Visits: 715
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.
Post #1523624
Posted Tuesday, December 17, 2013 5:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 12,910, Visits: 32,015
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1523631
Posted Tuesday, December 17, 2013 5:43 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 1:09 AM
Points: 227, Visits: 715
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.
Post #1523636
Posted Tuesday, December 17, 2013 5:55 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 5:03 AM
Points: 701, Visits: 1,260
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

Post #1523647
Posted Tuesday, December 17, 2013 6:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 12,910, Visits: 32,015
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1523653
Posted Wednesday, January 1, 2014 12:46 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:38 AM
Points: 36,988, Visits: 31,511
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1526946
Posted Wednesday, January 1, 2014 12:47 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:38 AM
Points: 36,988, Visits: 31,511
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1526947
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse