Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
austins
Starting Member
2 Posts |
 Posted - 2002-06-12 : 15:18:33
|
| How long do the records in the inserted table stay in the inserted table?I'm writing a trigger that keeps a 'LastUpdated' column updated. However, if the inserted table keeps records very long, I could end up updating records that weren't updated or were previously timestamped in the 'LastUpdated' column.I thought a better approach would be to write the trigger to update based on the foreign key in the table but I can't find a way to reference that field in an update trigger. Is there a way to reference the elements in the updated record within a trigger?sorry for the duality of the question...Thanks!Austin |
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2002-06-12 : 15:56:04
|
| Q1) How long does the inserted table exist in a triggerA1) Just for the life time of that trigger, from beginning to end after that it's gone To answer the rest of your post you should be using a TIMESTAMP column if you want to keep track of when the record was last changed. The server will update that column everytime a change occurs and you shouldn't need to use a trigger to do that. Maybe I missed something but I think that covers it... |
 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-06-12 : 16:04:57
|
inserted and deleted are fuzzy tables. Books on Line refers to them as temporary, but that's not to be confused with temporary tables identified by the first character of the name being a '#' and their existance in tempdb....I tend think of inserted and deleted by adding the word 'just' as a prefix, i.e. 'just_inserterd' and 'just_deleted', as SQL Server employees after-triggers. So if you insert into foo(bar) values ('foobar'), your inserted table will consist of one row, if and when you reference it a trigger on insert or update. Likewise, if you insert into foo(bar) select 'foobar' union select 'barfoo, your inserted table will consist of two rows.When you say 'inserted table keeps records very long', it implies to me a misunderstanding of the nature of these fuzzy tables. If there is not trigger on a table you insert into, then there is no inserted table (sorta like if a tree falls in the wood, but there is no on around to hear it does it make a sount....well, here, I am saying no). If you insert once into a table, then insert again into a table and you do have a trigger, your first insert will execute the dml in the trigger and the inserted table will contain the rows just_inserted from you first insert. Then on your second insert, the trigger dml is executed again and this time inserted will have a completely new rowset of data corresponding to the rows just_inserted by your second insert......so if in your trigger you have something likeupdate fooset lastupdated = getdate()from foo inner join inserted on foo.id = inserted.id then you will only update the lastupdated columns in foo for the records you just_inserted. The next time the trigger fires, it'll only be for those records...I have no idea what you mean by...quote:
I thought a better approach would be to write the trigger to update based on the foreign key in the table but I can't find a way to reference that field in an update trigger. Is there a way to reference the elements in the updated record within a trigger?
<O> |
|
|
|
austins
Starting Member
2 Posts |
Posted - 2002-06-12 : 16:17:03
|
| Thanks for the help. Unit 15 on this page - http://www.mssqlserver.com/tsql/ - helped me understand the inserted/deleted tables but it didn't talk too much about the lifetime of the table.Thanks again!Austin |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-12 : 16:32:01
|
| "Lifetime" is not an accurate term for the inserted and deleted tables. There's no such thing for them.If you perform an INSERT or UPDATE command, the rows affected by it appear in the inserted table for as long as the operation runs. This takes milliseconds in most cases unless you're updating a very large number of rows (100,000+)Also, in SQL and other relational database products, there are the ACID properties of a transaction: Atomic, Consistent, Isolated, and Durable. Atomic means that the entire transaction succeeds or the entire thing fails, no one part can fail. This keeps data Consistent. Isolation means that 2 or more processes do not interfere with each other, and Durability means that once the changes are committed they are permanently recorded. Sorry for repeating this if you already know it, but I do have a point.Now, not only is "lifetime" meaningless, but the isolation of transactions means that 2 or more people updating rows at the same time will see two completely different inserted tables. There is no way for you to make those 2 processes see what the other is doing. Whatever extra work you want to do will only happen within the session that performed the insert, and will only be seen by that session. Once those transactions complete, you'd have to start ANOTHER transaction/UPDATE/INSERT in order to see what the other processes changed.Hope this didn't muddy the waters. |
|
|
|
|
|
|
|
|
"inserted" table record lifetime