General application design question

[KitJackson]

CREATE SEQUENCE EMP_SEQ;

CREATE TABLE EMP(
EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
LAST_UPDATE DATE,
LAST_USER VARCHAR2(30));

These tables are maintained using stored procedures of the form

CREATE PROCEDURE EMP_ADD(
V_EMPNO NUMBER,
V_ENAME VARCHAR2,
V_JOB VARCHAR2) AS
V_SEQ NUMBER;
BEGIN
IF V_EMPNO IS NULL THEN
SELECT EMP_SEQ.NEXTVAL INTO V_SEQ FROM DUAL;
INSERT ITNO EMP
(EMPNO, ENAME, JOB, LAST_UPDATE, LAST_USER)
VALUES
(V_SEQ, V_ENAME, V_JOB, SYSDATE, USER);
ELSE
UPDATE EMP SET
ENAME = V_ENAME,
JOB = V_JOB,
LAST_UPDATE = SYSDATE,
LAST_USER = USER
WHERE EMPNO = V_EMPNO;
END IF;
COMMIT;
END;

Has anybody got any general advice on the design of the application to maintain these tables? There are no triggers in this database. I have control of the stored procedures but not of the database design and I can't add triggers. I want to simplify the Delphi application and perhaps do away with the use of these stored procedures but need to make sure that the last_update and last_user fields are maintained and displayed properly.

Thanks
Kit Jackson

 

[Marco Kalter]

The DeptAPI example project demonstrates such an application design. It uses a package to select, insert, delete, update, and lock. The insert function applies a sequence value, which is returned to the dataset.

------------------
Marco Kalter
Allround Automations