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.
Thanks in advance.
--
deepdata
------------------------------------------------------------------------
deepdata's Profile:
http://www.dbtalk.net/m335
View this thread:
http://www.dbtalk.net/t310906