Hi DB2 experts,
can you help me to understand the reason of the following failure, does the DB2 V9.1 support the INSTEAD OF in the CREATE TRIGGER?
CONNECT;
*** CONNECT successful, SERVER_NAME='BYDB2LABE', PRODUCT_ID='DSN09010'
*** SERVER_CLASS='QDB2 for DB2 UDB for z/OS'
--CREATE VIEW V2IBAVM.WIPINVRUL5
-- (RULE_TYPE,ACTIVE_IND,PN,RECV_LOC)
-- AS SELECT RULE_TYPE,ACTIVE_IND,PN,CHAR(SUBSTR(FAMILY,1,4),4)
-- FROM V2IBAVM.TOM_RULES
-- WHERE RULE_TYPE = 'WIPINVRULE';
--COMMIT;
CREATE TRIGGER WIP_INSERT INSTEAD OF INSERT ON V2IBAVM.WIPINVRUL5
REFERENCING NEW AS NEWWIP FOR EACH ROW MODE DB2SQL
INSERT INTO V2IBAVM.TOM_RULES
(RULE_TYPE,ACTIVE_IND,PN,FAMILY)
VALUES(NEWWIP.RULE_TYPE,NEWWIP.ACTIVE_IND,
NEWWIP.PN,NEWWIP.RECV_LOC);
*DB2error(s) on PREPARE STMNT2:
*DB2error#1* SQLCODE = -4700
*DB2error#1* ATTEMPT TO USE NEW FUNCTION BEFORE NEW FUNCTION MODE
*DB2error#1* SQLSTATE = 56038
56038 │ The requested feature is not supported in this environment. │ -4700, │
does the DB2 V9.1 support the INSTEAD OF in the CREATE TRIGGER?
│
Yes DB2 V9 supports INSTEAD OF TRIGGER......
CREATE VIEW EMP_DEPT (EMPNO, FIRSTNME, MIDINIT, LASTNAME,
PHONENO, HIREDATE, DEPTNAME)
AS SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, PHONENO,
HIREDATE, DEPTNAME
FROM EMP, DEPT
WHERE EMP.WORKDEPT = DEPT.DEPTNO;
CREATE TRIGGER E_D_ISRT
INSTEAD OF INSERT ON EMP_DEPT
REFERENCING NEW AS NEWEMP
FOR EACH ROW INSERT INTO EMPLOYEE
(EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE)
VALUES
(EMPNO, FIRSTNME, MIDINIT, LASTNAME,
COALESCE
((SELECT DEPTNO FROM DEPT AS D WHERE D.DEPTNAME = NEWEMP.DEPTNAME),
RAISE_ERROR('70001', 'Unknown dept name')
),
PHONENO, HIREDATE);
Hope this would work fine..........
Correct me if i were wrong.....
