Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Ways to aviod duplicate data

5 posts in General Discussion Last posting was on 2011-12-07 22:23:11.0Z
Kiran Chalapaka Posted on 2011-12-02 14:12:44.0Z
Sender: 2f2f.4ed4d107.1804289383@sybase.com
From: Kiran Chalapaka
Newsgroups: sybase.public.ase.general
Subject: Ways to aviod duplicate data
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ed8dcdc.4c7c.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 2 Dec 2011 06:12:44 -0800
X-Trace: forums-1-dub 1322835164 10.22.241.41 (2 Dec 2011 06:12:44 -0800)
X-Original-Trace: 2 Dec 2011 06:12:44 -0800, 10.22.241.41
Lines: 31
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30734
Article PK: 73624

Hi,

I have a table which has columns id,no,year,compno,status.

In this table,as per the business logic:

There can be no duplicate combination of no,year,compno with
an existing id for status 'Open'. This combination can
occur(i.e duplicate id,no,year,compno
combination) if the status is 'Closed'. So a unique trigger
on no,year,compno will not work.

So I've created an insert trigger, which will check, if the
inserted status is 'Open', whether there are existing
records with the inserted no,year,compno and id not equal to
inserted id.

The trigger is working when I try to insert a row with this
duplicate combination. But when 2 insertions are happening
simultaneously, the trigger is not preventing this duplicate
insertion.

The table has a row level locking schema, which I suspect is
the reason.

Can anyone suggest a work around for this, please let me
know if my description is not clear.

Regards,

Kiran


Rob V Posted on 2011-12-02 17:10:18.0Z
From: Rob V <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:8.0) Gecko/20111105 Thunderbird/8.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Ways to aviod duplicate data
References: <4ed8dcdc.4c7c.1681692777@sybase.com>
In-Reply-To: <4ed8dcdc.4c7c.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ed9067a$1@forums-1-dub>
Date: 2 Dec 2011 09:10:18 -0800
X-Trace: forums-1-dub 1322845818 10.22.241.152 (2 Dec 2011 09:10:18 -0800)
X-Original-Trace: 2 Dec 2011 09:10:18 -0800, vip152.sybase.com
Lines: 54
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30735
Article PK: 73625


On 02-Dec-2011 15:12, Kiran Chalapaka wrote:
> Hi,
>
> I have a table which has columns id,no,year,compno,status.
>
> In this table,as per the business logic:
>
> There can be no duplicate combination of no,year,compno with
> an existing id for status 'Open'. This combination can
> occur(i.e duplicate id,no,year,compno
> combination) if the status is 'Closed'. So a unique trigger
> on no,year,compno will not work.
>
> So I've created an insert trigger, which will check, if the
> inserted status is 'Open', whether there are existing
> records with the inserted no,year,compno and id not equal to
> inserted id.
>
> The trigger is working when I try to insert a row with this
> duplicate combination. But when 2 insertions are happening
> simultaneously, the trigger is not preventing this duplicate
> insertion.
>
> The table has a row level locking schema, which I suspect is
> the reason.
>
> Can anyone suggest a work around for this, please let me
> know if my description is not clear.
>
> Regards,
>
> Kiran

Already replied in sybase.public.ase.administration.
Please do not cross-post.

--
HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE, IQ, Replication Server

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks& Recipes for Sybase ASE"
"The Complete Sybase IQ Quick Reference Guide"
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


Sherlock, Kevin [TeamSybase] Posted on 2011-12-02 17:24:26.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <4ed8dcdc.4c7c.1681692777@sybase.com>
Subject: Re: Ways to aviod duplicate data
Lines: 63
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5512
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ed909ca@forums-1-dub>
Date: 2 Dec 2011 09:24:26 -0800
X-Trace: forums-1-dub 1322846666 10.22.241.152 (2 Dec 2011 09:24:26 -0800)
X-Original-Trace: 2 Dec 2011 09:24:26 -0800, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30739
Article PK: 73631

Well, a trigger would be the preferred approach I guess. So, you should
concentrate on getting that to work.

I can however think of other ways (either less desirable, or perhaps more
convoluted) to approach this with either a single table, or multiple tables.

1. Normalize. create two separate tables, one for closed records, one for
open records. Create unique index on open records table on no,year,compno.
create view which brings both closed records together with open records
labeling a "status" column depending on what table the records come from.
Create an "instead of trigger" for inserts/updates/deletes on the view to
process the correct tables.

2. create a function based column which references the status of the
record. For open records, generate a constant value for this column, for
non-open records have the function generate a guid or hash value for the
record. Create a unique index with the function based column included with
your no,year,compno columns.

3. Or, if datetime precision is enough to avoid duplicates (ie, closed
records don't happen within 1/300 of a second of each other [danger here]),
add a "closed_datetime" column to the record which is null for open records,
and populated (by trigger, function?) on closed records. Include this
column in your unique index spec. If datetime isn't precise enough, again,
use a guid datatype combined with trigger/function based column to generate
this "closed time" column which is always null or some other constant for
"open" records (but this is essentially my #2 suggestion above).

<Kiran Chalapaka> wrote in message
news:4ed8dcdc.4c7c.1681692777@sybase.com...
> Hi,
>
> I have a table which has columns id,no,year,compno,status.
>
> In this table,as per the business logic:
>
> There can be no duplicate combination of no,year,compno with
> an existing id for status 'Open'. This combination can
> occur(i.e duplicate id,no,year,compno
> combination) if the status is 'Closed'. So a unique trigger
> on no,year,compno will not work.
>
> So I've created an insert trigger, which will check, if the
> inserted status is 'Open', whether there are existing
> records with the inserted no,year,compno and id not equal to
> inserted id.
>
> The trigger is working when I try to insert a row with this
> duplicate combination. But when 2 insertions are happening
> simultaneously, the trigger is not preventing this duplicate
> insertion.
>
> The table has a row level locking schema, which I suspect is
> the reason.
>
> Can anyone suggest a work around for this, please let me
> know if my description is not clear.
>
> Regards,
>
> Kiran


SybaseGuru Posted on 2011-12-07 21:57:02.0Z
Sender: 5e0a.4edfcbef.1804289383@sybase.com
From: SybaseGuru
Newsgroups: sybase.public.ase.general
Subject: Re: Ways to aviod duplicate data
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4edfe12e.647f.1681692777@sybase.com>
References: <4ed8dcdc.4c7c.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 7 Dec 2011 13:57:02 -0800
X-Trace: forums-1-dub 1323295022 10.22.241.41 (7 Dec 2011 13:57:02 -0800)
X-Original-Trace: 7 Dec 2011 13:57:02 -0800, 10.22.241.41
Lines: 60
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30746
Article PK: 73636


> Hi,
>
> I have a table which has columns id,no,year,compno,status.
>
> In this table,as per the business logic:
>
> There can be no duplicate combination of no,year,compno
> with an existing id for status 'Open'. This combination
> can occur(i.e duplicate id,no,year,compno
> combination) if the status is 'Closed'. So a unique
> trigger on no,year,compno will not work.
>
> So I've created an insert trigger, which will check, if
> the inserted status is 'Open', whether there are existing
> records with the inserted no,year,compno and id not equal
> to inserted id.
>
> The trigger is working when I try to insert a row with
> this duplicate combination. But when 2 insertions are
> happening simultaneously, the trigger is not preventing
> this duplicate insertion.
>
> The table has a row level locking schema, which I suspect
> is the reason.
>
> Can anyone suggest a work around for this, please let me
> know if my description is not clear.
>
> Regards,
>
> Kiran

My below code never worked for insert, let go the
simultaneous inserts.

create table abc( id int, no int, year int, compno int,
status varchar(10))
go
create trigger i_abc on abc for insert as
if exists (
select 1
from abc a,
inserted b
where a.id = b.id
and a.no = b.no
and a.year = b.year
and a.status = 'OPEN'
and a.status = b.status
)
begin
rollback trigger
end

go
insert into abc select 1,1,1,1,'OPEN'
go

The insert statement inserts 0 rows. @@error is 0. No raised
exception. In trigger I cant query the base table the way i
did?


"Mark A. Parsons" <iron_horse Posted on 2011-12-07 22:23:11.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Ways to aviod duplicate data
References: <4ed8dcdc.4c7c.1681692777@sybase.com> <4edfe12e.647f.1681692777@sybase.com>
In-Reply-To: <4edfe12e.647f.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4edfe74f$1@forums-1-dub>
Date: 7 Dec 2011 14:23:11 -0800
X-Trace: forums-1-dub 1323296591 10.22.241.152 (7 Dec 2011 14:23:11 -0800)
X-Original-Trace: 7 Dec 2011 14:23:11 -0800, vip152.sybase.com
Lines: 88
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30747
Article PK: 73635

A normal ASE trigger fires *after* the base table has been modified.

In the case of an INSERT/UPDATE the contents of the 'inserted' table already resides in the base table.

In the case of a DELETE the contents of the 'deleted' table no longer exists in the base table.

In your case when the trigger fires the contents of the 'inserted' table is already in the 'abc' table, so the
conditional join succeeds (ie, there's a matching join between 'inserted' and 'abc') so the trigger is rolled back,
which in turn rolls back the INSERT that caused the trigger to fire, which means your row does not exist in the 'abc'
once all the processing has completed.

-------

Possible solutions:

- rewrite the trigger's conditional to look for a "count(*) > 1 where status = 'OPEN'"

or

- rewrite the trigger's conditional to look for some other attribute of the row that would be different for 2 records
that have "status = 'OPEN'"; you'll probably need to modify the table to add another column that provides for a more
granular search, eg, an insert date (accurate to ~3 milliseconds) possibly combined with the value of suser_name(); keep
in mind that even this workaround won't be sufficient to address your issue if you could have 2 processes with the same
login performing the same exact insert within ~3 milliseconds of each other

NOTE: With ASE 15.5 it's now possible to use the bigdatetime datatype to keep track of time down to the microsecond.

On 12/07/2011 16:57, SybaseGuru wrote:
>> Hi,
>>
>> I have a table which has columns id,no,year,compno,status.
>>
>> In this table,as per the business logic:
>>
>> There can be no duplicate combination of no,year,compno
>> with an existing id for status 'Open'. This combination
>> can occur(i.e duplicate id,no,year,compno
>> combination) if the status is 'Closed'. So a unique
>> trigger on no,year,compno will not work.
>>
>> So I've created an insert trigger, which will check, if
>> the inserted status is 'Open', whether there are existing
>> records with the inserted no,year,compno and id not equal
>> to inserted id.
>>
>> The trigger is working when I try to insert a row with
>> this duplicate combination. But when 2 insertions are
>> happening simultaneously, the trigger is not preventing
>> this duplicate insertion.
>>
>> The table has a row level locking schema, which I suspect
>> is the reason.
>>
>> Can anyone suggest a work around for this, please let me
>> know if my description is not clear.
>>
>> Regards,
>>
>> Kiran
>
> My below code never worked for insert, let go the
> simultaneous inserts.
>
> create table abc( id int, no int, year int, compno int,
> status varchar(10))
> go
> create trigger i_abc on abc for insert as
> if exists (
> select 1
> from abc a,
> inserted b
> where a.id = b.id
> and a.no = b.no
> and a.year = b.year
> and a.status = 'OPEN'
> and a.status = b.status
> )
> begin
> rollback trigger
> end
>
> go
> insert into abc select 1,1,1,1,'OPEN'
> go
>
> The insert statement inserts 0 rows. @@error is 0. No raised
> exception. In trigger I cant query the base table the way i
> did?