SEO

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 wa
not a valid Command Line Processor command. During SQL processing i
returned: SQL0104N An unexpected token "(" was found following "EAC
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 i
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 commi
everything (in the ongoing transaction) or only the compound statemen
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 tha
something like first syntax should work, i am not able to figure wha
what is wrong here. Any help to fix this will be highly appreciated.

Thanks in advance

--
deepdat
-----------------------------------------------------------------------
deepdata's Profile: http://www.dbtalk.net/m33
View this thread: http://www.dbtalk.net/t31090

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