Stored Procedure with Commit and Rollback
Hi,
I'm Informix Newbies. I want to insert into 2 table. Parent table & Child table
I've table as following,
CREATE TABLE teststudent (
id serial NOT NULL,
firstname varchar(100) NOT NULL,
lastname varchar(100) NOT NULL,
enrollmentdate date NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE teststudentdetail (
id serial NOT NULL,
teststudentid integer NOT NULL,
remark varchar(100) NOT NULL,
PRIMARY KEY (id)
);
ALTER TABLE teststudentdetail
ADD CONSTRAINT FOREIGN KEY (teststudentid)
REFERENCES teststudent (id);Stored Procedure is following,
CREATE PROCEDURE teststudent_AddStudent (p_firstname varchar(100), p_lastname varchar(100), p_enrollmentdate date, p_remark varchar(100))
BEGIN WORK;BEGIN
ON EXCEPTION
ROLLBACK WORK;
END EXCEPTION;
INSERT INTO teststudent
(firstname, lastname, enrollmentdate)
VALUES
(p_firstname, p_lastname, p_enrollmentdate);-- <inserted(id)>
select dbinfo('sqlca.sqlerrd1') from sysmaster:sysdual;
INSERT INTO teststudentdetail
(teststudentid, remark)
VALUES
(<inserted(id)>
, p_remark);
COMMIT WORK;
END;
END PROCEDURE
I need help to get <inserted(id)> from
select dbinfo('sqlca.sqlerrd1') from sysmaster:sysdual;
, and this teststudent(id) will be inserted into Child Table as following,
INSERT INTO teststudentdetail
(teststudentid, remark)
VALUES
(<inserted(id)>
, p_remark);
How to fix and complete my Stored Procedure ? Please help
------------------------------
Dalia Hudson
------------------------------
#Informix
CREATE PROCEDURE teststudent_AddStudent (p_firstname varchar(100), p_lastname varchar(100), p_enrollmentdate date, p_remark varchar(100))define newid integer;
BEGIN WORK;
BEGIN
ON EXCEPTION
ROLLBACK WORK;
END EXCEPTION;
INSERT INTO teststudent
(firstname, lastname, enrollmentdate)
VALUES
(p_firstname, p_lastname, p_enrollmentdate);-- <inserted(id)>
let newid=dbinfo('sqlca.sqlerrd1');
INSERT INTO teststudentdetail
(teststudentid, remark)
VALUES
(newid
, p_remark);
COMMIT WORK;
END;
END PROCEDURE
------------------------------
Mike Walker
xDB Systems, Inc
www.xdbsystems.com
------------------------------