Re: Trigger for a Newbie

Assuming that the "enroller" is the user that executes the enrolled procedure, you can use something like that:

connect /
create table student (id number, first_name varchar2(20),

                      last_name varchar2(20), date_enrolled date);
create table new_student (id number, first_name varchar2(20),
                          last_name varchar2(20), date_enrolled date,
                          enroled_by varchar2(20));

create or replace trigger tai_student after insert on student for each row
declare
  currentuser all_users.username%type;
begin
  select username into currentuser from all_users where user_id = uid;   insert into new_student values (:new.id, :new.first_name, :new.last_name,

                                  :new.date_enrolled, currentuser);

create or replace procedure enrol_student (

   pid number, pfirst varchar2, plast varchar2) is
begin

   insert into student values (pid, pfirst, plast, sysdate); end;
/

create public synonym enrol_student for enrol_student;

v734> connect system
Connected.

v734> exec enrol_student (1,'Black','Root'); PL/SQL procedure successfully completed.

connect /
Connected.

v734> select * from student;

        ID FIRST_NAME LAST_NAME DATE_ENRO

---------- -------------------- -------------------- ---------
         1 Black                Root                 02-03-00

1 row selected.

v734> select * from new_student;

        ID FIRST_NAME LAST_NAME DATE_ENRO ENROLED_BY

---------- -------------------- -------------------- --------- -----------------
---
         1 Black                Root                 02-03-00  SYSTEM

1 row selected.

--
Have a nice day
Michel


Black Rook <blackrook_at_darkhorsefan.net> a écrit dans le message :
s9hk1887er2121_at_corp.supernews.com...


> I am attempting to create a trigger that will be part of a package.


>

> What I would like this trigger to do is enter each new students enrolled into

> the new student table after insert as well as the date_enrolled and who

> enrolled the student. Can anyone offer help towards a solution?

>

> STUDENT

> ID                                       NOT NULL NUMBER

> FIRST_NAME                      NOT NULL VARCHAR2(20)

> LAST_NAME                       NOT NULL VARCHAR2(20)

> MAJOR                               NOT NULL VARCHAR2(20)

> DATE_ENROLLED               NOT NULL DATE

>

> NEW_STUDENT

> ID                                       NOT NULL NUMBER(10)

> FIRST_NAME                      NOT NULL VARCHAR2(20)

> LAST_NAME                       NOT NULL VARCHAR2(20)

> MAJOR                               NOT NULL VARCHAR2(20)

> DATE_ENROLLED               NOT NULL DATE

> ENROLLED_BY                   NOT NULL VARCHAR2(20)