Re: Check for no more than two entries of each value in a column?
Date: Fri, 17 Apr 2009 11:37:48 -0700 (PDT)
Message-ID: <7dcd1716-486c-4427-8cea-8ed1b0f457e9_at_21g2000vbk.googlegroups.com>
On Apr 17, 12:38 pm, livefreeordie <jpittm..._at_gmail.com> wrote:
> Hi,
>
> I need to create an app where any given employee may nominate up to
> TWO other employees per year for an award. This number could change.
>
> Now I could create a table like this:
>
> PK, U1 nominator (varchar2(12))
> PK, U1 year (int)
> U1 nomination1_id (int)
> U1 nomination2_id (int)
>
> Then another table:
> PK nomination_id (int)
> nominee (varchar2(12))
> nomination_data ....
>
> However, this would force me to update any Views or Code every time
> the number of allowed nominations changes.
>
> I'd much rather do this:
>
> PK nomination_id (int)
> year (int)
> nominator (varchar2(12))
> nominee (varchar2(12))
> nomination_data ...
>
> And have some kind of Check Constraint to only allow two nominees per
> nominator per year. But I don't know how to do that.
>
> Thoughts?
>
> ~ Jamie
Yes, you don't need or want a check constraint, you want a stored procedure called from an application to do the processing:
SQL> create table nominations(
2 nomination_id number, 3 year number, 4 nominator varchar2(12), 5 nominee varchar2(12), 6 nomination_data varchar2(255)7 );
Table created.
SQL>
SQL> create sequence nomseq
2 start with 1 increment by 1 nomaxvalue nocycle nocache order;
Sequence created.
SQL>
SQL> create or replace trigger pop_nom_id
2 before insert on nominations
3 for each row
4 begin
5 select nomseq.nextval 6 into :new.nomination_id 7 from dual;
8 end;
9 /
Trigger created.
SQL>
SQL> create or replace procedure nominate_emp(
2 p_nominee varchar2, 3 p_nominator varchar2, 4 p_reason varchar2
5 )
6 as
7
8 nom_ct number:=0; 9 too_many_noms exception; 10 pragma exception_init(too_many_noms, -20009);11
12 begin
13 select count(*) 14 into nom_ct 15 from nominations 16 where nominator = p_nominator; 17 18 if nom_ct < 2 then 19 insert into nominations 20 (year, nominator, nominee, nomination_data) 21 values 22 (to_number(to_char(sysdate, 'RRRR')), 23 p_nominator, 24 p_nominee, 25 p_reason); 26 27 commit; 28 29 else 30 raise too_many_noms; 31 end if;
32
33 exception
34 when too_many_noms then 35 raise_application_error(-20009, 'Too many nominations foruser '||p_nominator);
36
37 when others then
38 -- Output desired error message
39 dbms_output.put_line('-20999: Sumthin'' bad happened --
error stack follows');
40 -- Output actual line number of error source
41 dbms_output.put(dbms_utility.format_error_backtrace);
42 -- Output the actual error number and message
43 dbms_output.put_line(dbms_utility.format_error_stack);
44
45 end;
46 /
Procedure created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> exec nominate_emp(user, user, 'Because I am worth it')
PL/SQL procedure successfully completed.
SQL> exec nominate_emp('LARDBUCKET', user, 'Because he is worth it')
PL/SQL procedure successfully completed.
SQL> exec nominate_emp('ZING', user, 'Because') BEGIN nominate_emp('ZING', user, 'Because'); END;
*
ERROR at line 1:
ORA-20009: Too many nominations for user BING ORA-06512: at "BING.NOMINATE_EMP", line 35 ORA-06512: at line 1
SQL>
SQL> select
2 nomination_id,
3 year,
4 nominator,
5 nominee,
6 nomination_data
7 from nominations;
NOMINATION_ID YEAR NOMINATOR NOMINEE NOMINATION_DATA ------------- ---------- ------------ ------------
1 2009 BING BING Because I am worth it
2 2009 BING LARDBUCKET Because he is worth it
SQL> With the commit inside the procedure you *should* be able to keep to the two nominations maximum per nominator (of course depending upon how active this nomination process can be with multiple users you may have a few slip through the cracks).
David Fitzjarrell Received on Fri Apr 17 2009 - 13:37:48 CDT