INSERTED table

I have a qn regarding the INSERTED table.

Whenever a row is inserted into the table i understand that the INSERTED table also gets that particular row. But how long does that particular row stay thr? Till another new row is inserted into the table? which means that rows get overwritten whenever a row is inserted?

Hope everyone understands what i am trying to say. Would be kind of you to reply.thanks!

GayathriPlease provide an example of the sql.|||SELECT JobNumber
from inserted
where ServiceType = 'On-site' and ServiceStatus = 'NEW' and DateModified=(SELECT MAX(DateModified) from inserted)

when i execute this statement alone i will only get one job number but when i put this into a trigger and channel the output into a cursor to copy it into a variable it selects some other job number as well...This is the whole code

DECLARE job_number_cursor CURSOR FOR
SELECT JobNumber
from inserted
where ServiceType = 'On-site' and ServiceStatus = 'NEW' and DateModified=(SELECT MAX(DateModified) from inserted)
OPEN job_number_cursor
FETCH NEXT FROM job_number_cursor INTO
@.job_number

CLOSE job_number_cursor
DEALLOCATE job_number_cursor

I was hoping to get just one output since i thought the INSERTED table only contains the last inserted row.|||I understand what you are asking...

Basically you are saying,...

When a table has a trigger on it the trigger has access to a table called inserted (assuming it is an insert trigger). How long does the inserted table with the insert record exist...

In all honesty, I'm not sure, but I would say it would exist until the insert and the associated trigger (if there is one) has been completed...

I'll look up some resources and see what I can find.|||Um,... question,... when you use your cursor are you doing an update or insert into the table with the trigger on it??|||I am inserting|||okie,... so lets think about that for a sec...

you are in the middle of an insert, your trigger fires which opens a cursor which does an insert (loop to start and insert a new record into the inserted table)

your cursor is still open when you do your second insert and it references the same inserted table... which now has the new record in it...

does that make sense??|||Check out your bol:

The inserted table stores copies of the affected rows during INSERT and UPDATE statements. During an insert or update transaction, new rows are added simultaneously to both the inserted table and the trigger table. The rows in the inserted table are copies of the new rows in the trigger table.|||and I assume they get cleaned out once the insert is complete... including trigger execution...|||rnealejr ,I understand that INSERTED table stores copies of the rows inserted into the actual table...

So does this mean that everytime an insert or update statement is executed a new inserted table is formed?|||I think the table remains but the rows are removed after the action is completed.

The reason I think this is because according to the BOL you can reference the deleted table when doing an insert and the inserted table when doing a delete but there are no rows contained in the tables...

"When you set trigger conditions, use the inserted and deleted tables appropriately for the action that fired the trigger. Although referencing the deleted table while testing an INSERT, or the inserted table while testing a DELETE does not cause any errors, these trigger test tables do not contain any rows in these cases."|||You can have more than 1 record in the inserted table and the table is only accessible to the trigger - so the table exists as long as the trigger runs for a particular sql statement.|||So does this mean that everytime an insert or update statement is executed a new inserted table is formed?

These tables are created/stored in memory. From what I remember, I believe the scope of these virtual tables are for the life of the trigger. It would not make sense that ss would keep a table in memory any longer than needed.|||It's highly possible that they continue to exist after their usefulness has gone, after all we are talking a microsoft product and they have done stranger things in the past.

Also the amount of memory you are talking about is minimal so the effect of keeping the table alive in memory is unlikely to cause any real problems.

In fact, it is likely that the over head involved in creating the tables each time if more detrimental then kepeing them in memory especially when you consider that you are likely to do multiple updates/inserts/deletes on any given table at a time rather then constant swap around tables ...|||The inserted and deleted tables exist only within the scope of the trigger execution. Updates use both the inserted and deleted tables because they effectively insert new modified copies of the records and then delete the old ones.

gayamantra, the inserted table does not exist as a distinct and persistent object. Keep in mind that it has the same record format as whatever datatable was the subject of the operation.

New virtual tables of inserted and deleted records must be created (in memory only) for each operation, otherwise multiple users accessing the datatable would end up with their inserted/deleted records intermingling.

I would guess that there is little additional overhead in creating these virtual tables on the fly, because they may be incidental to the database server's operations anyway.|||"New virtual tables of inserted and deleted records must be created (in memory only) for each operation, otherwise multiple users accessing the datatable would end up with their inserted/deleted records intermingling."

Not necessarily, the tables could be created with a user context eg. they are specific to the user at the time... I don't really know though... lets be honest, there are quite a few methods that MS could be using... but for the nature of this discussion the inserted and deleted table exist for the duration of the insert or delete. :Dsql

Insert/Delete Trigger Misfires

I am having problems with a trigger that is designed to audit changes to a particular field in a table. If that field is updated, then the old record is inserted into an audit table.

This trigger never fails when I run test data against it from Query Analyzer. It works some of the time when the web application updates it, fails other times.

Typically, multiple records are updated at the same time. Any ideas?

Here is the Trigger:

create trigger t_u_product_rate_detail
on product_rate_detail
for insert, update, delete

as

/--Local variable
declare
@.auditdate datetime,
@.audituser sysname

--Set values so function isn't executed a bunch of times
select
@.auditdate = getdate(),
@.audituser = suser_sname()

if exists (select * from inserted)
begin
if exists (select * from deleted)
begin
insert into product_rate_detail_audit_log
select d.product_rate_detail_id,
d.product_rate_id,
d.day_of_week_id,
d.ad_size_id,
d.rate,
d.plan_vol,
d.plan_freq,
@.auditdate, @.audituser, 'U'
from deleted d
join inserted i on i.product_rate_detail_id = d.product_Rate_detail_id
where (d.rate <> 0 and d.rate is not null)
and i.rate <> d.rate -- this determines if the rate has changed.
end
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOLots of stuff wrong with this. Heres some things to consider:
Your trigger is for insert/update/delete, but this criteria:if exists (select * from inserted)
begin
if exists (select * from deleted)
begin...will cause your insert statement to execute only on updates, because that is the only occasion when data exists in both inserted and deleted tables.
You really don't need to check for the existence of data in the inserted and deleted tables anyway. If you reference them as a source of data in a statement and they are empty, then your statement will just not do anything. So drop the "if exists" clauses completely.
If you just want to capture updates and deletes then you need only reference the deleted table. The inserted table contains the new values, and looks like you are not archiving those (until they themselves are updated).
You can also drop the @.AuditDate and @.AuditUser variables, and just reference getdate() and suser_sname() directly in your update statement. suser_sname() is constant throughout the transaction, and unless you have a truly massive update then getdate() will return a consistent value across all affected records as well.
Dropping your exists clauses and your unnecessary variable declarations will simplify your code, and simpler is always better.|||Well, let me bite ...

First, check for existence is always a good idea, simply because attempting to perform an operation on an empty set also has its cost and contributes to resource contention. Besides the trigger will get fired even if 0 rows are updated.

Second, if you all want to use best practices, - do not perform mass updates, so that the trigger does not have to kill the server while processing millions of rows. Also, (and this is truly the best practice point) - read your virtual tables only once, because this opration in itself is extremely expensive. In order to satisfy this requirement, - select * into #tmp from deleted!

Third, - remove references to INSERT and DELETE in the trigger definition. UPDATE occurs only when a record is updated, not when it is deleted or inserted (unless your app performs UPDATE by issueing DELETE+INSERT).

Insert Relative Columns

So I want to insert 40 values into a table, starting at a particular column. Like this:

INSERT INTO MyTable (1) VALUES (...my forty values...)

Pretending that 0 (zero) indexes the first column, and 1 indexes the second column, the purpose is to skip the (first) column that contained an identity value (since normally you can't insert into an identity column anyway).

The only way I currently know how to solve this problem, is to use highly verbose syntax, like this:

INSERT INTO MyTable (...my forty column names...) VALUES (...my forty values...)

But yuck, who wants to explicitly mention all forty column names, ONLY BECAUSE I'm trying to avoid inserting a value into the first column which contains the identity?

It is best practice to name all of the columns, even if you weren't using identities. One of the most heinous problems an application I have had to work with was that they didn't name columns, then replication needed to add a column and boom, all of the code had to be rewritten.

It is easy to write though, just right click the table in SSMS or QA and say script table as insert... This will enerate a script for you that has all of the columns (without the identity column and timestamp if you have one.) It wouldn't be too hard to fashion one out of a query to information_schema.columns also.

If you feel strongly, I would suggest that you go here and submit this feedback: http://connect.microsoft.com/SQLServer/Feedback. It would be nice in ad-hoc usage to not have to name the columns, though