The out parameter contains the following Statement :
CREATE TRIGGER TEST AFTER INSERT ON DEPT REFERENCING NEW AS NEWTFOR EACH ROW MODE DB2SQL BEGIN
DECLARE HSQLCODE INTEGER DEFAULT 0 ;
DECLARE HSQLSTATE CHAR ( 5 ) DEFAULT '00000' ;
DECLARE SQLCODE INTEGER ;
DECLARE SQLSTATE CHAR ( 5 ) ;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION , SQLWARNING , NOT FOUND
VALUES ( SQLCODE , SQLSTATE ) INTO HSQLCODE , HSQLSTATE ;
INSERT INTO DEPTAUDIT(DNO, DNAME, LOC)
VALUES ( NEW.DNO, NEW.DNAME, NEW.LOC)
insert into log values(HSQLCODE , HSQLSTATE);
When I call the procedure CALL TESTPROC(trigname,?) I expect my program to create the TEST trigger and if any error occurs then put the sqlcode and sqlstate in a log table.
How can we execute the statement of the out parameter. Do i have to write another procedure to do this ? Write an SQL to call use the outparameter to create the trigger ? Please help .
When I call the procedure CALL TESTPROC(trigname,?) the procedure runs fine but the trigger does not create nor gives an sqlcode or sqlstate. I checked out and found that the DEPTAUDIT table was not existing. So i created it . even then the trigger is not created.
So i figure out the the CREATE TRIGGER statement is in the out parameter but is not executing . How to execute this CREATE TRIGGER Statement ?