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.

add/drop users in ASA9-database in a trigger-statement

8 posts in General Discussion Last posting was on 2006-01-27 23:55:42.0Z
M.Erlinger Posted on 2006-01-23 16:13:43.0Z
From: "M.Erlinger" <michael.erlinger@erlinger-software.at>
User-Agent: Mozilla Thunderbird 1.0.7 (Windows/20050923)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: add/drop users in ASA9-database in a trigger-statement
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 62.212.169.105
X-Original-NNTP-Posting-Host: 62.212.169.105
Message-ID: <43d500b7@forums-1-dub>
Date: 23 Jan 2006 08:13:43 -0800
X-Trace: forums-1-dub 1138032823 62.212.169.105 (23 Jan 2006 08:13:43 -0800)
X-Original-Trace: 23 Jan 2006 08:13:43 -0800, 62.212.169.105
Lines: 17
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5125
Article PK: 17524

Hello

i have from my powerbuilder-application a user-database/table - now i
want to create or drop the user's in the ASA-9-database in a
trigger-statement on my user-table...............but if i do this:

***** trigger-statement for new row *******
call sp_adduser( new_phy_mitarbeiter.c_login,lc_name,'AppUser');
call
sp_password('sql',new_phy_mitarbeiter.c_passwort,new_phy_mitarbeiter.c_login);
***************************************

i get the error-message "commit/rollback in atomic process not valid" ???

how could i solve this problem ?
many thank's in advance
Michael


Breck Carter [TeamSybase] Posted on 2006-01-23 16:36:24.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: add/drop users in ASA9-database in a trigger-statement
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <nf0at1pihffvt34j2p6lk6a2mpl1ulin8t@4ax.com>
References: <43d500b7@forums-1-dub>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: bcarter.sentex.ca
X-Original-NNTP-Posting-Host: bcarter.sentex.ca
Date: 23 Jan 2006 08:36:24 -0800
X-Trace: forums-1-dub 1138034184 64.7.134.118 (23 Jan 2006 08:36:24 -0800)
X-Original-Trace: 23 Jan 2006 08:36:24 -0800, bcarter.sentex.ca
Lines: 41
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5126
Article PK: 8675

The sp_adduser and sp_password procedures both execute GRANT CONNECT
commands, and GRANT CONNECT has "automatic commit" as a side-effect.
Because all statements which fire triggers (insert, update, etc) must
be atomic in nature, the commit and rollback operations are not
allowed within triggers; otherwise, it would be possible to "partially
execute an update" and other dirty things :)

If you *really* want this to happen, and you don't mind if it is
asynchronous, you could do the GRANT CONNECT inside a CREATE EVENT,
and do TRIGGER EVENT from inside the trigger. That will run the event
on a separate connection.

Breck

On 23 Jan 2006 08:13:43 -0800, "M.Erlinger"

<michael.erlinger@erlinger-software.at> wrote:

>Hello
>
>i have from my powerbuilder-application a user-database/table - now i
>want to create or drop the user's in the ASA-9-database in a
>trigger-statement on my user-table...............but if i do this:
>
>***** trigger-statement for new row *******
> call sp_adduser( new_phy_mitarbeiter.c_login,lc_name,'AppUser');
> call
>sp_password('sql',new_phy_mitarbeiter.c_passwort,new_phy_mitarbeiter.c_login);
>***************************************
>
>i get the error-message "commit/rollback in atomic process not valid" ???
>
>how could i solve this problem ?
>many thank's in advance
>Michael

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com


M.Erlinger Posted on 2006-01-23 17:41:31.0Z
From: "M.Erlinger" <michael.erlinger@erlinger-software.at>
Organization: ERLINGER-Software
User-Agent: Mozilla Thunderbird 1.0.7 (Windows/20050923)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: add/drop users in ASA9-database in a trigger-statement
References: <43d500b7@forums-1-dub> <nf0at1pihffvt34j2p6lk6a2mpl1ulin8t@4ax.com>
In-Reply-To: <nf0at1pihffvt34j2p6lk6a2mpl1ulin8t@4ax.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: 62.212.169.105
Message-ID: <43d5122e$1@forums-2-dub>
X-Original-Trace: 23 Jan 2006 09:28:14 -0800, 62.212.169.105
Lines: 54
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 23 Jan 2006 09:28:17 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 23 Jan 2006 09:41:31 -0800
X-Trace: forums-1-dub 1138038091 10.22.108.75 (23 Jan 2006 09:41:31 -0800)
X-Original-Trace: 23 Jan 2006 09:41:31 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5127
Article PK: 31524

Hi

yes, i *really* want this to happen :-)
or have you an another way of solution - to create or drop the a user in
database, if the user become to insert or delete in the application?

thank's
Michael

Breck Carter [TeamSybase] wrote:
> The sp_adduser and sp_password procedures both execute GRANT CONNECT
> commands, and GRANT CONNECT has "automatic commit" as a side-effect.
> Because all statements which fire triggers (insert, update, etc) must
> be atomic in nature, the commit and rollback operations are not
> allowed within triggers; otherwise, it would be possible to "partially
> execute an update" and other dirty things :)
>
> If you *really* want this to happen, and you don't mind if it is
> asynchronous, you could do the GRANT CONNECT inside a CREATE EVENT,
> and do TRIGGER EVENT from inside the trigger. That will run the event
> on a separate connection.
>
> Breck
>
> On 23 Jan 2006 08:13:43 -0800, "M.Erlinger"
> <michael.erlinger@erlinger-software.at> wrote:
>
>
>>Hello
>>
>>i have from my powerbuilder-application a user-database/table - now i
>>want to create or drop the user's in the ASA-9-database in a
>>trigger-statement on my user-table...............but if i do this:
>>
>>***** trigger-statement for new row *******
>> call sp_adduser( new_phy_mitarbeiter.c_login,lc_name,'AppUser');
>> call
>>sp_password('sql',new_phy_mitarbeiter.c_passwort,new_phy_mitarbeiter.c_login);
>>***************************************
>>
>>i get the error-message "commit/rollback in atomic process not valid" ???
>>
>>how could i solve this problem ?
>>many thank's in advance
>>Michael
>
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book: http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com


Breck Carter [TeamSybase] Posted on 2006-01-23 18:27:51.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: add/drop users in ASA9-database in a trigger-statement
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <657at1p8l31phdqodn1gud57ofpti5fism@4ax.com>
References: <43d500b7@forums-1-dub> <nf0at1pihffvt34j2p6lk6a2mpl1ulin8t@4ax.com> <43d5122e$1@forums-2-dub>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: bcarter.sentex.ca
X-Original-NNTP-Posting-Host: bcarter.sentex.ca
Date: 23 Jan 2006 10:27:51 -0800
X-Trace: forums-1-dub 1138040871 64.7.134.118 (23 Jan 2006 10:27:51 -0800)
X-Original-Trace: 23 Jan 2006 10:27:51 -0800, bcarter.sentex.ca
Lines: 72
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5128
Article PK: 8677

The TRIGGER EVENT from inside the trigger solution should work.

You could also move the statements out of the trigger, into the
application or into stored procedures containing the triggering
statements. But you cannot do it inside a trigger.

Breck

On 23 Jan 2006 09:41:31 -0800, "M.Erlinger"

<michael.erlinger@erlinger-software.at> wrote:

>Hi
>
>yes, i *really* want this to happen :-)
>or have you an another way of solution - to create or drop the a user in
>database, if the user become to insert or delete in the application?
>
>thank's
>Michael
>
>
>Breck Carter [TeamSybase] wrote:
>> The sp_adduser and sp_password procedures both execute GRANT CONNECT
>> commands, and GRANT CONNECT has "automatic commit" as a side-effect.
>> Because all statements which fire triggers (insert, update, etc) must
>> be atomic in nature, the commit and rollback operations are not
>> allowed within triggers; otherwise, it would be possible to "partially
>> execute an update" and other dirty things :)
>>
>> If you *really* want this to happen, and you don't mind if it is
>> asynchronous, you could do the GRANT CONNECT inside a CREATE EVENT,
>> and do TRIGGER EVENT from inside the trigger. That will run the event
>> on a separate connection.
>>
>> Breck
>>
>> On 23 Jan 2006 08:13:43 -0800, "M.Erlinger"
>> <michael.erlinger@erlinger-software.at> wrote:
>>
>>
>>>Hello
>>>
>>>i have from my powerbuilder-application a user-database/table - now i
>>>want to create or drop the user's in the ASA-9-database in a
>>>trigger-statement on my user-table...............but if i do this:
>>>
>>>***** trigger-statement for new row *******
>>> call sp_adduser( new_phy_mitarbeiter.c_login,lc_name,'AppUser');
>>> call
>>>sp_password('sql',new_phy_mitarbeiter.c_passwort,new_phy_mitarbeiter.c_login);
>>>***************************************
>>>
>>>i get the error-message "commit/rollback in atomic process not valid" ???
>>>
>>>how could i solve this problem ?
>>>many thank's in advance
>>>Michael
>>
>>
>> --
>> SQL Anywhere Studio 9 Developer's Guide
>> Buy the book: http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
>> bcarter@risingroad.com
>> RisingRoad SQL Anywhere and MobiLink Professional Services
>> www.risingroad.com

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com


M.Erlinger Posted on 2006-01-24 08:45:35.0Z
From: "M.Erlinger" <michael.erlinger@erlinger-software.at>
User-Agent: Mozilla Thunderbird 1.0.7 (Windows/20050923)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: add/drop users in ASA9-database in a trigger-statement
References: <43d500b7@forums-1-dub> <nf0at1pihffvt34j2p6lk6a2mpl1ulin8t@4ax.com> <43d5122e$1@forums-2-dub> <657at1p8l31phdqodn1gud57ofpti5fism@4ax.com>
In-Reply-To: <657at1p8l31phdqodn1gud57ofpti5fism@4ax.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: 62.212.169.105
Message-ID: <43d5e60f$1@forums-2-dub>
X-Original-Trace: 24 Jan 2006 00:32:15 -0800, 62.212.169.105
Lines: 84
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 24 Jan 2006 00:32:18 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 24 Jan 2006 00:45:35 -0800
X-Trace: forums-1-dub 1138092335 10.22.108.75 (24 Jan 2006 00:45:35 -0800)
X-Original-Trace: 24 Jan 2006 00:45:35 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5131
Article PK: 1195

Thank's Breck
"TRIGGER EVENT my_event_add_user(......) " work's fine!!!! - in
conjunction with triggers from the table.......

regards
Michael

Breck Carter [TeamSybase] wrote:
> The TRIGGER EVENT from inside the trigger solution should work.
>
> You could also move the statements out of the trigger, into the
> application or into stored procedures containing the triggering
> statements. But you cannot do it inside a trigger.
>
> Breck
>
> On 23 Jan 2006 09:41:31 -0800, "M.Erlinger"
> <michael.erlinger@erlinger-software.at> wrote:
>
>
>>Hi
>>
>>yes, i *really* want this to happen :-)
>>or have you an another way of solution - to create or drop the a user in
>>database, if the user become to insert or delete in the application?
>>
>>thank's
>>Michael
>>
>>
>>Breck Carter [TeamSybase] wrote:
>>
>>>The sp_adduser and sp_password procedures both execute GRANT CONNECT
>>>commands, and GRANT CONNECT has "automatic commit" as a side-effect.
>>>Because all statements which fire triggers (insert, update, etc) must
>>>be atomic in nature, the commit and rollback operations are not
>>>allowed within triggers; otherwise, it would be possible to "partially
>>>execute an update" and other dirty things :)
>>>
>>>If you *really* want this to happen, and you don't mind if it is
>>>asynchronous, you could do the GRANT CONNECT inside a CREATE EVENT,
>>>and do TRIGGER EVENT from inside the trigger. That will run the event
>>>on a separate connection.
>>>
>>>Breck
>>>
>>>On 23 Jan 2006 08:13:43 -0800, "M.Erlinger"
>>><michael.erlinger@erlinger-software.at> wrote:
>>>
>>>
>>>
>>>>Hello
>>>>
>>>>i have from my powerbuilder-application a user-database/table - now i
>>>>want to create or drop the user's in the ASA-9-database in a
>>>>trigger-statement on my user-table...............but if i do this:
>>>>
>>>>***** trigger-statement for new row *******
>>>> call sp_adduser( new_phy_mitarbeiter.c_login,lc_name,'AppUser');
>>>> call
>>>>sp_password('sql',new_phy_mitarbeiter.c_passwort,new_phy_mitarbeiter.c_login);
>>>>***************************************
>>>>
>>>>i get the error-message "commit/rollback in atomic process not valid" ???
>>>>
>>>>how could i solve this problem ?
>>>>many thank's in advance
>>>>Michael
>>>
>>>
>>>--
>>>SQL Anywhere Studio 9 Developer's Guide
>>>Buy the book: http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
>>>bcarter@risingroad.com
>>>RisingRoad SQL Anywhere and MobiLink Professional Services
>>>www.risingroad.com
>
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book: http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com


Breck Carter [TeamSybase] Posted on 2006-01-24 12:24:53.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: add/drop users in ASA9-database in a trigger-statement
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <o96ct15kmd2leh81m5ospflu1o9n8i53md@4ax.com>
References: <43d500b7@forums-1-dub> <nf0at1pihffvt34j2p6lk6a2mpl1ulin8t@4ax.com> <43d5122e$1@forums-2-dub> <657at1p8l31phdqodn1gud57ofpti5fism@4ax.com> <43d5e60f$1@forums-2-dub>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: bcarter.sentex.ca
X-Original-NNTP-Posting-Host: bcarter.sentex.ca
Date: 24 Jan 2006 04:24:53 -0800
X-Trace: forums-1-dub 1138105493 64.7.134.118 (24 Jan 2006 04:24:53 -0800)
X-Original-Trace: 24 Jan 2006 04:24:53 -0800, bcarter.sentex.ca
Lines: 102
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5132
Article PK: 1196

That's good!

I will repeat my warning, however: TRIGGER EVENT is an asynchronous
fire-and-forget operation, not a synchronous operation like CALL. So
there is no guarantee about *how soon* the event will execute.

Breck

On 24 Jan 2006 00:45:35 -0800, "M.Erlinger"

<michael.erlinger@erlinger-software.at> wrote:

>Thank's Breck
>"TRIGGER EVENT my_event_add_user(......) " work's fine!!!! - in
>conjunction with triggers from the table.......
>
>regards
>Michael
>
>Breck Carter [TeamSybase] wrote:
>> The TRIGGER EVENT from inside the trigger solution should work.
>>
>> You could also move the statements out of the trigger, into the
>> application or into stored procedures containing the triggering
>> statements. But you cannot do it inside a trigger.
>>
>> Breck
>>
>> On 23 Jan 2006 09:41:31 -0800, "M.Erlinger"
>> <michael.erlinger@erlinger-software.at> wrote:
>>
>>
>>>Hi
>>>
>>>yes, i *really* want this to happen :-)
>>>or have you an another way of solution - to create or drop the a user in
>>>database, if the user become to insert or delete in the application?
>>>
>>>thank's
>>>Michael
>>>
>>>
>>>Breck Carter [TeamSybase] wrote:
>>>
>>>>The sp_adduser and sp_password procedures both execute GRANT CONNECT
>>>>commands, and GRANT CONNECT has "automatic commit" as a side-effect.
>>>>Because all statements which fire triggers (insert, update, etc) must
>>>>be atomic in nature, the commit and rollback operations are not
>>>>allowed within triggers; otherwise, it would be possible to "partially
>>>>execute an update" and other dirty things :)
>>>>
>>>>If you *really* want this to happen, and you don't mind if it is
>>>>asynchronous, you could do the GRANT CONNECT inside a CREATE EVENT,
>>>>and do TRIGGER EVENT from inside the trigger. That will run the event
>>>>on a separate connection.
>>>>
>>>>Breck
>>>>
>>>>On 23 Jan 2006 08:13:43 -0800, "M.Erlinger"
>>>><michael.erlinger@erlinger-software.at> wrote:
>>>>
>>>>
>>>>
>>>>>Hello
>>>>>
>>>>>i have from my powerbuilder-application a user-database/table - now i
>>>>>want to create or drop the user's in the ASA-9-database in a
>>>>>trigger-statement on my user-table...............but if i do this:
>>>>>
>>>>>***** trigger-statement for new row *******
>>>>> call sp_adduser( new_phy_mitarbeiter.c_login,lc_name,'AppUser');
>>>>> call
>>>>>sp_password('sql',new_phy_mitarbeiter.c_passwort,new_phy_mitarbeiter.c_login);
>>>>>***************************************
>>>>>
>>>>>i get the error-message "commit/rollback in atomic process not valid" ???
>>>>>
>>>>>how could i solve this problem ?
>>>>>many thank's in advance
>>>>>Michael
>>>>
>>>>
>>>>--
>>>>SQL Anywhere Studio 9 Developer's Guide
>>>>Buy the book: http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
>>>>bcarter@risingroad.com
>>>>RisingRoad SQL Anywhere and MobiLink Professional Services
>>>>www.risingroad.com
>>
>>
>> --
>> SQL Anywhere Studio 9 Developer's Guide
>> Buy the book: http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
>> bcarter@risingroad.com
>> RisingRoad SQL Anywhere and MobiLink Professional Services
>> www.risingroad.com

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com


Rob Waywell Posted on 2006-01-24 20:46:37.0Z
From: "Rob Waywell" <rwaywell_no_spam_please@ianywhere.com>
Newsgroups: ianywhere.public.general
References: <43d500b7@forums-1-dub> <nf0at1pihffvt34j2p6lk6a2mpl1ulin8t@4ax.com> <43d5122e$1@forums-2-dub> <657at1p8l31phdqodn1gud57ofpti5fism@4ax.com> <43d5e60f$1@forums-2-dub> <o96ct15kmd2leh81m5ospflu1o9n8i53md@4ax.com>
Subject: Re: add/drop users in ASA9-database in a trigger-statement
Lines: 134
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-Original-NNTP-Posting-Host: rwaywell-d610.sybase.com
Message-ID: <43d68f0a$1@forums-2-dub>
X-Original-Trace: 24 Jan 2006 12:33:14 -0800, rwaywell-d610.sybase.com
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 24 Jan 2006 12:33:17 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 24 Jan 2006 12:46:37 -0800
X-Trace: forums-1-dub 1138135597 10.22.108.75 (24 Jan 2006 12:46:37 -0800)
X-Original-Trace: 24 Jan 2006 12:46:37 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5143
Article PK: 1204

Note that Events operate on their own connection (which goes along with
being asynchronous). There is no way for the Event to return an error back
to the trigger that initiated it. Is this really acceptable to your
application?

--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional

Sybase's iAnywhere Solutions

Please respond ONLY to newsgroup

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports:
http://case-express.sybase.com/cx/cx.stm?starturl=casemessage.ssc?CASETYPE=Bug

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

"Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com> wrote in
message news:o96ct15kmd2leh81m5ospflu1o9n8i53md@4ax.com...
> That's good!
>
> I will repeat my warning, however: TRIGGER EVENT is an asynchronous
> fire-and-forget operation, not a synchronous operation like CALL. So
> there is no guarantee about *how soon* the event will execute.
>
> Breck
>
> On 24 Jan 2006 00:45:35 -0800, "M.Erlinger"
> <michael.erlinger@erlinger-software.at> wrote:
>
>>Thank's Breck
>>"TRIGGER EVENT my_event_add_user(......) " work's fine!!!! - in
>>conjunction with triggers from the table.......
>>
>>regards
>>Michael
>>
>>Breck Carter [TeamSybase] wrote:
>>> The TRIGGER EVENT from inside the trigger solution should work.
>>>
>>> You could also move the statements out of the trigger, into the
>>> application or into stored procedures containing the triggering
>>> statements. But you cannot do it inside a trigger.
>>>
>>> Breck
>>>
>>> On 23 Jan 2006 09:41:31 -0800, "M.Erlinger"
>>> <michael.erlinger@erlinger-software.at> wrote:
>>>
>>>
>>>>Hi
>>>>
>>>>yes, i *really* want this to happen :-)
>>>>or have you an another way of solution - to create or drop the a user in
>>>>database, if the user become to insert or delete in the application?
>>>>
>>>>thank's
>>>>Michael
>>>>
>>>>
>>>>Breck Carter [TeamSybase] wrote:
>>>>
>>>>>The sp_adduser and sp_password procedures both execute GRANT CONNECT
>>>>>commands, and GRANT CONNECT has "automatic commit" as a side-effect.
>>>>>Because all statements which fire triggers (insert, update, etc) must
>>>>>be atomic in nature, the commit and rollback operations are not
>>>>>allowed within triggers; otherwise, it would be possible to "partially
>>>>>execute an update" and other dirty things :)
>>>>>
>>>>>If you *really* want this to happen, and you don't mind if it is
>>>>>asynchronous, you could do the GRANT CONNECT inside a CREATE EVENT,
>>>>>and do TRIGGER EVENT from inside the trigger. That will run the event
>>>>>on a separate connection.
>>>>>
>>>>>Breck
>>>>>
>>>>>On 23 Jan 2006 08:13:43 -0800, "M.Erlinger"
>>>>><michael.erlinger@erlinger-software.at> wrote:
>>>>>
>>>>>
>>>>>
>>>>>>Hello
>>>>>>
>>>>>>i have from my powerbuilder-application a user-database/table - now i
>>>>>>want to create or drop the user's in the ASA-9-database in a
>>>>>>trigger-statement on my user-table...............but if i do this:
>>>>>>
>>>>>>***** trigger-statement for new row *******
>>>>>> call sp_adduser( new_phy_mitarbeiter.c_login,lc_name,'AppUser');
>>>>>> call
>>>>>>sp_password('sql',new_phy_mitarbeiter.c_passwort,new_phy_mitarbeiter.c_login);
>>>>>>***************************************
>>>>>>
>>>>>>i get the error-message "commit/rollback in atomic process not valid"
>>>>>>???
>>>>>>
>>>>>>how could i solve this problem ?
>>>>>>many thank's in advance
>>>>>>Michael
>>>>>
>>>>>
>>>>>--
>>>>>SQL Anywhere Studio 9 Developer's Guide
>>>>>Buy the book:
>>>>>http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
>>>>>bcarter@risingroad.com
>>>>>RisingRoad SQL Anywhere and MobiLink Professional Services
>>>>>www.risingroad.com
>>>
>>>
>>> --
>>> SQL Anywhere Studio 9 Developer's Guide
>>> Buy the book:
>>> http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
>>> bcarter@risingroad.com
>>> RisingRoad SQL Anywhere and MobiLink Professional Services
>>> www.risingroad.com
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
> http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com


M.Erlinger Posted on 2006-01-27 23:55:42.0Z
From: "M.Erlinger" <michael.erlinger@erlinger-software.at>
User-Agent: Mozilla Thunderbird 1.0.7 (Windows/20050923)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: add/drop users in ASA9-database in a trigger-statement
References: <43d500b7@forums-1-dub> <nf0at1pihffvt34j2p6lk6a2mpl1ulin8t@4ax.com> <43d5122e$1@forums-2-dub> <657at1p8l31phdqodn1gud57ofpti5fism@4ax.com> <43d5e60f$1@forums-2-dub> <o96ct15kmd2leh81m5ospflu1o9n8i53md@4ax.com> <43d68f0a$1@forums-2-dub>
In-Reply-To: <43d68f0a$1@forums-2-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: 62.212.169.105
Message-ID: <43daafc9$1@forums-2-dub>
X-Original-Trace: 27 Jan 2006 15:42:01 -0800, 62.212.169.105
Lines: 13
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 27 Jan 2006 15:42:04 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 27 Jan 2006 15:55:42 -0800
X-Trace: forums-1-dub 1138406142 10.22.108.75 (27 Jan 2006 15:55:42 -0800)
X-Original-Trace: 27 Jan 2006 15:55:42 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5155
Article PK: 1220

o.k. - thank's for your information and warnings - i become to examine
this for my application.....................

Michael

Rob Waywell wrote:
> Note that Events operate on their own connection (which goes along with
> being asynchronous). There is no way for the Event to return an error back
> to the trigger that initiated it. Is this really acceptable to your
> application?
>