CREATE TRIGGER ON INSERT

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sithellaneous@gmail.com - 27 May 2006 08:53 GMT

Hi,

I have looked over various forums, and refined this trigger to the best
of my abilities, but when someone pastes a trigger using code -very-
similar to yours and it still doesn't work for you, ya gotta ask
yourself, what's wrong?

CREATE TRIGGER DB2ADMIN.CHECK_INSTOCK
BEFORE INSERT ON DB2ADMIN.PRODUCTORDER
REFERENCING NEW AS NEW_ORDER
FOR EACH STATEMENT MODE DB2SQL
BEGIN ATOMIC
DECLARE TEMP INTEGER;
SET TEMP = (SELECT INSTOCK FROM DB2ADMIN.PRODUCT
WHERE PRODUCTID = NEW_ORDER.PRODUCTID);
IF (NEW_ORDER.QUANTITY > TEMP) THEN
SIGANL SQLSTATE '70001' ('Insufficient Stock');
END IF;
END;

Above is the trigger i'm trying to get working, the error message
clipping is:

SQL0104N An unexpected token "INTEGER" was found following "ATOMIC
DECLARE
TEMP". Expected tokens may include: "END-OF-STATEMENT". LINE
NUMBER=6.
SQLSTATE=42601

I've seen this line used in other triggers. I've also heard about
setting up an alternate termination character such as @ or #. The
problem being, I need a way to do that in SQL, as this needs to be
submitted as part of a "create script" for an assignment, thus command
editor customizations aren't really ideal.

I wouldn't be using Google Groups if I had other options, I've been
stretched beyond my means, trigger creation isn't even my task or
forte.

Can someone please help me out?

Regards,
b00x

Reply to this Message

Serge Rielau - 27 May 2006 10:31 GMT

> Hi,
>
[quoted text clipped - 30 lines]
> submitted as part of a "create script" for an assignment, thus command
> editor customizations aren't really ideal.

The termination character is indeed your problem.
is your interface the regular CLP? If so simply try this:
--#SET TERMINATOR @
CREATE TRIGGER .......
BEGIN ATOMIC
....
END
@
--#SET TERMINATOR ;

Or you can start CLP like this:
db2 -td@
perhaps
db2 -td@ -f <filename>
for your script.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

sithellaneous@gmail.com - 27 May 2006 10:51 GMT

Serge,

Interesting that a fellow IBMer would respond to me :)

I tried what you said using the following code, and feeding it straight
through the Command Editor GUI:

--#SET TERMINATOR @
CREATE TRIGGER DB2ADMIN.CHECK_INSTOCK
BEFORE INSERT ON DB2ADMIN.PRODUCTORDER
REFERENCING NEW AS NEW_ORDER
FOR EACH STATEMENT MODE DB2SQL
BEGIN ATOMIC
DECLARE TEMP INTEGER;
SET TEMP = (SELECT INSTOCK FROM DB2ADMIN.PRODUCT
WHERE PRODUCTID = NEW_ORDER.PRODUCTID);
IF (NEW_ORDER.QUANTITY > TEMP) THEN
SIGANL SQLSTATE '70001' ('Insufficient Stock');
END IF;
END
@
--#SET TERMINATOR ;

But still the same error, it doesn't like the "INTEGER" following
"DECLARE TEMP" for some reason. Thanx for the SQL on to set the
terminator though, heh I could of almost guessed it ;)

Reply to this Message

Serge Rielau - 27 May 2006 11:20 GMT

> Serge,
>
[quoted text clipped - 22 lines]
> "DECLARE TEMP" for some reason. Thanx for the SQL on to set the
> terminator though, heh I could of almost guessed it ;)

I'm not sure if the GUI eats the --# notation. Might do nothing.
What's so bad about setting the terminator in the GUI...?

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

sithellaneous@gmail.com - 27 May 2006 11:22 GMT

Just a thought,

Maybe if someone could explain to me why the following won't work:

CREATE TRIGGER DB2ADMIN.CHECK_INSTOCK
BEFORE INSERT ON DB2ADMIN.PRODUCTORDER
REFERENCING NEW AS NEW_ORDER
FOR EACH ROW MODE DB2SQL
WHEN (NEW_ORDER.QUANTITY > (SELECT INSTOCK FROM DB2ADMIN.PRODUCT
WHERE PRODUCTID = NEW_ORDER.PRODUCTID))
SIGANL SQLSTATE '70001' ('Insufficient Stock');

Yet one like below, works fine?

CREATE TRIGGER DB2ADMIN.CHECK_ORDERQTY
BEFORE INSERT ON DB2ADMIN.PRODUCTORDER
REFERENCING NEW AS NEW_PRODUCT
FOR EACH ROW MODE DB2SQL
WHEN(NEW_PRODUCT.QUANTITY < 1)
SIGNAL SQLSTATE '70001' ('You cannot order less than 1 of an
item');

And how can I over come the problem?

Maybe this is a better question than my first one.

Thanks in advance!

Regards,
b00x

Reply to this Message

sithellaneous@gmail.com - 27 May 2006 11:29 GMT

Serge,

Nothing, its just dirty, and provides complications when transporting
the create script.

Not to worry, the GUI editor loves the --# ;)

------------------------------ Commands Entered
------------------------------
--#SET TERMINATOR @;
SELECT COUNT(*) FROM PRODUCT;
SELECT COUNT(*) FROM TOURINFO @;
------------------------------------------------------------------------------
SELECT COUNT(*) FROM PRODUCT
1
-----------
42
1 record(s) selected.

SELECT COUNT(*) FROM TOURINFO @
1
-----------
8
1 record(s) selected.

Reply to this Message

sithellaneous@gmail.com - 27 May 2006 11:33 GMT

OKAY!!!!!!!!

I just made a massive dick of myself! (lucky i didn't expose my real
name! ;))

Seems that typo on "SIGANL" is for real. The following worked fine:

CREATE TRIGGER DB2ADMIN.CHECK_INSTOCK
BEFORE INSERT ON DB2ADMIN.PRODUCTORDER
REFERENCING NEW AS NEW_ORDER
FOR EACH ROW MODE DB2SQL
WHEN (NEW_ORDER.QUANTITY > (SELECT INSTOCK FROM DB2ADMIN.PRODUCT
WHERE PRODUCTID = NEW_ORDER.PRODUCTID))
SIGNAL SQLSTATE '70001' ('Insufficient Stock');

Reply to this Message

Serge Rielau - 27 May 2006 13:05 GMT

So it's working now?

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

b00x - 27 May 2006 14:01 GMT

Yeh, was just a stupid mistake. I guess it pays to have another pair of
eyes look at it.

One of my friends checked it out, and saw that typo straight away.

Thanks for your help though, nice to know people do monitor these
groups in real-time :)

Reply to this Message