DB2 SQL Multiple Trigger Cascade

Question

As above title mentioned. Assuming I have 3 table.

--table A, B, C

and two trigger that are meant to cascade:

--trigger X, Y
create trigger X
after insert on A
...(some codes)
update B set ...(some codes);

--and
create trigger Y
after update on B
...(some codes)
insert into C values ( ...(some codes));

where:

--insert on A fire X to update B, then again fire Y to insert on C.

But the problem is first trigger X was fired without problem but second trigger Y is not being fired after the first.

What may be the problem with above code?

For my project, I'm creating a mini Warehouse database to test out all the entity. Below is the code I have done so far.

--create tables, no error over here i assumed
CREATE TABLE VENUE(
VENUE_ID        DECIMAL(3,0) NOT NULL CHECK (VENUE_ID BETWEEN 111 AND 333),
VENUE_FLOOR     INT NOT NULL CHECK (VENUE_FLOOR IN (1,2,3)),
VENUE_SECTION   INT NOT NULL CHECK (VENUE_SECTION IN (1,2,3)),
PRIMARY KEY (VENUE_ID)
)@

CREATE TABLE CLIENT(
    CLIENT_ID       DECIMAL(5,0) NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 10000 INCREMENT BY 1) CHECK (CLIENT_ID BETWEEN 10000 AND 99999),
    CLIENT_FNAME    VARCHAR(30) NOT NULL,
    CLIENT_LNAME    VARCHAR(30) NOT NULL,
    CLIENT_PHONE    VARCHAR(12) NOT NULL,
    CLIENT_ADDRS    VARCHAR(300) NOT NULL,
    PRIMARY KEY (CLIENT_ID),
    CONSTRAINT CLIENT_PHONE_UNQ UNIQUE (CLIENT_PHONE)
)@

CREATE TABLE COURIER(
    COURIER_ID      DECIMAL(3,0) NOT NULL CHECK (COURIER_ID BETWEEN 100 AND 999),
    COURIER_FNAME   VARCHAR(30) NOT NULL,
    COURIER_LNAME   VARCHAR(30) NOT NULL,
    COURIER_PHONE   VARCHAR(12) NOT NULL,
    PRIMARY KEY (COURIER_ID),
    CONSTRAINT COURIER_PHONE_UNQ UNIQUE (COURIER_PHONE)
)@

CREATE TABLE STOCK(
    STOCK_ID        DECIMAL(8,0) NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 10000000 INCREMENT BY 1) CHECK (STOCK_ID BETWEEN 10000000 AND 19999999),
    STOCK_NAME      VARCHAR(20) NOT NULL,
    SUPPLIER_NAME   VARCHAR(50) NOT NULL,
    STOCK_QTY       INT NOT NULL DEFAULT 0,
    VENUE_ID        DECIMAL(3,0) NOT NULL,
    PRIMARY KEY (STOCK_ID),
    FOREIGN KEY (VENUE_ID) REFERENCES VENUE ON DELETE no action,
    CONSTRAINT STOCK_NAME_UNQ UNIQUE (STOCK_NAME),
    CONSTRAINT SUPPLIER_NAME_UNQ UNIQUE (SUPPLIER_NAME)
)@

--CREATE TRIGGER/PROCEDURE CHECK STOCK_QTY - ORDER_QUANTITY >= 100-- 
CREATE TABLE ORDER(
    ORDER_ID        DECIMAL(11,0) NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 10000000000 INCREMENT BY 1) CHECK (ORDER_ID BETWEEN 10000000000 AND 99999999999),
    ORDER_QTY       INT NOT NULL CHECK (ORDER_QTY BETWEEN 10 AND 999),
    CLIENT_ID       DECIMAL(5,0) NOT NULL,
    STOCK_ID        DECIMAL(8,0) NOT NULL,
    PRIMARY KEY (ORDER_ID),
    FOREIGN KEY (CLIENT_ID) REFERENCES CLIENT(CLIENT_ID) ON DELETE no action,
    FOREIGN KEY (STOCK_ID) REFERENCES STOCK(STOCK_ID) ON DELETE no action
)@

CREATE TABLE DELIVERY(
    DELIVERY_ID     DECIMAL(11,0) NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 16900000000 INCREMENT BY 1) CHECK ( DELIVERY_ID BETWEEN 16900000000 AND 16999999999 ),
    ORDER_ID        DECIMAL(11,0) NOT NULL,
    COURIER_ID      DECIMAL(3,0),
    PRIMARY KEY(DELIVERY_ID),
    FOREIGN KEY(ORDER_ID)REFERENCES ORDER(ORDER_ID) ON DELETE no action,
    FOREIGN KEY(COURIER_ID)REFERENCES COURIER(COURIER_ID) ON DELETE no action,
    CONSTRAINT ORDER_ID_DELI_UNQ UNIQUE (ORDER_ID)
)@

--TO SELECT FOR LOG- USE SELECT TIMESTAMP(LOG_ID), ... FROM LOG@
CREATE TABLE LOG(
    LOG_ID          CHAR(13) NOT NULL FOR BIT DATA,
    LOG_PROCESS     VARCHAR(9) NOT NULL CHECK (LOG_PROCESS IN ('Stock-in','Stock-out')),
    LOG_QTY         INT NOT NULL CHECK(LOG_QTY BETWEEN 10 AND 999),
    LOG_DATE        DATE NOT NULL,
    DELIVERY_ID     DECIMAL(11,0),
    STOCK_ID        DECIMAL(8,0) NOT NULL,
    PRIMARY KEY(LOG_ID),
    FOREIGN KEY(DELIVERY_ID)REFERENCES DELIVERY(DELIVERY_ID),
    FOREIGN KEY(STOCK_ID)REFERENCES STOCK(STOCK_ID)
    --CONSTRAINT DELIVERY_ID_LOG_UNQ UNIQUE (DELIVERY_ID)
)@

insert some value to test:

--INSERT INTO TABLE--
INSERT INTO VENUE VALUES (111,1,1)@
INSERT INTO VENUE VALUES (112,2,2)@
INSERT INTO VENUE VALUES (113,3,3)@

INSERT INTO CLIENT VALUES (DEFAULT,'ROAN','CHENG','60189774541','28 JLN APA')@
INSERT INTO CLIENT VALUES (DEFAULT,'AAA','ABC','60101234567','29 JLJ AAA')@
INSERT INTO CLIENT VALUES (DEFAULT,'BBB','BBC','60111234567','30 JLN BBB')@

INSERT INTO COURIER VALUES (100,'DHL','EXP','60355006666')@
INSERT INTO COURIER VALUES (200,'ABX','EXP','60344007777')@
INSERT INTO COURIER VALUES (300,'POS','LAJU','61300882525')@

INSERT INTO STOCK VALUES (DEFAULT,'IPHONE 7','APPLE',100,111)@
INSERT INTO STOCK (STOCK_ID,STOCK_NAME,SUPPLIER_NAME,VENUE_ID) VALUES (10000001,'SAMSUNG S7','SAMSUNG',112)@
INSERT INTO STOCK VALUES (DEFAULT,'MMU','TM',300,113)@
INSERT INTO STOCK VALUES (DEFAULT,'CINEMA','TGV',200,111)@
INSERT INTO STOCK VALUES (DEFAULT,'FOO','FOOCORP',1000,113)@

INSERT INTO ORDER VALUES (DEFAULT,10,10000,10000003)@
INSERT INTO ORDER VALUES (DEFAULT,20,10000,10000002)@
INSERT INTO ORDER VALUES (DEFAULT,30,10002,10000002)@

and the trigger part, with correct order for the cascade layer

--TRIGGER--
-------------------------------------------------------------------------------------------------------

--1)AUTO CREATE DELIVERY AFTER PLACING ORDER (no error)
CREATE TRIGGER REQUEST_DELIVERY
AFTER INSERT ON ORDER
REFERENCING NEW AS N
for each row mode db2sql
INSERT INTO DELIVERY VALUES (
    DEFAULT,
    N.ORDER_ID,
    NULL
)@

--2)UPDATE STOCK QTY AFTER ISSUED A SUCCESSFUL DELIVERY (UPDATE DELIVERY WITH COURIER_ID MEANS PARCEL HAS
--  BEEN MAILED OUT)
--(error here, sqlstate="21000" & "09000",for trigger "...UPDATELOG)
drop trigger UPDATESTOCK_STOCKOUT@
CREATE TRIGGER UPDATESTOCK_STOCKOUT
AFTER UPDATE OF COURIER_ID ON DELIVERY
REFERENCING OLD AS O
FOR EACH ROW MODE DB2SQL
BEGIN
    UPDATE STOCK
    SET STOCK_QTY = STOCK_QTY - (SELECT ORDER_QTY FROM ORDER WHERE ORDER.ORDER_ID = O.ORDER_ID)
    WHERE STOCK_ID IN (SELECT STOCK_ID FROM ORDER WHERE ORDER.ORDER_ID = O.ORDER_ID);
END@


--NOT WORKING EITHER FOR BELOW ALTERNATIVES--
----------------------------------------------------------------------------------------------
CREATE PROCEDURE UPDATEDELIVERY(IN ORD_ID DECIMAL(11,0), COU_ID DECIMAL(3,0))
BEGIN
    UPDATE DELIVERY SET COURIER_ID = COU_ID WHERE ORDER_ID = ORD_ID;
    --UPDATE STOCK SET STOCK_QTY = STOCK_QTY - (SELECT ORDER_QTY FROM ORDER WHERE ORDER.ORDER_ID = ORD_ID);
END@

DROP PROCEDURE UPDATEDELIVERY2@
CREATE PROCEDURE UPDATEDELIVERY2(IN DLV_ID DECIMAL(11,0), COU_ID DECIMAL(3,0))
BEGIN
    UPDATE DELIVERY SET COURIER_ID = COU_ID WHERE DELIVERY_ID = DLV_ID;
    --UPDATE STOCK SET STOCK_QTY = STOCK_QTY - (SELECT ORDER_QTY FROM ORDER WHERE ORDER.ORDER_ID = (SELECT ORDER_ID FROM DELIVERY D WHERE D.DELIVERY_ID = DLV_ID))
    --WHERE STOCK_ID = (SELECT STOCK_ID FROM ORDER O, DELIVERY D WHERE D.DELIVERY_ID = DLV_ID AND D.ORDER_ID = O.ORDER_ID);
END@

---------------------------------------------------------------------------------------------

--3)auto update delivery.COURIER_ID once stock out in log
--(error here, sqlstate="21000" & "09000",for trigger "...UPDATELOG)
drop trigger UPDATELOG@
CREATE TRIGGER UPDATELOG
AFTER UPDATE OF STOCK_QTY ON STOCK
REFERENCING OLD AS O
FOR EACH ROW MODE DB2SQL
BEGIN
    IF O.STOCK_QTY < (SELECT STOCK_QTY FROM STOCK WHERE STOCK_ID = O.STOCK_ID) THEN
        --STOCKIN
        INSERT INTO LOG VALUES (
            GENERATE_UNIQUE(),
            'Stock-in',
            ((SELECT STOCK_QTY FROM STOCK WHERE STOCK_ID = O.STOCK_ID) - O.STOCK_QTY), 
            CURRENT DATE,
            NULL,
            O.STOCK_ID
        );
        --UPDATE STOCK
        --SET STOCK_QTY = STOCK_QTY - (SELECT ORDER_QTY FROM ORDER WHERE ORDER.ORDER_ID = O.ORDER_ID AND ORDER.STOCK_ID = STOCK.STOCK_ID);
    ELSEIF O.STOCK_QTY > (SELECT STOCK_QTY FROM STOCK WHERE STOCK_ID = O.STOCK_ID) THEN
        --STOCKOUT
        INSERT INTO LOG VALUES (
            GENERATE_UNIQUE(),
            'Stock-out',
            (O.STOCK_QTY - (SELECT STOCK_QTY FROM STOCK WHERE STOCK_ID = O.STOCK_ID)), 
            CURRENT DATE,
            (SELECT DELIVERY_ID FROM DELIVERY D, ORDER WHERE D.ORDER_ID = ORDER.ORDER_ID AND ORDER.STOCK_ID = O.STOCK_ID),
            O.STOCK_ID
        );
        END IF;
END@

I read the documentation on the SQLCODE and SQLSTATE for the error, and it says that the error is with the trigger and some select statement which return more than 1 value.

However when tested all the select statement (subqueries and queries) in trigger, it all return only 1 value, which is working without error.

ERROR:
An error occurred in a triggered SQL statement
 in trigger "PCNAME.UPDATELOG". 
Information returned for the error includes SQLCODE "-811", 
SQLSTATE "21000" and message token "", SQLSTATE "09000"

PS: Please help me by guiding me to do the correct thing with explanation instead of giving me just straightforward answer, my brain got stuck in this SQL traffic. If I cant learn anything from this lesson then I'm officially doomed.

Answer 1

I am not sure if I got enverything right from your question but here are some points to consider:

• Your trigger REQUEST_DELIVERY will never fire UPDATESTOCK_STOCKOUT REQUEST_DELIVERY will execute an INSERT and UPDATESTOCK_STOCKOUT is waiting for an update
• Regarding the error with multiple rows returned - your have to guarantee that the only one row is returned. You could try to filter to a single PK value or use fetch first 1 row only or something like that.

Answer 2

Well, the error message is quite explicit: as you correctly said, it complains about a SELECT statement returning more than one row in a context where only a single value is expected, typically in an assignment or scalar comparison. It also tells you that the problem occurs in the trigger named PCNAME.UPDATELOG.

Looking at that trigger's source you can see five subselects used in scalar contexts. Four of them select from STOCK by STOCK_ID, which is the primary key and therefore returns only one row by definition. Therefore, it's the remaining statement:

SELECT DELIVERY_ID FROM DELIVERY D, ORDER 
  WHERE D.ORDER_ID = ORDER.ORDER_ID AND ORDER.STOCK_ID = O.STOCK_ID

that is likely causing the issue. Since you are joining ORDER and DELIVERY by ORDER_ID, this query will return as many rows as there are deliveries for a particular order.