Maybe I missed something here, but why not just make it AFTER INSERT? just
do...
create or replace trigger checkDepartment
AFTER insert on dept for each row
pragma autonomous_transaction;
begin
INSERT INTO dept_audit
(deptno, dname, loc, tot_emps, tot_sals)
VALUES
(:new.deptno, :new.dname, :new.loc, :new.tot_emps,
:new.tot_sals);
commit;
end;
/
If deptno is a primary key, and you attempt to insert a duplicate dept, this
trigger will not get executed.
On Mon, Oct 6, 2008 at 1:09 PM, JNewMember <[EMAIL PROTECTED]> wrote:
>
> Thanks alot Fitz, This is really helpful while waiting for the help I
> worked on the fallowing but I will go over the solution that you
> publish and see what I can extract to enhance what I created... once
> again thanks for the detail explanation.
>
> create or replace trigger checkDepartment
> before insert on dept for each row
> declare vDeptNumber number;
> pragma autonomous_transaction;
> begin
> check_dno(:new.deptno, vDeptNumber);
> if vDeptNumber != -1 then
> INSERT INTO dept_audit
> (deptno, dname, loc, tot_emps, tot_sals)
> VALUES
> (:new.deptno, :new.dname, :new.loc, :new.tot_emps,
> :new.tot_sals);
> commit;
> else
> raise_application_error(-20300, 'Value already exists');
> end if;
> end;
> /
>
> On Oct 6, 1:51 pm, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote:
> > On Oct 5, 1:31 pm, JNewMember <[EMAIL PROTECTED]> wrote:
> >
> >
> >
> > > I want to Write a trigger that when a new department is added, Checks
> > > the uniqueness of the DEPTNO using the stored procedure or functions
> > > that I have written, If the deptno value is unique(doesn't exists)
> > > then it adds the department to the table
> >
> > > I have fallowing procedure
> > > create or replace procedure check_dno (deptNum number, returnValue out
> > > number)
> > > is
> > > v_deptNum number;
> > > begin
> > > select deptno into v_deptNum from dept where deptno = deptNum;
> > > if deptNum = v_deptNum then
> > > returnValue := -1;
> > > else
> > > returnValue := 1;
> > > end if;
> > > exception
> > > when NO_DATA_FOUND THEN
> > > dbms_output.put_line('Department Number does not exists');
> > > returnValue := 1;
> > > end check_dno;
> > > /
> >
> > > I started on writing the trigger like this...
> > > create or replace trigger checkDepartment
> > > after insert on dept
> > > for each row
> > > declare vDeptNumber number;
> > > begin
> > > check_dno(:new.deptno, vDeptNumber)
> >
> > > I am not sure how to go further...
> >
> > > Thanks,
> >
> > You don't, plainly and simply put, as it won't work:
> >
> > 13:36:38 SQL> --
> > 13:36:38 SQL> -- Let's create a procedure to check the uniqueness of
> > department numbers
> > 13:36:38 SQL> --
> > 13:36:38 SQL> -- Writing this, of course, is a mistake
> > 13:36:38 SQL> --
> > 13:36:38 SQL> create or replace procedure check_dno (deptNum number,
> > returnValue out
> > 13:36:38 2 number)
> > 13:36:38 3 is
> > 13:36:38 4 v_deptNum number; -- Variable is NULL by default
> > 13:36:38 5 begin
> > 13:36:38 6 select deptno into v_deptNum from dept where deptno =
> > deptNum;
> > 13:36:38 7 if deptNum = v_deptNum then -- of course if nothing is
> > returned ...
> > 13:36:38 8 returnValue := -1;
> > 13:36:38 9 else
> > 13:36:38 10 --
> > 13:36:38 11 -- This procedure will never execute this line
> > of code
> > 13:36:38 12 -- because nothing equals NULL
> > 13:36:38 13 --
> > 13:36:38 14 returnValue := 1;
> > 13:36:38 15 end if;
> > 13:36:38 16 exception
> > 13:36:38 17 when NO_DATA_FOUND THEN
> > 13:36:38 18 dbms_output.put_line('Department Number does not
> > exists');
> > 13:36:38 19 returnValue := 1;
> > 13:36:38 20 end check_dno;
> > 13:36:38 21 /
> >
> > Procedure created.
> >
> > 13:36:38 SQL>
> > 13:36:38 SQL> --
> > 13:36:38 SQL> -- Fixing the procedure is simple
> > 13:36:38 SQL> --
> > 13:36:38 SQL> -- Writing it is still a mistake, though
> > 13:36:38 SQL> --
> > 13:36:38 SQL> create or replace procedure check_dno (deptNum number,
> > returnValue out
> > 13:36:38 2 number)
> > 13:36:38 3 is
> > 13:36:38 4 v_deptNum number;
> > 13:36:38 5 begin
> > 13:36:38 6 select deptno into v_deptNum from dept where deptno =
> > deptNum;
> > 13:36:38 7 if deptNum = v_deptNum then
> > 13:36:38 8 returnValue := -1;
> > 13:36:38 9 end if;
> > 13:36:38 10 exception
> > 13:36:38 11 when NO_DATA_FOUND THEN
> > 13:36:38 12 returnValue := 1;
> > 13:36:38 13 end check_dno;
> > 13:36:38 14 /
> >
> > Procedure created.
> >
> > 13:36:38 SQL>
> > 13:36:38 SQL> --
> > 13:36:38 SQL> -- Now let's write a trigger to use that procedure
> > 13:36:38 SQL> --
> > 13:36:38 SQL>
> > 13:36:38 SQL> create or replace trigger chk_deptno
> > 13:36:38 2 before insert on dept
> > 13:36:38 3 for each row
> > 13:36:38 4 declare
> > 13:36:38 5 retVal number;
> > 13:36:38 6 begin
> > 13:36:38 7 check_dno(:new.deptno, retVal);
> > 13:36:38 8 if retVal = -1 then
> > 13:36:38 9 raise_application_error(-20001,
> > 'Department number '||:new.deptno||' exists.');
> > 13:36:38 10 end if;
> > 13:36:38 11 end;
> > 13:36:38 12 /
> >
> > Trigger created.
> >
> > 13:36:38 SQL>
> > 13:36:38 SQL> --
> > 13:36:38 SQL> -- Now let's check to see if this really works
> > 13:36:38 SQL> --
> > 13:36:38 SQL>
> > 13:36:38 SQL> --
> > 13:36:38 SQL> -- We'll try this from two separate sessions
> > 13:36:38 SQL> -- and see if the changes from session #1 can be
> > 13:36:38 SQL> -- 'seen' by session #2
> > 13:36:38 SQL> --
> > 13:36:38 SQL> -- They can't, but, let's prove that
> > 13:36:38 SQL> --
> > 13:36:38 SQL>
> > 13:36:38 SQL> --
> > 13:36:38 SQL> -- Session #1
> > 13:36:38 SQL> --
> > 13:36:38 SQL>
> > 13:36:38 SQL> insert into dept
> > 13:36:38 2 values (80, 'RECYCLING','BERMUDA');
> >
> > 1 row created.
> >
> > 13:36:38 SQL>
> > 13:36:38 SQL> exec dbms_lock.sleep(60)
> >
> > PL/SQL procedure successfully completed.
> >
> > 13:37:38 SQL>
> > 13:37:38 SQL> commit;
> >
> > Commit complete.
> >
> > 13:37:38 SQL>
> > 13:37:38 SQL> select *
> > 13:37:38 2 From dept
> > 13:37:38 3 order by deptno;
> >
> > DEPTNO DNAME LOC
> > ---------- --------------- -------------
> > 10 ACCOUNTING NEW YORK
> > 20 RESEARCH DALLAS
> > 30 SALES CHICAGO
> > 40 OPERATIONS BOSTON
> > 50 HUMAN RESOURCES DULUTH
> > 60 DATA SERVICES KANSAS CITY
> > 70 MARKETING DULUTH
> > 80 RECYCLING BERMUDA
> >
> > 8 rows selected.
> >
> > 13:37:38 SQL>
> > 13:37:38 SQL> --
> > 13:37:38 SQL> -- Just one DEPT 80 here
> > 13:37:38 SQL> --
> > 13:37:38 SQL>
> >
> > Noting the time of the second insert is important:
> >
> > 13:36:50 SQL> --
> > 13:36:50 SQL> -- Session #2
> > 13:36:50 SQL> --
> > 13:36:50 SQL>
> > 13:36:50 SQL> insert into dept
> > 13:36:50 2 values (80, 'RECYCLING','BERMUDA'); -- this insert
> > occurs 12 seconds after the first, but
> >
> > -- BEFORE the first session commits its work
> > 1 row created.
> >
> > 13:36:50 SQL>
> > 13:36:50 SQL> exec dbms_lock.sleep(60);
> >
> > PL/SQL procedure successfully completed.
> >
> > 13:37:50 SQL>
> > 13:37:50 SQL> commit;
> >
> > Commit complete.
> >
> > 13:37:50 SQL>
> > 13:37:50 SQL> select *
> > 13:37:50 2 From dept
> > 13:37:50 3 order by deptno;
> >
> > DEPTNO DNAME LOC
> > ---------- --------------- -------------
> > 10 ACCOUNTING NEW YORK
> > 20 RESEARCH DALLAS
> > 30 SALES CHICAGO
> > 40 OPERATIONS BOSTON
> > 50 HUMAN RESOURCES DULUTH
> > 60 DATA SERVICES KANSAS CITY
> > 70 MARKETING DULUTH
> > 80 RECYCLING BERMUDA
> > 80 RECYCLING BERMUDA
> >
> > 9 rows selected.
> >
> > 13:37:50 SQL>
> > 13:37:50 SQL> --
> > 13:37:50 SQL> -- but TWO DEPT 80's here
> > 13:37:50 SQL> --
> > 13:37:50 SQL> -- The trigger failed to do the intended job
> > 13:37:50 SQL> --
> > 13:37:50 SQL>
> >
> > And it will always fail under those conditions. Why you feel it
> > necessary to replace a primary key or unique constraint (which works)
> > with a trigger that won't is a mystery. Had a primary key been in
> > place
> > on the DEPTNO column of the DEPT table the first insert would have
> > succeeded and the second would have failed:
> >
> > 13:45:25 SQL> alter table dept
> > 13:45:30 2 add constraint dept_pk primary key(deptno);
> >
> > Table altered.
> >
> > 13:45:40 SQL> insert into dept
> > 13:45:55 2 values (80, 'RECYCLING','BERMUDA');
> >
> > 1 row created.
> >
> > 13:45:56 SQL> commit;
> >
> > Commit complete.
> >
> > 13:46:11 SQL>
> >
> > 13:37:50 SQL> insert into dept
> > 13:46:02 2 values (80, 'RECYCLING','BERMUDA');
> > insert into dept
> > *
> > ERROR at line 1:
> > ORA-00001: unique constraint (BING.DEPT_PK) violated
> >
> > 13:46:11 SQL>
> >
> > No trigger, no procedure necessary, and no running into the read
> > consistency mechanism Oracle employs (readers don't block writers and
> > writers don't block readers) that caused your trigger/procedure
> > implementation to fail.
> >
> > The Concepts Guide is a wonderful place to begin when trying to
> > understand Oracle and how it works.
> >
> > David Fitzjarrell
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
[EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
