--- In ib-support@y..., Derek Basch <dbasch@y...> wrote:
Dear Derek,
If I understand you you can do like this
CREATE GENERATOR GET_MY_ID;
SET GENERATOR GET_MY_ID TO 6;
CREATE TABLE T_CAR_TITLE (
PK_CAR_ID INTEGER NOT NULL,
CAR_TITLE VARCHAR(10));
CREATE TABLE T_EMP_INFO (
PK_EMP_ID INTEGER NOT NULL,
FK_JOB_ID INTEGER NOT NULL,
FK_CAR_ID INTEGER NOT NULL,
FNAME VARCHAR(10),
LNAME VARCHAR(10));
CREATE TABLE T_JOB_TITLE (
PK_JOB_ID INTEGER NOT NULL,
JOB_TITLE VARCHAR(10));
CREATE VIEW V_EMP_INFO(
PK_EMP_ID,
FNAME,
LNAME,
PK_CAR_ID,
CAR_TITLE,
PK_JOB_ID,
JOB_TITLE)
AS
SELECT e.PK_EMP_ID,
e.FNAME,
e.LNAME,
c.PK_CAR_ID,
c.CAR_TITLE,
j.PK_JOB_ID,
j.JOB_TITLE
FROM T_EMP_INFO e
join T_JOB_TITLE j on j.PK_JOB_ID=e.FK_JOB_ID
join T_CAR_TITLE c on c.PK_CAR_ID=e.FK_CAR_ID
;
ALTER TABLE T_CAR_TITLE ADD PRIMARY KEY (PK_CAR_ID);
ALTER TABLE T_EMP_INFO ADD PRIMARY KEY (PK_EMP_ID);
ALTER TABLE T_JOB_TITLE ADD PRIMARY KEY (PK_JOB_ID);
SET TERM ^ ;
CREATE TRIGGER V_EMP_INFO_BI0 FOR V_EMP_INFO
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
POST_EVENT 'DUMMY_EVENT';
END
^
ALTER TRIGGER V_EMP_INFO_BI0
as
DECLARE VARIABLE car_id integer;
DECLARE VARIABLE job_id integer;
DECLARE VARIABLE emp_id integer;
begin
IF (new.PK_emp_ID is null) THEN
new.PK_emp_ID=gen_id(get_my_id,1);
IF ((new.PK_CAR_ID is null) OR (new.PK_CAR_ID=0)) THEN
begin
car_id=null;
select c.pk_car_id from t_car_title c
where c.car_title=new.car_title
into :car_id;
IF (car_id is null) THEN
begin
CAR_ID=gen_id(get_my_id,1);
insert into t_car_title(pk_car_id,car_title)
values(:car_id,new.car_title);
end
end
else
car_id=new.pk_car_id;
IF ((new.PK_JOB_ID is null) OR (new.PK_JOB_ID=0)) THEN
begin
job_id=null;
select j.pk_job_id from t_job_title j
where j.job_title=new.job_title
into :job_id;
IF (job_id is null) THEN
begin
job_ID=gen_id(get_my_id,1);
insert into t_job_title(pk_job_id,job_title)
values(:job_id,new.job_title);
end
end
else
job_id=new.pk_job_id;
emp_id=gen_id(get_my_id,1);
insert into t_emp_info(pk_emp_id,fk_car_id,fk_job_id,lname,fname)
values(:emp_id,:car_id,:job_id,new.lname,new.fname);
end
^
SET TERM ; ^
This is only a basic idea and you can add some funcionaly you want.