Insert into from select with multiple columns and multiple tables

Question

I need to create a SQL Trigger with Insert Statement selecting multiple columns from different tables . Something like this:

CREATE OR REPLACE TRIGGER ACTIV_TRG 
AFTER INSERT ON TRANS_LOG
REFERENCING NEW AS TR  
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
    INSERT INTO ACTIVITIES values ( 
    (SELECT MAX(SEQNBR) FROM ACTIV_LOG where TRAN_ID = 'XXX'),
    TR.ACCOUNT,
    TR.EVENT, 
    SELECT TRAN_ID ,
    TRAN_DT ,
    TRAN_LN  FROM ACTIV_DEL where TRAN_ID = 'XXX',
    CURRENT TIMESTAMP
)
END;

The issue is when I tried to select more than one column in the sub query. I know if I separate the Sub Query like this :

(TRAN_ID  FROM ACTIV_DEL where TRAN_ID = 'XXX'),
(TRAN_DT  FROM ACTIV_DEL where TRAN_ID = 'XXX'),
(TRAN_LN  FROM ACTIV_DEL where TRAN_ID = 'XXX'),

will work but definitely there must be a better and efficient way to do this.

Thanks in advance.

Definitely, there must be a better solution than this:

CREATE OR REPLACE TRIGGER ACTIV_TRG 
AFTER INSERT ON TRANS_LOG
REFERENCING NEW AS TR  
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
   INSERT INTO ACTIVITIES values ( 
   (SELECT MAX(SEQNBR) FROM ACTIV_LOG where TRAN_ID = 'XXX'),
   TR.ACCOUNT, ---> FIELD FROM TRANS_LOG (TRIGGER SOURCE)
   TR.EVENT,   ---> FIELD FROM TRANS_LOG (TRIGGER SOURCE)
   SELECT TRAN_ID  FROM ACTIV_DEL where TRAN_ID = 'XXX',  ---> REPETING SELECT
   SELETC TRAN_DT  FROM ACTIV_DEL where TRAN_ID = 'XXX',  ---> REPETING SELECT
   SELECT TRAN_LN  FROM ACTIV_DEL where TRAN_ID = 'XXX',  ---> REPETING SELECT
   CURRENT TIMESTAMP
)
END;

Cherry Blossom Girl · Answer 1 · 2017-06-23T18:47:29.720

1

I would do the insert with the following select:

    SELECT (SELECT MAX(SEQNBR) FROM ACTIV_LOG where TRAN_ID = 'XXX'),
        TR.ACCOUNT,
        TR.EVENT,
        TRAN_ID ,
        TRAN_DT ,
        TRAN_LN,
        CURRENT TIMESTAMP
        FROM ACTIV_DEL
        where TRAN_ID = 'XXX'

edited Jun 23 '17 at 18:47

answered Jun 23 '17 at 15:57

Cherry Blossom Girl

503
5
8

Thanks Mary but the thing is that I also need 3 columns from another sub Query: SELECT TRAN_ID , TRAN_DT , TRAN_LN FROM ACTIV_DEL where TRAN_ID = 'XXX', CURRENT TIMESTAMP – Bill_Buckner Jun 23 '17 at 18:35
@Bill_Buckner I think this covers perfectly your issue, since it is doing a select to the table ACTIV_DEL – Cherry Blossom Girl Jun 23 '17 at 18:51
Mary- but the thing is that I can't use the insert in that way because table ACTIV_DEL might not has record and I'm still need to insert the values of the table(TRANS_LOG ) affected in the trigger. – Bill_Buckner Jun 29 '17 at 13:33
@Bill_Buckner I would check if the value exists in ACTIV_DEL if it doesn't exist I would insert a null value in those fields instead of doing the select (check if exists [link](https://stackoverflow.com/questions/6922048/db2-for-ibm-iseries-if-exists-statement-syntax)) – Cherry Blossom Girl Jun 29 '17 at 17:02
That's a good approach but we are running in DB2 and does not support conditional INSERT. – Bill_Buckner Jun 29 '17 at 18:15

score 0 · Answer 2 · answered Jun 25 '17 at 10:08

0

Something like this?

CREATE OR REPLACE TRIGGER ACTIV_TRG 
AFTER INSERT ON TRANS_LOG
REFERENCING NEW AS TR  
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
    INSERT INTO ACTIVITIES 
        SELECT (SELECT MAX(f0.SEQNBR) FROM ACTIV_LOG f0 where f0.TRAN_ID = f1.TRAN_ID),
        TR.ACCOUNT,
        TR.EVENT,
        f1.TRAN_ID ,
        f1.TRAN_DT ,
        f1.TRAN_LN,
        CURRENT TIMESTAMP
        FROM ACTIV_DEL f1
        where f1.TRAN_ID = 'XXX'

END;

answered Jun 25 '17 at 10:08

Esperento57

16,521
3
39
45

The issue is that table ACTIV_DEL might not has record and I need to insert the values of the table(TRANS_LOG ) affected in the trigger no matter if can't get TRAN_ID ,TRAN_LN and TRAN_ID from ACTIV_DEL table. – Bill_Buckner Jun 29 '17 at 13:53

Insert into from select with multiple columns and multiple tables

I need to create a SQL Trigger with Insert Statement selecting multiple columns from different tables . Something like this:

2 Answers2