Re: Check for no more than two entries of each value in a column?
Date: Fri, 17 Apr 2009 11:46:16 -0700 (PDT)
Message-ID: <60ebcd57-c137-4260-aabc-ebe29de0d2d8_at_n4g2000vba.googlegroups.com>
On Apr 17, 1:37 pm, ddf <orat..._at_msn.com> wrote:
> 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 for
> user '||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- Hide quoted text -
>
> - Show quoted text -
Gee, I forgot the year in the count(*) calculation. :( Here's a corrected copy:
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> insert into nominations
2 (year, nominator, nominee, nomination_data)
3 values
4 (to_number(to_char(sysdate, 'RRRR')) -2,
5 user, 'AL', 'He''s cool');
1 row created.
SQL>
SQL> insert into nominations
2 (year, nominator, nominee, nomination_data)
3 values
4 (to_number(to_char(sysdate, 'RRRR')) -2,
5 user, 'WANDA', 'She''s cool');
1 row created.
SQL>
SQL> insert into nominations
2 (year, nominator, nominee, nomination_data)
3 values
4 (to_number(to_char(sysdate, 'RRRR')) -1,
5 user, 'AL', 'He''s cool');
1 row created.
SQL>
SQL> insert into nominations
2 (year, nominator, nominee, nomination_data)
3 values
4 (to_number(to_char(sysdate, 'RRRR')) -1,
5 user, 'WANDA', 'She''s cool');
1 row 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 and year = to_number(to_char(sysdate, 'RRRR'));18
19
20 if nom_ct < 2 then 21 insert into nominations 22 (year, nominator, nominee, nomination_data) 23 values 24 (to_number(to_char(sysdate, 'RRRR')), 25 p_nominator, 26 p_nominee, 27 p_reason); 28 29 commit; 30 31 else 32 raise too_many_noms; 33 end if;
34
35 exception
36 when too_many_noms then 37 raise_application_error(-20009, 'Too many nominations foruser '||p_nominator);
38
39 when others then 40 -- Output desired error message 41 dbms_output.put_line('-20999: Sumthin'' bad happened -- error stack follows'); 42 -- Output actual line number of error source 43 dbms_output.put(dbms_utility.format_error_backtrace); 44 -- Output the actual error number and message 45 dbms_output.put_line(dbms_utility.format_error_stack);46
47 end;
48 /
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 dbms_lock.sleep(30)
PL/SQL procedure successfully completed.
SQL> exec nominate_emp('LARDBUCKET', user, 'Because he is worth it')
PL/SQL procedure successfully completed.
SQL> exec dbms_lock.sleep(30)
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 37 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 2007 BING AL He's cool 2 2007 BING WANDA She's cool 3 2008 BING AL He's cool 4 2008 BING WANDA She's cool 5 2009 BING BING Because I am worth it 6 2009 BING LARDBUCKET Because he is worthit
6 rows selected.
SQL> David Fitzjarrell Received on Fri Apr 17 2009 - 13:46:16 CDT