June 21, 2011 at 12:28 pm
Hello,
Within a trigger on a table can we identify the value of column which is getting inserted? Example suppose there is a table emp with column id, suppose I insert 1 in the table, can i identify within the trigger that 1 was inserted?
Please let me know if you have any solution to this.
thanks,
aman
June 21, 2011 at 12:31 pm
Yes you can use the "inserted" table.
select * from inserted
This will be the same structure as your table but will be the new values. In a delete trigger you have the "deleted" table. In an update you have both.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 21, 2011 at 12:59 pm
Thanks a ton i did work, last one thing for update is the table name updated?
June 21, 2011 at 1:02 pm
No, in an update you "inserted" (the new values) and "deleted" (the current values). That make sense?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 21, 2011 at 1:04 pm
Just a thought about triggers. Make sure you build your logic so that it can handle more than 1 record. In other words you want to join to the inserted table instead doing things like select myValue from Inserted. I will be happy to take a look at your trigger if you want to post the code.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 21, 2011 at 1:29 pm
As per the requirment there will be always more than one inserted in the table at one time.
I am still writting the code suggested by you. will post it soon.
I was not sure about what you were trying to say about joins here?
June 21, 2011 at 2:10 pm
Well all too often people will write an insert trigger to create an audit trail or something along those lines. Let's assume there are 3 records being inserted at once and they will do things like this:
declare @MyVar int
set @MyVar = select MyVal from inserted
insert MyAudit (MyAuditVal) values (@MyVal)
This doesn't work because it only supports a single record insert.
insert MyAudit (MyAuditVal)
select MyVal from inserted
This is not the best example of what I am trying to describe but I think you get the idea.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply