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
-~----------~----~----~----~------~----~------~--~---
