SQL Anywhere database - Execute immeadiate within an after insert,update trigger

Author

Execute immeadiate within an after insert,update trigger

Dave Westphal

2005-04-14, 11:24 am

version of ASA9 : 9.0.1 build 1964

I am trying to grant connect to a user in an after insert trigger.
When I tried inserting a record into the dbuser table, I received the error:
Error at line 1
COMMIT/ROLLBACK not allowed within atomic operation

Is there a way for me to get this to work?
I have done execute immediate in triggers before, but not to alter the system tables in this way.

Any help would be appreciated
Thanks in advance,
Dave

trigger code:

create trigger
dba.eztaiu_DBUser after insert,update order 1 on
dba.DBUser
referencing new as NewUser
for each row
begin
declare sql_string long varchar;
if not exists ( select 1 from sysusers where name = NewUser.UserName )then
SET sql_string = 'grant connect to ' + String(NewUser.UserName) +
' Identified BY ' + '''' + 'ezdata' + '''';
execute immediate sql_string;
-- message sql_string;
-- SET sql_string = 'grant DBA,RESOURCE to ' + String(NewUser.UserName);
-- execute immediate sql_string;
end if;
end;

Glenn Paulley

2005-04-14, 11:24 am

"Dave Westphal" <Dave_Westphal@aal.org> wrote in
news:425e8170$1@foru
ms-1-dub:

> version of ASA9 : 9.0.1 build 1964
>
> I am trying to grant connect to a user in an after insert trigger.
> When I tried inserting a record into the dbuser table, I received the
> error: Error at line 1
> COMMIT/ROLLBACK not allowed within atomic operation
>
>
> Is there a way for me to get this to work?

Not directly, no. Many (most?) DDL statements in SQL Anywhere perform an
implicit COMMIT operation, which cannot be supported in a trigger.

I would question why you wish to change permissions for any user in such
a trigger. However, if this is the functionality that you really require,
a different way to do it is as follows:

- in the trigger, insert a row in a special user table to represent the
need for a GRANT statement
- periodically (either by explicitly executing a stored procedure, or by
doing so in an EVENT that is fired periodically):

1) OPEN a cursor that SELECTs DISTINCT GRANT requests for individual
users from this special permissions table
2) for each DISTINCT row
3) execute immediate a GRANT statement built from this cursor's
result set
4) DELETE WHERE CURRENT OF CURSOR
5) end for

Glenn

> I have done execute immediate in triggers before, but not to alter the
> system tables in this way.
>
> Any help would be appreciated
> Thanks in advance,
> Dave
>
> trigger code:
>
> create trigger
> dba.eztaiu_DBUser after insert,update order 1 on
> dba.DBUser
> referencing new as NewUser
> for each row
> begin
> declare sql_string long varchar;
> if not exists ( select 1 from sysusers where name = NewUser.UserName
> )then
> SET sql_string = 'grant connect to ' + String(NewUser.UserName) +
> ' Identified BY ' + '''' + 'ezdata' +
> '''';
> execute immediate sql_string;
> -- message sql_string;
> -- SET sql_string = 'grant DBA,RESOURCE to ' +
> String(NewUser.UserName); -- execute immediate sql_string;
> end if;
> end;
> ------ =_NextPart_000_0043_
01C540D6.5635E9C0
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> <HTML><HEAD>
> <META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
> <META content="MSHTML 6.00.2800.1479" name=GENERATOR>
> <STYLE></STYLE>
> </HEAD>
> <BODY bgColor=#ffffff>
> <DIV><FONT face=Arial size=2>version of ASA9 : 9.0.1 build
> 1964</FONT></DIV> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2>I am trying to grant connect to a user in
> an after insert trigger.</FONT></DIV>
> <DIV><FONT face=Arial size=2>When I tried inserting a record into the
> dbuser table, I received the error:</FONT></DIV>
> <DIV><FONT face=Arial size=2>Error at line 1</FONT></DIV>
> <DIV><FONT face=Arial size=2>COMMIT/ROLLBACK not allowed within atomic
> operation</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2>Is there a way for me to get this to
> work?</FONT></DIV>
> <DIV><FONT face=Arial size=2>I have done execute immediate in triggers
> before, but not to alter the system tables in this way.</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2>Any help would be
> appreciated</FONT></DIV> <DIV><FONT face=Arial size=2>Thanks in
> advance,</FONT></DIV> <DIV><FONT face=Arial size=2>Dave</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2>trigger code:</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV><FONT size=2>
> <DIV><FONT face=Arial>create trigger</FONT></DIV>
> <DIV><FONT face=Arial>dba.eztaiu_DBUser after insert,update order 1
> on</FONT></DIV>
> <DIV><FONT face=Arial>dba.DBUser</FONT></DIV>
> <DIV><FONT face=Arial>referencing new as NewUser</FONT></DIV>
> <DIV><FONT face=Arial>for each row</FONT></DIV>
> <DIV><FONT face=Arial>begin</FONT></DIV>
> <DIV><FONT face=Arial> declare sql_string long
> varchar;</FONT></DIV> <DIV><FONT face=Arial> if not exists (
> select 1 from sysusers where name = NewUser.UserName
> )then</FONT></DIV></FONT><FONT face=Courier size=2> <DIV><FONT
> face=Arial>    SET sql_string = 'grant connect to ' +
> String(</FONT></FONT><FONT face=Arial
> size=2>NewUser.UserName</FONT><FONT face=Courier size=2><FONT
> face=Arial> ) + </FONT></DIV> <DIV><FONT
> face=Arial>
>
> ;       ' Identified BY ' + '''' +
> 'ezdata' + '''';</FONT></DIV> <DIV><FONT face=Arial>
> execute immediate sql_string; </FONT></DIV></FONT><FONT size=2>
> <DIV><FONT face=Arial>-- message </FONT></FONT><FONT face=Courier
> size=2><FONT face=Arial>sql_string;</FONT></DIV>
> <DIV><FONT face=Arial>-- SET sql_string = 'grant DBA,RESOURCE to ' +
> String(</FONT></FONT><FONT face=Arial
> size=2>NewUser.UserName</FONT><FONT face=Courier size=2><FONT
> face=Arial> ); </FONT></DIV> <DIV><FONT face=Arial>-- execute immediate
> sql_string; </FONT></DIV></FONT><FONT size=2>
> <DIV><FONT face=Arial> end if;</FONT></DIV>
> <DIV><FONT face=Arial>end;</FONT></DIV></FONT></BODY></HTML>
>
> ------ =_NextPart_000_0043_
01C540D6.5635E9C0--

--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

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

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

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

Breck Carter

2005-04-14, 1:23 pm

Create a user-defined EVENT with a user-defined parameter,
and use TRIGGER EVENT to fire it. Then you can pass the
command in a string to the event and have it to EXECUTE
IMMEDIATE. It will work because events run on their own
connections.

Breck

> version of ASA9 : 9.0.1 build 1964
>
> I am trying to grant connect to a user in an after insert
> trigger. When I tried inserting a record into the dbuser
> table, I received the error: Error at line 1
> COMMIT/ROLLBACK not allowed within atomic operation
>
>
> Is there a way for me to get this to work?
> I have done execute immediate in triggers before, but not
> to alter the system tables in this way.
>
> Any help would be appreciated
> Thanks in advance,
> Dave
>
> trigger code:
>
> create trigger
> dba.eztaiu_DBUser after insert,update order 1 on
> dba.DBUser
> referencing new as NewUser
> for each row
> begin
> declare sql_string long varchar;
> if not exists ( select 1 from sysusers where name =
> NewUser.UserName )then
> SET sql_string = 'grant connect to ' +
> String(NewUser.UserName) +
> ' Identified BY ' + '''' +
> 'ezdata' + '''';
> execute immediate sql_string;
> -- message sql_string;
> -- SET sql_string = 'grant DBA,RESOURCE to ' +
> String(NewUser.UserName); -- execute immediate sql_string
> ;
> end if;
> end;

Sponsored Links