Wouldnt this be better (far more efficiently) done by way of a unique
index on deptno? If not, why not?
JNewMember 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 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
-~----------~----~----~----~------~----~------~--~---
