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.

How to prevent the trigger issue in MSA?

7 posts in General Discussion Last posting was on 2011-11-09 07:19:26.0Z
Eisen Posted on 2011-11-07 13:19:47.0Z
Sender: 36ab.4eb7d7a2.1804289383@sybase.com
From: Eisen
Newsgroups: sybase.public.rep-server
Subject: How to prevent the trigger issue in MSA?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4eb7daf3.3739.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 7 Nov 2011 05:19:47 -0800
X-Trace: forums-1-dub 1320671987 10.22.241.41 (7 Nov 2011 05:19:47 -0800)
X-Original-Trace: 7 Nov 2011 05:19:47 -0800, 10.22.241.41
Lines: 35
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.rep-server:9081
Article PK: 870243

Dear sirs
Currently, I've set up a MSA system from A.a => B.b
successfully. But soon I found sth. is wrong within it. --
When triggered the insert trigger on source table to insert
history data into another table, the DSI to B.b would be
shutdown by duplicated key error.
eg. on both A.a and B.b there are two tables --
create table test_wj1(id int)
create table test_wj2(id int)
alter table test_wj1 add constraint pk_test_wj1 primary
key(id)
alter table test_wj2 add constraint pk_test_wj2 primary
key(id)
go
and a trigger on test_wj1 for insert --
create trigger tr_test_wj1 on test_wj1 for insert
as
begin
insert test_wj2 select * from inserted
end
go

So once execute "insert test_wj1 values..." the DSI to B.b
would shutdown immediately by duplicate key error. I think
it's due to RS would replicate two transactions to B.b --
Tran 1 is on test_wj1 and would trigger it on B.b so the
B.b..test_wj2 already had that value, but then Tran2 on
test_wj2 is replicated to B.b too. Then it would make
duplicated key error. Am I right?

Is there any configuration to prevent it? Thanks in advance
for any help or suggestions.

Best Regards
Eisen


Sukhesh Posted on 2011-11-07 14:30:45.0Z
From: "Sukhesh" <sukheshnair@yahoo.com.sg>
Newsgroups: sybase.public.rep-server
References: <4eb7daf3.3739.1681692777@sybase.com>
Subject: Re: How to prevent the trigger issue in MSA?
Lines: 48
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3664
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3664
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4eb7eb95$1@forums-1-dub>
Date: 7 Nov 2011 06:30:45 -0800
X-Trace: forums-1-dub 1320676245 10.22.241.152 (7 Nov 2011 06:30:45 -0800)
X-Original-Trace: 7 Nov 2011 06:30:45 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.rep-server:9082
Article PK: 870245

Hi,

You can turn off triggers for the connections by turning off the
"dsi_keep_triggers" option of alter connection.
By default, triggers are off for a warmstandby ... I think for MSA, you may
have to turn it off manually.

HTH
Regards
Sukhesh

<Eisen> wrote in message news:4eb7daf3.3739.1681692777@sybase.com...
> Dear sirs
> Currently, I've set up a MSA system from A.a => B.b
> successfully. But soon I found sth. is wrong within it. --
> When triggered the insert trigger on source table to insert
> history data into another table, the DSI to B.b would be
> shutdown by duplicated key error.
> eg. on both A.a and B.b there are two tables --
> create table test_wj1(id int)
> create table test_wj2(id int)
> alter table test_wj1 add constraint pk_test_wj1 primary
> key(id)
> alter table test_wj2 add constraint pk_test_wj2 primary
> key(id)
> go
> and a trigger on test_wj1 for insert --
> create trigger tr_test_wj1 on test_wj1 for insert
> as
> begin
> insert test_wj2 select * from inserted
> end
> go
>
> So once execute "insert test_wj1 values..." the DSI to B.b
> would shutdown immediately by duplicate key error. I think
> it's due to RS would replicate two transactions to B.b --
> Tran 1 is on test_wj1 and would trigger it on B.b so the
> B.b..test_wj2 already had that value, but then Tran2 on
> test_wj2 is replicated to B.b too. Then it would make
> duplicated key error. Am I right?
>
> Is there any configuration to prevent it? Thanks in advance
> for any help or suggestions.
>
> Best Regards
> Eisen


"Mark A. Parsons" <iron_horse Posted on 2011-11-08 13:00:43.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.rep-server
Subject: Re: How to prevent the trigger issue in MSA?
References: <4eb7eb95$1@forums-1-dub> <4eb88f06.5913.1681692777@sybase.com>
In-Reply-To: <4eb88f06.5913.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: <4eb927fb@forums-1-dub>
Date: 8 Nov 2011 05:00:43 -0800
X-Trace: forums-1-dub 1320757243 10.22.241.152 (8 Nov 2011 05:00:43 -0800)
X-Original-Trace: 8 Nov 2011 05:00:43 -0800, vip152.sybase.com
Lines: 76
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.rep-server:9085
Article PK: 870251

dsi_keep_triggers is used to tell the repserver how to (re)configure a DSI connection once it has connected to the ASE.

dsi_keep_triggers is a repserver-specific setting that affects only the DSI connection(s) to which it's attached.

When the DSI connects to the ASE it will issue the appropriate 'set triggers on|off' command as dictated by the
dsi_keep_triggers setting.

NOTE: 'set triggers on|off' is an ASE command which is applied at the individual connection level, ie, each connection
has its own triggers on|off setting. 'set triggers on|off' will enable|disable the firing of triggers for the connection.

Other connections into the ASE (eg, a manual connection) will *NOT* be affected by the dsi_keep_triggers setting that's
been assigned to the DSI connection. Remember, each connection into the ASE, whether it be manual or DSI, has its own
trigger setting.

NOTE: By default a new connection into ASE has triggers enabled. It is possible for any connection (DSI or otherwise)
with the right permissions to issue 'set triggers on|off' thus enabling|disabling triggers for said connection. The
issuance of the 'set triggers' command could be automated through a login trigger or the connection logic of the
front-end application thus overriding the default behavior of having triggers enabled.

On 11/07/2011 21:08, Eisen wrote:
> Hi Sukhesh
> Thanks a lot. But I still has a question -- would this
> trigger be triggered if there's manual operation on the
> replicate site? Thanks
>
> Best Regards
> Eisen
>> Hi,
>>
>> You can turn off triggers for the connections by turning
>> off the "dsi_keep_triggers" option of alter connection.
>> By default, triggers are off for a warmstandby ... I think
>> for MSA, you may have to turn it off manually.
>>
>> HTH
>> Regards
>> Sukhesh
>> <Eisen> wrote in message
>>> news:4eb7daf3.3739.1681692777@sybase.com... Dear sirs
>>> Currently, I've set up a MSA system from A.a => B.b
>>> successfully. But soon I found sth. is wrong within it.
>>> -- When triggered the insert trigger on source table to
>>> insert history data into another table, the DSI to B.b
>>> would be shutdown by duplicated key error.
>>> eg. on both A.a and B.b there are two tables --
>>> create table test_wj1(id int)
>>> create table test_wj2(id int)
>>> alter table test_wj1 add constraint pk_test_wj1 primary
>>> key(id)
>>> alter table test_wj2 add constraint pk_test_wj2 primary
>>> key(id)
>>> go
>>> and a trigger on test_wj1 for insert --
>>> create trigger tr_test_wj1 on test_wj1 for insert
>>> as
>>> begin
>>> insert test_wj2 select * from inserted
>>> end
>>> go
>>>
>>> So once execute "insert test_wj1 values..." the DSI to
>>> B.b would shutdown immediately by duplicate key error. I
>>> think it's due to RS would replicate two transactions to
>>> B.b -- Tran 1 is on test_wj1 and would trigger it on B.b
>>> so the B.b..test_wj2 already had that value, but then
>>> Tran2 on test_wj2 is replicated to B.b too. Then it
>>> would make duplicated key error. Am I right?
>>>
>>> Is there any configuration to prevent it? Thanks in
>>> advance for any help or suggestions.
>>>
>>> Best Regards
>>> Eisen
>>
>>


Manish Negandhi [TeamSybase] Posted on 2011-11-08 13:05:45.0Z
From: "Manish Negandhi [TeamSybase]" <nospam_negandhi.manish@gmail.com>
Newsgroups: sybase.public.rep-server
References: <4eb7eb95$1@forums-1-dub> <4eb88f06.5913.1681692777@sybase.com>
Subject: Re: How to prevent the trigger issue in MSA?
Lines: 72
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3664
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3664
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4eb92929@forums-1-dub>
Date: 8 Nov 2011 05:05:45 -0800
X-Trace: forums-1-dub 1320757545 10.22.241.152 (8 Nov 2011 05:05:45 -0800)
X-Original-Trace: 8 Nov 2011 05:05:45 -0800, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.rep-server:9086
Article PK: 870255

I think replication server uses rs_triggers_reset function when you make
use of "dsi_keep_triggers" for replicated databases. However that will not
hold true when you do anything at replicated site manually hence trigger
will fire for manual operations , you can do a dummy transation manually and
check it.

-HTH
Manish Negandhi
[TeamSybase]

<Eisen> wrote in message news:4eb88f06.5913.1681692777@sybase.com...
> Hi Sukhesh
> Thanks a lot. But I still has a question -- would this
> trigger be triggered if there's manual operation on the
> replicate site? Thanks
>
> Best Regards
> Eisen
>> Hi,
>>
>> You can turn off triggers for the connections by turning
>> off the "dsi_keep_triggers" option of alter connection.
>> By default, triggers are off for a warmstandby ... I think
>> for MSA, you may have to turn it off manually.
>>
>> HTH
>> Regards
>> Sukhesh
>> <Eisen> wrote in message
>> > news:4eb7daf3.3739.1681692777@sybase.com... Dear sirs
>> > Currently, I've set up a MSA system from A.a => B.b
>> > successfully. But soon I found sth. is wrong within it.
>> > -- When triggered the insert trigger on source table to
>> > insert history data into another table, the DSI to B.b
>> > would be shutdown by duplicated key error.
>> > eg. on both A.a and B.b there are two tables --
>> > create table test_wj1(id int)
>> > create table test_wj2(id int)
>> > alter table test_wj1 add constraint pk_test_wj1 primary
>> > key(id)
>> > alter table test_wj2 add constraint pk_test_wj2 primary
>> > key(id)
>> > go
>> > and a trigger on test_wj1 for insert --
>> > create trigger tr_test_wj1 on test_wj1 for insert
>> > as
>> > begin
>> > insert test_wj2 select * from inserted
>> > end
>> > go
>> >
>> > So once execute "insert test_wj1 values..." the DSI to
>> > B.b would shutdown immediately by duplicate key error. I
>> > think it's due to RS would replicate two transactions to
>> > B.b -- Tran 1 is on test_wj1 and would trigger it on B.b
>> > so the B.b..test_wj2 already had that value, but then
>> > Tran2 on test_wj2 is replicated to B.b too. Then it
>> > would make duplicated key error. Am I right?
>> >
>> > Is there any configuration to prevent it? Thanks in
>> > advance for any help or suggestions.
>> >
>> > Best Regards
>> > Eisen
>>
>>


Sukhesh Posted on 2011-11-08 17:21:31.0Z
From: "Sukhesh" <sukheshnair@yahoo.com.sg>
Newsgroups: sybase.public.rep-server
References: <4eb7eb95$1@forums-1-dub> <4eb88f06.5913.1681692777@sybase.com> <4eb92929@forums-1-dub>
Subject: Re: How to prevent the trigger issue in MSA?
Lines: 90
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3664
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3664
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4eb9651b@forums-1-dub>
Date: 8 Nov 2011 09:21:31 -0800
X-Trace: forums-1-dub 1320772891 10.22.241.152 (8 Nov 2011 09:21:31 -0800)
X-Original-Trace: 8 Nov 2011 09:21:31 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.rep-server:9087
Article PK: 870247

As always, Mark has documented the functioning of the dsi_keep_triggers very
well in his post. Thanks Mark.
There is one thing you need to keep in mind though. Supposing you have
created the database replication definition mentioning a subset of tables
instead of all the tables -- and if table A in the list which has a trigger
that updates table B and table B is not in the list ...then in this case,
the trigger in the replicate side will not get fired. This may cause
inconsistencies which the DBA may not be able to figure out.
So, you need to work closely with the application team to figure out what
exactly will need to be replicated.

Cheers
Sukhesh

"Manish Negandhi [TeamSybase]" <nospam_negandhi.manish@gmail.com> wrote in
message news:4eb92929@forums-1-dub...
>I think replication server uses rs_triggers_reset function when you make
>use of "dsi_keep_triggers" for replicated databases. However that will not
>hold true when you do anything at replicated site manually hence trigger
>will fire for manual operations , you can do a dummy transation manually
>and check it.
>
> -HTH
> Manish Negandhi
> [TeamSybase]
>
>
>
>
>
> <Eisen> wrote in message news:4eb88f06.5913.1681692777@sybase.com...
>> Hi Sukhesh
>> Thanks a lot. But I still has a question -- would this
>> trigger be triggered if there's manual operation on the
>> replicate site? Thanks
>>
>> Best Regards
>> Eisen
>>> Hi,
>>>
>>> You can turn off triggers for the connections by turning
>>> off the "dsi_keep_triggers" option of alter connection.
>>> By default, triggers are off for a warmstandby ... I think
>>> for MSA, you may have to turn it off manually.
>>>
>>> HTH
>>> Regards
>>> Sukhesh
>>> <Eisen> wrote in message
>>> > news:4eb7daf3.3739.1681692777@sybase.com... Dear sirs
>>> > Currently, I've set up a MSA system from A.a => B.b
>>> > successfully. But soon I found sth. is wrong within it.
>>> > -- When triggered the insert trigger on source table to
>>> > insert history data into another table, the DSI to B.b
>>> > would be shutdown by duplicated key error.
>>> > eg. on both A.a and B.b there are two tables --
>>> > create table test_wj1(id int)
>>> > create table test_wj2(id int)
>>> > alter table test_wj1 add constraint pk_test_wj1 primary
>>> > key(id)
>>> > alter table test_wj2 add constraint pk_test_wj2 primary
>>> > key(id)
>>> > go
>>> > and a trigger on test_wj1 for insert --
>>> > create trigger tr_test_wj1 on test_wj1 for insert
>>> > as
>>> > begin
>>> > insert test_wj2 select * from inserted
>>> > end
>>> > go
>>> >
>>> > So once execute "insert test_wj1 values..." the DSI to
>>> > B.b would shutdown immediately by duplicate key error. I
>>> > think it's due to RS would replicate two transactions to
>>> > B.b -- Tran 1 is on test_wj1 and would trigger it on B.b
>>> > so the B.b..test_wj2 already had that value, but then
>>> > Tran2 on test_wj2 is replicated to B.b too. Then it
>>> > would make duplicated key error. Am I right?
>>> >
>>> > Is there any configuration to prevent it? Thanks in
>>> > advance for any help or suggestions.
>>> >
>>> > Best Regards
>>> > Eisen
>>>
>>>
>
>


Eisen Posted on 2011-11-09 07:19:26.0Z
Sender: 5835.4eb88a01.1804289383@sybase.com
From: Eisen
Newsgroups: sybase.public.rep-server
Subject: Re: How to prevent the trigger issue in MSA?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4eba297e.1c8f.1681692777@sybase.com>
References: <4eb9651b@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 8 Nov 2011 23:19:26 -0800
X-Trace: forums-1-dub 1320823166 10.22.241.41 (8 Nov 2011 23:19:26 -0800)
X-Original-Trace: 8 Nov 2011 23:19:26 -0800, 10.22.241.41
Lines: 99
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.rep-server:9088
Article PK: 870249

Thanks a lot for all. :))

Best Regards
Eisen

> As always, Mark has documented the functioning of the
> dsi_keep_triggers very well in his post. Thanks Mark.
> There is one thing you need to keep in mind though.
> Supposing you have created the database replication
> definition mentioning a subset of tables instead of all
> the tables -- and if table A in the list which has a
> trigger that updates table B and table B is not in the
> list ...then in this case, the trigger in the replicate
> side will not get fired. This may cause inconsistencies
> which the DBA may not be able to figure out. So, you need
> to work closely with the application team to figure out
> what exactly will need to be replicated.
>
> Cheers
> Sukhesh
>
> "Manish Negandhi [TeamSybase]"
> <nospam_negandhi.manish@gmail.com> wrote in message
> news:4eb92929@forums-1-dub... >I think replication server
> uses rs_triggers_reset function when you make >use of
> "dsi_keep_triggers" for replicated databases. However
> that will not >hold true when you do anything at
> replicated site manually hence trigger >will fire for
> manual operations , you can do a dummy transation manually
> >and check it.
> >
> > -HTH
> > Manish Negandhi
> > [TeamSybase]
> >
> >
> >
> >
> >
> > <Eisen> wrote in message
> news:4eb88f06.5913.1681692777@sybase.com... >> Hi Sukhesh
> >> Thanks a lot. But I still has a question -- would
> this >> trigger be triggered if there's manual operation
> on the >> replicate site? Thanks
> >>
> >> Best Regards
> >> Eisen
> >>> Hi,
> >>>
> >>> You can turn off triggers for the connections by
> turning >>> off the "dsi_keep_triggers" option of alter
> connection. >>> By default, triggers are off for a
> warmstandby ... I think >>> for MSA, you may have to turn
> it off manually. >>>
> >>> HTH
> >>> Regards
> >>> Sukhesh
> >>> <Eisen> wrote in message
> >>> > news:4eb7daf3.3739.1681692777@sybase.com... Dear
> sirs >>> > Currently, I've set up a MSA system from A.a
> => B.b >>> > successfully. But soon I found sth. is wrong
> within it. >>> > -- When triggered the insert trigger on
> source table to >>> > insert history data into another
> table, the DSI to B.b >>> > would be shutdown by
> duplicated key error. >>> > eg. on both A.a and B.b there
> are two tables -- >>> > create table test_wj1(id int)
> >>> > create table test_wj2(id int)
> >>> > alter table test_wj1 add constraint pk_test_wj1
> primary >>> > key(id)
> >>> > alter table test_wj2 add constraint pk_test_wj2
> primary >>> > key(id)
> >>> > go
> >>> > and a trigger on test_wj1 for insert --
> >>> > create trigger tr_test_wj1 on test_wj1 for insert
> >>> > as
> >>> > begin
> >>> > insert test_wj2 select * from inserted
> >>> > end
> >>> > go
> >>> >
> >>> > So once execute "insert test_wj1 values..." the DSI
> to >>> > B.b would shutdown immediately by duplicate key
> error. I >>> > think it's due to RS would replicate two
> transactions to >>> > B.b -- Tran 1 is on test_wj1 and
> would trigger it on B.b >>> > so the B.b..test_wj2 already
> had that value, but then >>> > Tran2 on test_wj2 is
> replicated to B.b too. Then it >>> > would make duplicated
> key error. Am I right? >>> >
> >>> > Is there any configuration to prevent it? Thanks in
> >>> > advance for any help or suggestions.
> >>> >
> >>> > Best Regards
> >>> > Eisen
> >>>
> >>>
> >
> >
>
>