[trigger] old data persisting in inserted table

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

[trigger] old data persisting in inserted table

Author

Topic

lordzoster
Starting Member

29 Posts

Posted - 2009-01-30 : 08:11:14

Hallo
i'm running an AFTER INSERT trigger upon a table, with two subsequent SELECT FROM INSERTED in it:

1.first it populates several variables with values from [inserted],like this:
SELECT @variable1..n =Inserted.Fields1..n
FROM Inserted

2.then it performs some checks and manipulations upon the variables (including select values from other tables)

3.it runs a statement like this:
INSERT INTO [newtable]
SELECT (@variable1..n), Inserted.DATA
FROM Inserted

The problem is that in the first SELECT FROM INSERTED i'm always getting values from a previously inserted record, so i'm getting always the same values.
The second SELECT works fine (apart from the variables values, that have been fetched from the first SELECT).

The same trigger is working perfectly into a test environment (specular to the production one).
Is there any issue into fetching from INSERTED twice into a trigger?

Before redesigning the trigger with a single SELECT (it is actually possible, but it would require a variable for every field to be transcribed in the other table) i would like to know if anybody can point me to solve this mistery.
Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-30 : 08:41:38

1. Show us the trigger code
2. Describe the problem you have (check)
3. Tell us what you want to be performed.

E 12°55'05.63"
N 56°04'39.26"

lordzoster
Starting Member

29 Posts

Posted - 2009-01-30 : 09:57:30

quote:
Originally posted by Peso

1. Show us the trigger code
2. Describe the problem you have (check)
3. Tell us what you want to be performed.

1.
TRIGGER AFTER INSERT
AS
DECLARE @var1,@var2, @var3
SELECT @var1 = field1, @var2 = field2
FROM inserted

--do some check upon @var1, @var2
--results is a value of @var3

INSERT INTO [othertable]
SELECT @var1, @var2, @var3, field4,field6,field32
FROM inserted

2. the problem
@var1, @var2,@var3 contain values from a record different that the one being currently inserted. In particular, for every new record, the values are taken all from the same record, that has been inserted several weeks ago, has never been edited since that, and has no particular meaning nor reference to the new records being inserted. Eg.:
SELECT @var1 = field1, @var2 = field2
FROM inserted
--these are the results
@var1 = "Jenna"
@var2 = "Jameson"
--but actual "inserted" record contains
fields1 = "John"
fields2 = "Lennon"

3. what i want
i'd like to have "SELECT from INSERTED" working as should.
And it actually does, but in the testing server only: the only and one difference is that since now, in the testing server we never had more than one user at time; in production there are multiple active users (but none touching THAT record being fetched by the first SELECT statement)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-31 : 13:29:05

firstly your main problem is that you're assuming that inserted will contain a single record always which is not the case, so your first step of assigning variables with values will not work fine. in case of multiple records, it stores only one set of values. so what you need is table variable to store all the records.


TRIGGER AFTER INSERT
AS
DECLARE @temp_table
(
var1 datatype,
var2 datatype, 
var3 datatype
)
INSERT INTO @temp_table
SELECT field1,field2,...
FROM inserted


INSERT INTO [othertable]
SELECT field1, field2, 
CASE WHEN...  AS field3,--do the checks here 
field4,field6,field32
FROM inserted

lordzoster
Starting Member

29 Posts

Posted - 2009-02-01 : 14:04:16

Each transaction *actually* contains only a single record.
And this doesn't explain why the first SELECT FROM inserted returns always the same record, actually inserted weeks ago.
BTW i'm considering your model: it's more engineered but less mainteinable than the previous (managing a CASE that performs a SELECT to other table can be very CPU intensive; using some LEFT JOINs with the other tables where to check for the existance of the value can lead to quite large queries difficult to debug).
Anyway, thanks, i'm gonna test that.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-01 : 23:24:24

consider the case where first select returns you more than one row. based on order in which the records are retrieved (which cannot be guaranteed unless you use order by), the variables will always contain values of last record alone.
Thus, use of variables in last select will not always be giving values associated to record you're retrieveing there (as variables all contain only last record value)
re. your last point,are you performing some complex checks using CASE?

lordzoster
Starting Member

29 Posts

Posted - 2009-02-02 : 03:18:50

these are the checks after the first select:
1. SELECT from a reference table where values are equal to @vars selected from INSERTED.
2.Does the recordset contains more than one row? if so, insert them in an audit table and exit the procedure with nothing else.
3. if the @var1 is not null (one record returned) transform @var2 and @var3 with some REPLACE and SUBSTRING, compare them with values in a reference table and write them in the audit table in case they are different
4. else (no record returned), transform (REPLACE+SUBSTRING) and transcribe them in the reference table

Eventually after those checks, the trigger performs the

SELECT @vars, field1..n
INTO publishing_table
FROM INSERTED

I don't think that within a CASE I could handle a @@ROWCOUNT selfrelated to the query being executed; nor within a CASE I could handle a SELECT, an UPDATE, or an INSERT.

lordzoster
Starting Member

29 Posts

Posted - 2009-02-02 : 08:42:34

I found out that the issue resides in the SELECT from the reference table. In some way the WHERE doesn't filter records and the select returns 60K records.
I'll go deep further into that.
Thanks for the precious hint! :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-02 : 09:00:00

no problem

you're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources