Re: Trigger Help

From: Quinton McCombs <quintonm_at_bellsouth.net>
Date: 1998/02/20
Message-ID: <34EDA3B0.A19B5E97@bellsouth.net>#1/1

create or replace trigger employee_before_ins_row before insert on EMPLOYEE
for each row
begin
  select empl_seq.nextval
  into :new.seq_num
  from dual;
end;

create or replace trigger employee_after_ins_row after insert on EMPLOYEE
for each row
begin
insert into employee_checklist values (:new.seq_num,

0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0);

insert into employee_accounts values (:new.seq_num, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
end;

I would advise you to specify the columns on the inset to protect you for column changes.

insert into employee_accounts
( col1, col2, col3, col4, col5, col6, col7, col8, col9, col10,   col11, col12)
values (:new.seq_num,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0);

Steven C Job wrote:
>
> I know this can't be too tough, but I just can't seem to get
> it done.
>
> I have a sequence called empl_seq.
> When I insert into the the table (employee) I want it to put
> the sequence number into the employee table, and then insert the
> sequence number again in two other tables, with extra data.
>
> So here is the before insert trigger which does NOT work.
>
> create or replace trigger employee_before_ins_row
> before insert on EMPLOYEE
> for each row
> begin
> :new.seq_num := :empl_seq.nextval;
> end;
>
> Here is the trigger which does work. This one takes the seq_num
> and makes the relationship in two other tables.
>
> create or replace trigger employee_after_ins_row
> after insert on EMPLOYEE
> for each row
> begin
> insert into employee_checklist values (:new.seq_num,
> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
> insert into employee_accounts values (:new.seq_num,
> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
> end;
>
> Any help here would be extremely appreciated....
>
> Thank you, thank you, thank you....
>
> -Steven Job
  Received on Fri Feb 20 1998 - 00:00:00 CST