serious issue with sybase bug? please advise URGENT

trigger

create trigger tableoneTrgUpd
on Tableone
for update
as
insert into tableoneLog
select inserted.*, "UPDATE" from inserted
-----------------------------------------------------
sql

update tableone set columnname = 'somevalue' where Id in (select distinct Id from sometable e where e.Id = 1234 and e.Flag = 'N') and Status = 'somestatus'
--------------------------------------------------------------
I ran this sql a couple times, the trigger puts COMPLETELY junk into my log table:
for example:
486555904      -21877      llenged      1852073330      [NULL]      &à@[•Œ1N´$ins&à€~¬´klK­tK­w•Œ1O ÿ>§.TB÷Ü¡$ùLÉF„Ï”é+!quickstats                    &à •Œ1N´&àP\t      T            9/13/0000 4:12:05.333 PM       @      12/16/0000 7:03:07.626 PM      UPDATE
-----------------------------------------------------------------
when i do sql

update tableone set columnname = 'somevalue' where Id in (5432) and Status = 'somestatus'

this works perfectly. Note those two sql does the exact same thing. Why? please help
-----------------------------------------------------------------

to summarize:

update tableone set columnname = 'somevalue' where Id in (select distinct Id from sometable e where e.Id = 1234 and e.Flag = 'N') and Status = 'somestatus'

and

update tableone set columnname = 'somevalue' where Id in (5432) and Status = 'somestatus'

is exactly the same, as in (select distinct Id from sometable e where e.Id = 1234 and e.Flag = 'N') returns 5432,

yet if i just use 5432 it works, but if i use an inner select it completely messes up my log table and possibly cause a db crash.

this is very urgent and we are all out of ideas, any expert please help!
LVL 1
gagaliyaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ChrisKingCommented:
OK, not sure how literally to take your example, but ...

select distinct Id from sometable e where e.Id = 1234 and e.Flag = 'N'
can only return the id 1234, so I would run the following

select * from tableone where id = 1234
I would be sure that the curruption is in that data and is not being caused by your trigger
0
gagaliyaAuthor Commented:
chris can you explain more, i dont understand.

update tableone set columnname = 'somevalue' where Id in (select distinct Id from sometable e where e.Id = 1234 and e.Flag = 'N') and Status = 'somestatus'

this causes the trigger on tableon to corrupt the data in its log table.

update tableone set columnname = 'somevalue' where Id in (5432) and Status = 'somestatus'

this works fine.

5432 is just some id i made up, in reality we have to use update tableone set columnname = 'somevalue' where Id in (select distinct Id from sometable e where e.Id = 1234 and e.Flag = 'N') and Status = 'somestatus' because the id is dynamic and not static 5432
0
bretCommented:
What Sybase product, version, and platform are you using?
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

ChrisKingCommented:
what I am saying is: if you select distinct id's and your where clause states id = 1234, then the select distinct can only return a value of 1234.

there for the corruption would appear to be in the data row with the id of 1234, and is not being caused by the trigger.

so, run the following:
select * from tableone where id = 1234
and look for the corruption

select * from tableone where id = 5432
will not be corrupted

I know the table names and id's are not real, just substitute those for the real values and have a look
0
gagaliyaAuthor Commented:
bret i am using sybase 12.5

chrisking, we did further analysis and determined the issue must be from the trigger inserted temp table.  Our tables are not corrupted, the database crashes only when trigger try to put the value into the logtable from inserted.  Basically if you do printout of select * from inserted within the trigger you will see corrupted data. Then when the trigger attempts to insert those corrupted data into our log table, it crashes the db.
0
gagaliyaAuthor Commented:
sorry bret, i mean ase 12.5 on linux/unix (not sure exactly which)
0
ChrisKingCommented:
to get the exact version.       SELECT @@version
and post the output
0
bretCommented:
The database crashes?  Does anything appear in the errorlog when that happens?
0
gagaliyaAuthor Commented:
chris, we tried on a bunch versions. 12.5.0.3, 12.5.2, etc   12.5.2 doesnt crash and just puts the corrupted data into the table. 12.5.03 crashes.

bret, yes the ENTIRE ase server crashed and it was production. Not just a single database. Stacktrace dba provided proves to be no help at all. We pinpointed the problem to be what i stated above. trigger insert into log table, fired from an update containing an inner select from a view(maybe a table too, not sure).

create trigger tableoneTrgUpd
on Tableone
for update
as
insert into tableoneLog
select inserted.*, "UPDATE" from inserted --this produces corrupted data

0
bretCommented:
The stacktrace might mean something to me, please feel free to post it.

Can you provide the DDL for Tableone, a row of data, and an update statement that (with the trigger in place) will cause the problem?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ChrisKingCommented:
when you post the DDL for "Tableone", please inclide the DDL for "tableoneLog" too
0
gagaliyaAuthor Commented:
thanks guys sorry i cannot provide the stacktrace, we are going to try resolve this through offical support for now
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.