deepdata wrote:
> Hi,
>
> I am creating a trigger in DB2 express version.
>
> When i use the following syntax to create trigger
>
> CREATE TRIGGER USER_PK_TRIGGER
> BEFORE INSERT On users
> REFERENCING NEW As N
> FOR EACH ROW
> Begin
> if((N.user_id is null) or (N.user_id <= 0)) then
> set N.user_id = nextval for user_seq;
> end if;
> End@
>
>
> I am getting the following error.
>
> DB21034E The command was processed as an SQL statement because it was
> not a valid Command Line Processor command. During SQL processing it
> returned: SQL0104N An unexpected token "(" was found following "EACH
> ROW Begin if(".
> Expected tokens may include: "<space>". LINE NUMBER=6.
> SQLSTATE=42601
>
> But when i change my create trigger syntax to include atomic word it
> works.
>
> CREATE TRIGGER USER_PK_TRIGGER
> BEFORE INSERT On users
> REFERENCING NEW As N
> FOR EACH ROW
> Begin Atomic
> if((N.user_id is null) or (N.user_id <= 0)) then
> set N.user_id = nextval for user_seq;
> end if;
> End@
>
> Why do i need to make my trigger atomic? When it commits does it commit
> everything (in the ongoing transaction) or only the compound statement
> in the trigger? What is wrong in my first create trigger syntax?
>
> I am new in DB2 and in most of the examples they have shown that
> something like first syntax should work, i am not able to figure what
> what is wrong here. Any help to fix this will be highly appreciated.
CREATE TRIGGER:
<blah..blah>
SQL-procedure-statement:

|--+-CALL----------------------------------------------+--------|
+-Compound SQL (Dynamic)----------------------------+
+...


Under compound SQL (Dynamic):
dynamic-compound-statement

>>-+-------------+--BEGIN ATOMIC--------.......
| (1) |
'-label:------'


So much for the syntax. Now for the semantics.
A when a trigger fails the invoking statement must fail to maintain DB
integrity. Thus atomicity is semantically required.
COMMIT or ROLLBACK are not allowed in triggers.
You can ROLLBACK TO SAVEPOINT in a procedure called by a trigger if you
wish, but that savepoint has to be local within the trigger's scope.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab