CREATE TRIGGER "SYSTEM".CHECKDATETIME NO CASCADE BEFORE
INSERT
ON "SYSTEM".NON_MEMBER_BOOKING
REFERENCING NEW AS N FOR EACH ROW
MODE DB2SQL
WHEN (
(SELECT * FROM "SYSTEM".NON_MEMBER_BOOKING AS NON1 WHERE
NON1.BOOKDATE = N.BOOKDATE AND SELECT * FROM "SYSTEM".NON_MEMBER_BOOKING AS NON2 WHERE
NON2.BOOKTIME = N.BOOKTIME) OR
(SELECT * FROM "SYSTEM".MEMBER_BOOKING AS MEM WHERE MEM.BOOKDATE = N.BOOKDATE AND SELECT * FROM "SYSTEM".MEMBER_BOOKING AS MEM2 WHERE MEM2.BOOKTIME = N.BOOKTIME)
)
BEGIN ATOMIC
SIGNAL SQLSTATE '70001' ('THIS DATE AND TIME IS ALREADY BEEN BOOKED BY OTHERS!');
END

I cannot compile, I have no idea why, I am newbie in db2...

It show me this error "SQLSTATE=42601"

Please help, I am really urgent on it...
Your help will be appreciated!

THANK YOU SO MUCH!!

You made some apparent syntax errors.
For example:
1)
WHEN (
(SELECT * ... ) OR
(SELECT * ... )
)

2)
WHERE NON1.BOOKDATE = N.BOOKDATE AND SELECT * ...

One way to eliminate the syntax errors may be:
WHEN (
EXISTS (SELECT * FROM SYSTEM.NON_MEMBER_BOOKING AS NON WHERE
NON.BOOKDATE = N.BOOKDATE AND
NON.BOOKTIME = N.BOOKTIME) OR
EXISTS (SELECT * FROM SYSTEM.MEMBER_BOOKING AS MEM WHERE MEM.BOOKDATE = N.BOOKDATE AND MEM.BOOKTIME = N.BOOKTIME)
)

But, I don't know this will satisfy your requirement.

CREATE TRIGGER KEN.CHECKDATETIME NO CASCADE BEFORE
INSERT
ON KEN.NON_MEMBER_BOOKING
REFERENCING
NEW AS N
FOR EACH ROW
MODE DB2SQL
WHEN (
EXISTS (SELECT * FROM KEN.NON_MEMBER_BOOKING AS NON WHERE NON.BOOKDATE = N.BOOKDATE AND NON.BOOKTIME = N.BOOKTIME) OR
EXISTS (SELECT * FROM KEN.MEMBER_BOOKING AS MEM WHERE MEM.BOOKDATE = N.BOOKDATE AND MEM.BOOKTIME = N.BOOKTIME)
)
BEGIN ATOMIC
SIGNAL SQLSTATE '70001' ('THIS DATE AND TIME IS ALREADY BEEN BOOKED BY OTHERS!');
END


Still cannot ..i dono y.. any one please help?

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 "END-OF-STATEMENT" was found following "BOOKED
BY OTHERS!')". Expected tokens may include: "<delim_semicolon>". LINE
NUMBER=13. SQLSTATE=42601

END
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 "END-OF-STATEMENT" was found following "END".
Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END". Expected tokens may include: "JOIN <joined_table> ".

Explanation:

A syntax error in the SQL statement or the input command string for the
SYSPROC.ADMIN_CMD procedure was detected at the specified token
following the text "<text>". The "<text>" field indicates the 20
characters of the SQL statement or the input command string for the
SYSPROC.ADMIN_CMD procedure that preceded the token that is not valid.

As an aid, a partial list of valid tokens is provided in the SQLERRM
field of the SQLCA as "<token-list>". This list assumes the statement is
correct to that point.

The statement cannot be processed.

User response:

Examine and correct the statement in the area of the specified token.

sqlcode: -104

sqlstate: 42601


It show me this error

Change statement termination character to other than semicolon.

OR

Remove BEGIN ATOMIC/END.

U really my king!... I hug you!
THank you very very much... is working!