execute immediate trigger creation & rollback

[unknown]

I have created a stored procedure that generates code to
create views and then executes that code via the execute
immediate function.

I am trying to create an instead of trigger on a view that
rolls back an update/insert/delete. When I try to do this I
get the error:

Msg 11736, Level 15, State 0:
Server 'deva_gen_ase', Procedure
'dbo.TRG_CER_ATAD_ADDRESS_D', Line 3:
line 1: You cannot specify this command in an 'execute
immediate' context.

Basically this fails:

execute ('create trigger dbo.TRG_CER_ATAD_ADDRESS_D on
dbo.CER_ATAD_ADDRESS_D instead of insert, update, delete as
raiserror 9988888 "Updates through masked views not allowed"
rollback ')

But this works fine:

execute ('create trigger dbo.TRG_CER_ATAD_ADDRESS_D on
dbo.CER_ATAD_ADDRESS_D instead of insert, update, delete as
raiserror 9988888 "Updates through masked views not allowed"
')

And this works fine:

execute ('create procedure dbo.foo as raiserror 9988888
"Updates through masked views not allowed" rollback')

So it appears that ASE does not object to the 'rollback' as
it allows it in a procedure, nor does it object to the
'create trigger'.

But the combination of 'rollback' and 'create trigger' seems
offensive. Is this a bug? Is there a workaround? Is there
a good way to rollback a transaction without using rollback?

I do not want to use 'select syb_quit()' and I would like to
avoid having the trigger call a stored procedure that
performs the rollback.

Adaptive Server Enterprise/15.0.3/EBF 16554
ESD#1/P/RS6000/AIX 5.3/ase1503/2681/64-bit/FBO/Mon Mar 9
01:35:28 2009

Thanks,
awh

[Mark A. Parsons]

Running the 'create trigger/rollback' at the command line succeeds, so I'm not sure why the 'rollback' is being
disallowed in the exec(); probably an issue with how the exec() argument is being parsed, ie, a bug.

-------------

Within triggers you also have the ability to issue a 'rollback trigger'. This will rollback the DML command that caused
the trigger to fire; it will not rollback any parent transactions.

'create trigger/rollback trigger' within an exec() construct seems to work just fine. (more confirmation that there
must be a parsing issue with a standalone 'rollback' in an exec() construct?)

Post by unknown
I have created a stored procedure that generates code to
create views and then executes that code via the execute
immediate function.
I am trying to create an instead of trigger on a view that
rolls back an update/insert/delete. When I try to do this I
Server 'deva_gen_ase', Procedure
line 1: You cannot specify this command in an 'execute
immediate' context.
execute ('create trigger dbo.TRG_CER_ATAD_ADDRESS_D on
dbo.CER_ATAD_ADDRESS_D instead of insert, update, delete as
raiserror 9988888 "Updates through masked views not allowed"
rollback ')
execute ('create trigger dbo.TRG_CER_ATAD_ADDRESS_D on
dbo.CER_ATAD_ADDRESS_D instead of insert, update, delete as
raiserror 9988888 "Updates through masked views not allowed"
')
execute ('create procedure dbo.foo as raiserror 9988888
"Updates through masked views not allowed" rollback')
So it appears that ASE does not object to the 'rollback' as
it allows it in a procedure, nor does it object to the
'create trigger'.
But the combination of 'rollback' and 'create trigger' seems
offensive. Is this a bug? Is there a workaround? Is there
a good way to rollback a transaction without using rollback?
I do not want to use 'select syb_quit()' and I would like to
avoid having the trigger call a stored procedure that
performs the rollback.
Adaptive Server Enterprise/15.0.3/EBF 16554
ESD#1/P/RS6000/AIX 5.3/ase1503/2681/64-bit/FBO/Mon Mar 9
01:35:28 2009
Thanks,
awh

[unknown]

rollback trigger is perfect.

Thanks for the help.

Post by Mark A. Parsons
Running the 'create trigger/rollback' at the command line
succeeds, so I'm not sure why the 'rollback' is being
disallowed in the exec(); probably an issue with how the
exec() argument is being parsed, ie, a bug.
-------------
Within triggers you also have the ability to issue a
'rollback trigger'. This will rollback the DML command
that caused the trigger to fire; it will not rollback any
parent transactions.
'create trigger/rollback trigger' within an exec()
construct seems to work just fine. (more confirmation
that there must be a parsing issue with a standalone
'rollback' in an exec() construct?)

Post by unknown
I have created a stored procedure that generates code to
create views and then executes that code via the execute
immediate function.
I am trying to create an instead of trigger on a view
that rolls back an update/insert/delete. When I try to
Server 'deva_gen_ase', Procedure
line 1: You cannot specify this command in an 'execute
immediate' context.
execute ('create trigger dbo.TRG_CER_ATAD_ADDRESS_D on
dbo.CER_ATAD_ADDRESS_D instead of insert, update, delete
as raiserror 9988888 "Updates through masked views not
allowed" rollback ')
execute ('create trigger dbo.TRG_CER_ATAD_ADDRESS_D on
dbo.CER_ATAD_ADDRESS_D instead of insert, update, delete
as raiserror 9988888 "Updates through masked views not
allowed" ')
execute ('create procedure dbo.foo as raiserror 9988888
"Updates through masked views not allowed" rollback')
So it appears that ASE does not object to the 'rollback'
as it allows it in a procedure, nor does it object to
the 'create trigger'.
But the combination of 'rollback' and 'create trigger'
seems offensive. Is this a bug? Is there a workaround?
Is there a good way to rollback a transaction without
using rollback?
I do not want to use 'select syb_quit()' and I would
like to avoid having the trigger call a stored procedure
that performs the rollback.
Adaptive Server Enterprise/15.0.3/EBF 16554
ESD#1/P/RS6000/AIX 5.3/ase1503/2681/64-bit/FBO/Mon Mar
9 01:35:28 2009
Thanks,
awh