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 [email protected]
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
-~----------~----~----~----~------~----~------~--~---
