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.

Table Triggers

11 posts in Replication Last posting was on 2007-08-16 19:30:49.0Z
Kim Jensen Posted on 2007-07-13 17:53:11.0Z
Reply-To: "Kim Jensen" <kim@comcasystems.com>
From: "Kim Jensen" <kim@comcasystems.com>
Newsgroups: advantage.Replication
Subject: Table Triggers
Date: Fri, 13 Jul 2007 13:53:11 -0400
Lines: 17
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3138
NNTP-Posting-Host: 72.64.222.190
Message-ID: <4697baec@solutions.advantagedatabase.com>
X-Trace: 13 Jul 2007 11:48:28 -0700, 72.64.222.190
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!72.64.222.190
Xref: solutions.advantagedatabase.com Advantage.Replication:206
Article PK: 1134064

It's my first time creating triggers and I'm little confused what which
trigger to use in this scenario.

A new customer is added to the local servers table primary key is the phone
number. This record is replicated to a AIS that already have the customer on
file because had use a different store before. The replication comes as an
INSERT but it Now have to be change to an EDIT or DO NOTHING.

I understand I have to do a Before trigger, but is it enough to just check
"Use implicit transactions to maintain data integrity" or do I need to write
some SQL if so what would I write?

Any help,
Thanks
Kim


Joachim Duerr (ADS) Posted on 2007-07-16 09:40:16.0Z
From: "Joachim Duerr (ADS)" <jojo.duerr@gmx.de>
Subject: Re: Table Triggers
Newsgroups: Advantage.Replication
References: <4697baec@solutions.advantagedatabase.com>
Organization: iAnywhere
User-Agent: XanaNews/1.18.1.2
X-Face: ,QMv7[luB)BpWAQ~:"kw6n%0ieY63.:g2K3n~8ky0;||5Xle*Xq+=~<Fy:0CVC2nx@8~vZ
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
NNTP-Posting-Host: 10.56.66.176
Message-ID: <469b2ef0@solutions.advantagedatabase.com>
Date: 16 Jul 2007 02:40:16 -0700
X-Trace: 16 Jul 2007 02:40:16 -0700, 10.56.66.176
Lines: 16
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!10.56.66.176
Xref: solutions.advantagedatabase.com Advantage.Replication:210
Article PK: 1134065


Kim Jensen wrote in <4697baec@solutions.advantagedatabase.com>:

> A new customer is added to the local servers table primary key is the
> phone number. This record is replicated to a AIS that already have
> the customer on file because had use a different store before. The
> replication comes as an INSERT but it Now have to be change to an
> EDIT or DO NOTHING.

the only triggers that get fired through the replication are ON
CONFLICT triggers.

--
Joachim Duerr
System Consultant (Advantage Database Server)
Sybase iAnywhere
advantagesupport[AT]ianywhere.com


Joachim Duerr (ADS) Posted on 2007-07-17 09:38:03.0Z
From: "Joachim Duerr (ADS)" <jojo.duerr@gmx.de>
Subject: Re: Table Triggers
Newsgroups: Advantage.Replication
References: <469b2ef0@solutions.advantagedatabase.com> <6c56a9222d4068c995e1c3a20f38@devzone.advantagedatabase.com>
Organization: iAnywhere
User-Agent: XanaNews/1.18.1.2
X-Face: ,QMv7[luB)BpWAQ~:"kw6n%0ieY63.:g2K3n~8ky0;||5Xle*Xq+=~<Fy:0CVC2nx@8~vZ
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
NNTP-Posting-Host: 10.56.66.176
Message-ID: <469c7feb@solutions.advantagedatabase.com>
Date: 17 Jul 2007 02:38:03 -0700
X-Trace: 17 Jul 2007 02:38:03 -0700, 10.56.66.176
Lines: 56
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!10.56.66.176
Xref: solutions.advantagedatabase.com Advantage.Replication:212
Article PK: 1134067

Rodd Graham wrote in
<6c56a9222d4068c995e1c3a20f38@devzone.advantagedatabase.com>:

> So, IF I have a summary/detail related pair of tables such that
> INSERTing a detail row UPDATEs a summary table column (such as
> account balance) via a ON INSERT trigger of the detail table, THEN
> replication of the detail row will not trigger the UPDATE of the
> summary row at the target (assuming the target has the same ON INSERT
> trigger)?

correct

> Rather, the summary row should also be replicated if the UPDATE of
> the ON INSERT trigger at the source needs to be propagated to the
> target?

yes

> Seems like it would be difficult to maintain the summary column
> accurately if detail is added at multiple locations with N way
> replication. The summary row replication ON CONFLICT trigger at the
> target would not know which detail row to delta into the summary
> column. I suppose you could add the PK of the most recent detail row
> to the summary table. But then I must be assured that a race
> condition cannot occur where the summary replication happens before
> the detail row has replicated.

use transaction processing in that case. Replication respects
transactions.

> So, the question now is: Are replications guaranteed serialized at
> the target in the same order they were processed at the source? If
> so, does a failed replication pause the replication feed until it is
> resolved?

this is guaranteed

> Have you ever considered implementing a user configuration option
> that would allow AFTER triggers to fire on replications at the hub
> database in a hub and spoke replication configuration? This would
> allow the summary/detail to maintain the summary at the hub as the
> detail is received from the spokes. Not sure this is a good solution
> since it requires a replication round trip before the spokes have
> updated summary. Especially a problem is the spoke is a mobile user
> that goes offline. Is there a standard pattern that makes for
> bulletproof replication in this scenario?

well, it's just not implemented that way. One of the R&D or product
management guys should jump in here.
I don't know of any standard pattern on how to solve such a scenario.

--
Joachim Duerr
System Consultant (Advantage Database Server)
Sybase iAnywhere
advantagesupport[AT]ianywhere.com


Mark Wilkins Posted on 2007-07-17 22:02:25.0Z
From: "Mark Wilkins" <mark@no.email>
Newsgroups: Advantage.Replication
References: <469b2ef0@solutions.advantagedatabase.com> <6c56a9222d4068c995e1c3a20f38@devzone.advantagedatabase.com> <469c7feb@solutions.advantagedatabase.com>
Subject: Re: Table Triggers
Date: Tue, 17 Jul 2007 16:02:25 -0600
Lines: 28
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3138
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: 10.24.38.228
Message-ID: <469d3b4c@solutions.advantagedatabase.com>
X-Trace: 17 Jul 2007 15:57:32 -0700, 10.24.38.228
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!10.24.38.228
Xref: solutions.advantagedatabase.com Advantage.Replication:213
Article PK: 1134069


>
>> Have you ever considered implementing a user configuration option
>> that would allow AFTER triggers to fire on replications at the hub
>> database in a hub and spoke replication configuration? This would
>> allow the summary/detail to maintain the summary at the hub as the
>> detail is received from the spokes. Not sure this is a good solution
>> since it requires a replication round trip before the spokes have
>> updated summary. Especially a problem is the spoke is a mobile user
>> that goes offline. Is there a standard pattern that makes for
>> bulletproof replication in this scenario?
>
> well, it's just not implemented that way. One of the R&D or product
> management guys should jump in here.
> I don't know of any standard pattern on how to solve such a scenario.
>

This is a possibility. We discussed the trigger issues quite a lot during
the design and finally just chose the current implementation because it
seemed the most flexible solution that did not involve adding various
configuration options (the typical feature versus time-to-release tradeoff).
Since then, however, we have discussed making the firing of various triggers
configurable; I will make sure that this suggestion is on our list of
enhancements to consider for future releases.

Mark Wilkins
Advantage R&D


Kim Jensen Posted on 2007-07-19 05:54:19.0Z
Reply-To: "Kim Jensen" <kim@comcasystems.com>
From: "Kim Jensen" <kim@comcasystems.com>
Newsgroups: Advantage.Replication
References: <4697baec@solutions.advantagedatabase.com> <469b2ef0@solutions.advantagedatabase.com>
Subject: Re: Table Triggers
Date: Thu, 19 Jul 2007 01:54:19 -0400
Lines: 26
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3138
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: 66.155.143.238
Message-ID: <469efb70@solutions.advantagedatabase.com>
X-Trace: 18 Jul 2007 23:49:36 -0700, 66.155.143.238
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!66.155.143.238
Xref: solutions.advantagedatabase.com Advantage.Replication:214
Article PK: 1134070

So what happens in this case since the ON CONFLICT trigger only execute on
DELETE and UPDATE?
Will it stop replication?

Kim

"Joachim Duerr (ADS)" <jojo.duerr@gmx.de> wrote in message
news:469b2ef0@solutions.advantagedatabase.com...
> Kim Jensen wrote in <4697baec@solutions.advantagedatabase.com>:
>
>> A new customer is added to the local servers table primary key is the
>> phone number. This record is replicated to a AIS that already have
>> the customer on file because had use a different store before. The
>> replication comes as an INSERT but it Now have to be change to an
>> EDIT or DO NOTHING.
>
> the only triggers that get fired through the replication are ON
> CONFLICT triggers.
>
> --
> Joachim Duerr
> System Consultant (Advantage Database Server)
> Sybase iAnywhere
> advantagesupport[AT]ianywhere.com


Joachim Duerr (ADS) Posted on 2007-07-19 07:39:10.0Z
From: "Joachim Duerr (ADS)" <jojo.duerr@gmx.de>
Subject: Re: Table Triggers
Newsgroups: Advantage.Replication
References: <4697baec@solutions.advantagedatabase.com> <469b2ef0@solutions.advantagedatabase.com> <469efb70@solutions.advantagedatabase.com>
Organization: iAnywhere
User-Agent: XanaNews/1.18.1.2
X-Face: ,QMv7[luB)BpWAQ~:"kw6n%0ieY63.:g2K3n~8ky0;||5Xle*Xq+=~<Fy:0CVC2nx@8~vZ
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
NNTP-Posting-Host: 10.56.66.176
Message-ID: <469f070e@solutions.advantagedatabase.com>
Date: 19 Jul 2007 00:39:10 -0700
X-Trace: 19 Jul 2007 00:39:10 -0700, 10.56.66.176
Lines: 13
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!10.56.66.176
Xref: solutions.advantagedatabase.com Advantage.Replication:215
Article PK: 1134071


Kim Jensen wrote in <469efb70@solutions.advantagedatabase.com>:

> So what happens in this case since the ON CONFLICT trigger only
> execute on DELETE and UPDATE? Will it stop replication?

if you have 2 inserts with the same primary key, then replication will
stop due to the 'duplicate key' error.

--
Joachim Duerr
System Consultant (Advantage Database Server)
Sybase iAnywhere
advantagesupport[AT]ianywhere.com


Joachim Duerr (ADS) Posted on 2007-07-19 07:59:35.0Z
From: "Joachim Duerr (ADS)" <jojo.duerr@gmx.de>
Subject: Re: Table Triggers
Newsgroups: Advantage.Replication
References: <4697baec@solutions.advantagedatabase.com> <469b2ef0@solutions.advantagedatabase.com> <469efb70@solutions.advantagedatabase.com> <469f070e@solutions.advantagedatabase.com>
Organization: iAnywhere
User-Agent: XanaNews/1.18.1.2
X-Face: ,QMv7[luB)BpWAQ~:"kw6n%0ieY63.:g2K3n~8ky0;||5Xle*Xq+=~<Fy:0CVC2nx@8~vZ
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
NNTP-Posting-Host: 10.56.66.176
Message-ID: <469f0bd7@solutions.advantagedatabase.com>
Date: 19 Jul 2007 00:59:35 -0700
X-Trace: 19 Jul 2007 00:59:35 -0700, 10.56.66.176
Lines: 13
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!10.56.66.176
Xref: solutions.advantagedatabase.com Advantage.Replication:216
Article PK: 1134072


Joachim Duerr (ADS) wrote in <469f070e@solutions.advantagedatabase.com>:

> if you have 2 inserts with the same primary key, then replication will
> stop due to the 'duplicate key' error.

forgot one: this is one of the reasons why you _never_ should use
functional primary keys (chapter one of database design).

--
Joachim Duerr
System Consultant (Advantage Database Server)
Sybase iAnywhere
advantagesupport[AT]ianywhere.com


Lance Schmidt Posted on 2007-07-19 15:26:59.0Z
From: "Lance Schmidt" <LanceSc@ExtendSys.com>
Newsgroups: Advantage.Replication
References: <4697baec@solutions.advantagedatabase.com> <469b2ef0@solutions.advantagedatabase.com> <469efb70@solutions.advantagedatabase.com>
Subject: Re: Table Triggers
Date: Thu, 19 Jul 2007 09:26:59 -0600
Lines: 42
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3138
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: 10.24.38.197
Message-ID: <469f819e@solutions.advantagedatabase.com>
X-Trace: 19 Jul 2007 09:22:06 -0700, 10.24.38.197
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!10.24.38.197
Xref: solutions.advantagedatabase.com Advantage.Replication:217
Article PK: 1134073

Hi Kim,

Kim if you are going to be doing replication you will want to make your
primary keys both temporally and spatially unique. You can do this by
adding a site code to the record or using GUIDs. In 8.1 you can create
string GUIDs on any platform using the NEWIDSTRING( ) function.

Best Regards,

Lance Schmidt
Advantage R&D

"Kim Jensen" <kim@comcasystems.com> wrote in message
news:469efb70@solutions.advantagedatabase.com...
> So what happens in this case since the ON CONFLICT trigger only execute on
> DELETE and UPDATE?
> Will it stop replication?
>
> Kim
>
> "Joachim Duerr (ADS)" <jojo.duerr@gmx.de> wrote in message
> news:469b2ef0@solutions.advantagedatabase.com...
>> Kim Jensen wrote in <4697baec@solutions.advantagedatabase.com>:
>>
>>> A new customer is added to the local servers table primary key is the
>>> phone number. This record is replicated to a AIS that already have
>>> the customer on file because had use a different store before. The
>>> replication comes as an INSERT but it Now have to be change to an
>>> EDIT or DO NOTHING.
>>
>> the only triggers that get fired through the replication are ON
>> CONFLICT triggers.
>>
>> --
>> Joachim Duerr
>> System Consultant (Advantage Database Server)
>> Sybase iAnywhere
>> advantagesupport[AT]ianywhere.com
>
>


Kim Jensen Posted on 2007-07-19 23:04:31.0Z
Reply-To: "Kim Jensen" <kim@comcasystems.com>
From: "Kim Jensen" <kim@comcasystems.com>
Newsgroups: Advantage.Replication
References: <4697baec@solutions.advantagedatabase.com> <469b2ef0@solutions.advantagedatabase.com> <469efb70@solutions.advantagedatabase.com> <469f819e@solutions.advantagedatabase.com>
Subject: Re: Table Triggers
Date: Thu, 19 Jul 2007 19:04:31 -0400
Lines: 67
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3138
NNTP-Posting-Host: 66.155.143.238
Message-ID: <469fecdb@solutions.advantagedatabase.com>
X-Trace: 19 Jul 2007 16:59:39 -0700, 66.155.143.238
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!66.155.143.238
Xref: solutions.advantagedatabase.com Advantage.Replication:218
Article PK: 1134074

The primary key do not change it is the customers account number.

What I'm asking is since the ON CONFLICT is not triggered on INSERT what
happens when a insert get replicated to the replication database and that
database already have that primary key?

Here is the scenario. Customer enter store 1 and his file is inserted to
store 1's database, store 1 replicate the record to the main server in
another location.
Next week the same customer enter store 2 and since he is not on file store
2 enter the customer into the store 2's database and since the phone number
is the primary key then the replication will send an insert to the main
server, when it arrive there it can not do an insert because the primary key
is the same, will the replication stop or will it dispose the record. We
don't want the customer in the main database twice which would happen if we
made a unique primary key for each store customers.

Thanks,
Kim Jensen

"Lance Schmidt" <LanceSc@ExtendSys.com> wrote in message
news:469f819e@solutions.advantagedatabase.com...
> Hi Kim,
>
> Kim if you are going to be doing replication you will want to make your
> primary keys both temporally and spatially unique. You can do this by
> adding a site code to the record or using GUIDs. In 8.1 you can create
> string GUIDs on any platform using the NEWIDSTRING( ) function.
>
> Best Regards,
>
> Lance Schmidt
> Advantage R&D
>
> "Kim Jensen" <kim@comcasystems.com> wrote in message
> news:469efb70@solutions.advantagedatabase.com...
>> So what happens in this case since the ON CONFLICT trigger only execute
>> on DELETE and UPDATE?
>> Will it stop replication?
>>
>> Kim
>>
>> "Joachim Duerr (ADS)" <jojo.duerr@gmx.de> wrote in message
>> news:469b2ef0@solutions.advantagedatabase.com...
>>> Kim Jensen wrote in <4697baec@solutions.advantagedatabase.com>:
>>>
>>>> A new customer is added to the local servers table primary key is the
>>>> phone number. This record is replicated to a AIS that already have
>>>> the customer on file because had use a different store before. The
>>>> replication comes as an INSERT but it Now have to be change to an
>>>> EDIT or DO NOTHING.
>>>
>>> the only triggers that get fired through the replication are ON
>>> CONFLICT triggers.
>>>
>>> --
>>> Joachim Duerr
>>> System Consultant (Advantage Database Server)
>>> Sybase iAnywhere
>>> advantagesupport[AT]ianywhere.com
>>
>>
>
>


Kim Jensen Posted on 2007-08-16 19:30:49.0Z
Reply-To: "Kim Jensen" <kim@comcasystems.com>
From: "Kim Jensen" <kim@comcasystems.com>
Newsgroups: Advantage.Replication
References: <469fecdb@solutions.advantagedatabase.com> <6c56a9222d66f8c9987dcfcb5dde@devzone.advantagedatabase.com>
Subject: Re: Table Triggers
Date: Thu, 16 Aug 2007 15:30:49 -0400
Lines: 74
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3138
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: 72.64.222.173
Message-ID: <46c3534c@solutions.advantagedatabase.com>
X-Trace: 15 Aug 2007 13:26:04 -0700, 72.64.222.173
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!72.64.222.173
Xref: solutions.advantagedatabase.com Advantage.Replication:246
Article PK: 1134101

In the newsgroup I found that you can ignore errors if you set the registry
key PERMITTED_REP_ERRORS to the errors you will allow then the records get
removed.

So I just have to find what is the error numbers for Insert a Primary key
and Update a record that do not exist.

Kim

"Rodd Graham" <rgraham@grahamautomation.com> wrote in message
news:6c56a9222d66f8c9987dcfcb5dde@devzone.advantagedatabase.com...
> Hello Kim,
>
>> The primary key do not change it is the customers account number.
>>
>> What I'm asking is since the ON CONFLICT is not triggered on INSERT
>> what happens when a insert get replicated to the replication database
>> and that database already have that primary key?
>>
>> Here is the scenario. Customer enter store 1 and his file is inserted
>> to
>> store 1's database, store 1 replicate the record to the main server in
>> another location.
>> Next week the same customer enter store 2 and since he is not on file
>> store
>> 2 enter the customer into the store 2's database and since the phone
>> number
>> is the primary key then the replication will send an insert to the
>> main
>> server, when it arrive there it can not do an insert because the
>> primary key
>> is the same, will the replication stop or will it dispose the record.
>> We
>> don't want the customer in the main database twice which would happen
>> if we
>> made a unique primary key for each store customers.
>
> Interesting scenario. You have not made store 2 aware of the Store 1
> customer from a week prior, but want the customer to be uniquely
> represented at the home office.
>
> I think your point is very good as I cannot understand why an ON CONFLICT
> trigger should not apply to INSERTS. I can only suppose that it is
> because the ON CONFLICT is triggered by the CRC row change detection and
> not by other (Relational Integrity) RI database controls. With an INSERT,
> there is not any previous CRC to check against and you wouldn't want to
> fire the CONFLICT trigger for every INSERT if you used a null CRC for the
> pre-insert value.
>
> If I was to design your solution using ADS the way it is, I think I would
> go ahead and add the spatial uniqueness to the replication. Since you are
> not replicating back to the stores, I would have the stores replicate to a
> hidden table which is post processed at the home office to merge the
> multiple customer datas into the singular home office view.
>
> To answer your question, based upon the ADS docs for Permitted_Rep_Errors,
> I think that RI violations that are not permitted by this setting always
> cause the replication to stop until it is resolved (automatically or
> manually). The replication queue is not allowed to skip or remove any
> entries until they succeed (except permitted errors). When you think
> about it, RI errors are not normally handled by triggers anyway and
> require some resolution sequence to be defined.
>
> Mastering bulletproof SQL replication appears to be a skillset in and of
> itself. Hopefully, I am on the right track.
>
> Regards,
>
> Rodd Graham, Consultant
> Graham Automation Systems, LLC
>
>


Kim Jensen Posted on 2007-07-15 17:41:28.0Z
Reply-To: "Kim Jensen" <kim@comcasystems.com>
From: "Kim Jensen" <kim@comcasystems.com>
Newsgroups: Advantage.Replication
References: <4697baec@solutions.advantagedatabase.com> <6c56a9222d2808c993773ad38db7@devzone.advantagedatabase.com>
Subject: Re: Table Triggers
Date: Sun, 15 Jul 2007 13:41:28 -0400
Lines: 62
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3138
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: 72.64.222.204
Message-ID: <469a5b31@solutions.advantagedatabase.com>
X-Trace: 15 Jul 2007 11:36:49 -0700, 72.64.222.204
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!72.64.222.204
Xref: solutions.advantagedatabase.com Advantage.Replication:208
Article PK: 1134066

Hello Rodd,

I will do some test. I still haven't found out what I need to monitor to see
if the replication stops because of an error.

Thanks,
Kim

"Rodd Graham" <rgraham@grahamautomation.com> wrote in message
news:6c56a9222d2808c993773ad38db7@devzone.advantagedatabase.com...
> Hello Kim,
>
>> It's my first time creating triggers and I'm little confused what
>> which trigger to use in this scenario.
>>
>> A new customer is added to the local servers table primary key is the
>> phone number. This record is replicated to a AIS that already have the
>> customer on file because had use a different store before. The
>> replication comes as an INSERT but it Now have to be change to an EDIT
>> or DO NOTHING.
>>
>> I understand I have to do a Before trigger, but is it enough to just
>> check "Use implicit transactions to maintain data integrity" or do I
>> need to write some SQL if so what would I write?
>
> My initial guess would be a CONFLICT trigger. However, CONFLICT triggers
> only apply to updates and deletes, not inserts. Hence, you are left with
> only INSTEAD OF, BEFORE, and AFTER for inserts. In this case an INSTEAD
> OF is probably the trigger to use since you may or may not insert the row
> into the table.
>
> Regarding what should be in the trigger: A conditional insert or update
> based upon pre-existence of the new primary key in the target table.
>
> The documentation is unclear what would happen in your case above, but
> without any trigger I suspect ADS would return a primary key duplication
> error to the source server. Based upon the 'How Replication Works
> Internally' docs, since the statement was not successful, the source
> server might retain the update in the replication queue (and try again?).
> However I suspect that statement errors that cannot self-correct on
> retries are probably logged and removed from the replication queue. If
> the replication is removed from the queue after the failed insert, then I
> don't think you will need any trigger to 'do nothing'.
>
> Ultimately, the documentation is too vague on this scenario such that the
> only way to know for sure is to execute tests.
>
> FWIW, except for one-way single source backups, I believe replication
> projects are not quick or easy to implement. They become dependent upon
> the actual concurrency of data and the required integrity. Fullproof
> replication and integrity requires resolving all possible conflict
> scenarios that might occur. This is not an ADS specific issue, but applies
> to all database replication systems.
>
> Regards,
>
> Rodd Graham, Consultant
> Graham Automation Systems, LLC
>
>