Home » SQL & PL/SQL » SQL & PL/SQL » Advance Distinct Check.
Advance Distinct Check. [message #343787] Thu, 28 August 2008 02:43 Go to next message
weekend79
Messages: 188
Registered: April 2005
Location: Islamabad
Senior Member

Hi

Oracle10 R2
I want that in my table a distinct value of column can only be repeat up to a predefined number.
i.e. if employee can take only 3 bonus at most, then distinct(Employee_Name,Year) can only repeat 3 times.

Please advise how to establish it.

Wishes
J a w a d
Re: Advance Distinct Check. [message #343790 is a reply to message #343787] Thu, 28 August 2008 03:08 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
One way I can think of:

Have a unique-constraint on three columns :

Employee_Name, Year, BonusID

And a check-constraint that BonusID can only be 1, 2 or 3.

That would require some work on the insert-procedure though. Possibly trying to insert the new record three times with the different BonusID.
Re: Advance Distinct Check. [message #344192 is a reply to message #343787] Fri, 29 August 2008 01:10 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11g> CREATE TABLE bonuses
  2    (employee_name  VARCHAR2 (15),
  3  	year	       NUMBER)
  4  /

Table created.

SCOTT@orcl_11g> CREATE MATERIALIZED VIEW LOG ON bonuses
  2  WITH ROWID (employee_name, year)
  3  INCLUDING NEW VALUES
  4  /

Materialized view log created.

SCOTT@orcl_11g> CREATE MATERIALIZED VIEW bonuses_per_year
  2  REFRESH FAST
  3  ON COMMIT
  4  AS
  5  SELECT employee_name, year,
  6  	    COUNT (*) AS bonuses_per_year
  7  FROM   bonuses
  8  GROUP  BY employee_name, year
  9  /

Materialized view created.

SCOTT@orcl_11g> ALTER MATERIALIZED VIEW bonuses_per_year
  2  ADD CONSTRAINT three_bonuses_per_year
  3  CHECK (bonuses_per_year <= 3)
  4  /

Materialized view altered.

SCOTT@orcl_11g> INSERT INTO bonuses VALUES ('emp1', 2008)
  2  /

1 row created.

SCOTT@orcl_11g> COMMIT
  2  /

Commit complete.

SCOTT@orcl_11g> INSERT INTO bonuses VALUES ('emp1', 2008)
  2  /

1 row created.

SCOTT@orcl_11g> COMMIT
  2  /

Commit complete.

SCOTT@orcl_11g> INSERT INTO bonuses VALUES ('emp1', 2008)
  2  /

1 row created.

SCOTT@orcl_11g> COMMIT
  2  /

Commit complete.

SCOTT@orcl_11g> INSERT INTO bonuses VALUES ('emp1', 2008)
  2  /

1 row created.

SCOTT@orcl_11g> COMMIT
  2  /
COMMIT
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (SCOTT.THREE_BONUSES_PER_YEAR) violated


SCOTT@orcl_11g> SELECT * FROM bonuses
  2  /

EMPLOYEE_NAME         YEAR
--------------- ----------
emp1                  2008
emp1                  2008
emp1                  2008

SCOTT@orcl_11g> 

Re: Advance Distinct Check. [message #344463 is a reply to message #343787] Fri, 29 August 2008 22:08 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Or, if one does not like consuming space with MVIEWS, then the instead-of-trigger approach also works. Both alternatives have their advantages and detractors.

SQL> 
SQL> drop synonym bonuses;

Synonym dropped.

SQL> drop view vw_bonuses;

View dropped.

SQL> drop table t_bonuses;

Table dropped.

SQL> 
SQL> create table bonuses
  2  (
  3     employee_name varchar2(30)
  4   , year number
  5  )
  6  /

Table created.

SQL> 
SQL> rename bonuses to t_bonuses
  2  /

Table renamed.

SQL> 
SQL> create or replace view vw_bonuses
  2  as
  3  select * from t_bonuses
  4  /

View created.

SQL> 
SQL> create or replace synonym bonuses for vw_bonuses
  2  /

Synonym created.

SQL> 
SQL> create or replace trigger io_bonuses
  2  instead of insert or update or delete on vw_bonuses
  3  for each row
  4  declare
  5     rowcount_v number;
  6  begin
  7     if inserting or updating then
  8        select count(*) into rowcount_v from t_bonuses where employee_name = :new.employee_name and year = :new.year;
  9        if rowcount_v >= 3 then raise_application_error(-20999,'too many rows'); end if;
 10     end if;
 11     if inserting then
 12        insert into t_bonuses values (:new.employee_name,:new.year);
 13     elsif updating then
 14        update t_bonuses set
 15              employee_name = :new.employee_name
 16            , year = :new.year
 17        where employee_name = :old.employee_name
 18       and year = :old.year;
 19     elsif deleting then
 20        delete from t_bonuses where employee_name = :new.employee_name and year = :new.year;
 21     end if;
 22  end;
 23  /

Trigger created.

SQL> show errors
No errors.
SQL> 
SQL> insert into bonuses values ('BOB',2001);

1 row created.

SQL> insert into bonuses values ('BOB',2001);

1 row created.

SQL> insert into bonuses values ('BOB',2001);

1 row created.

SQL> insert into bonuses values ('BOB',2001);
insert into bonuses values ('BOB',2001)
*
ERROR at line 1:
ORA-20999: too many rows
ORA-06512: at "KEVIN.IO_BONUSES", line 6
ORA-04088: error during execution of trigger 'KEVIN.IO_BONUSES'


Kevin
Re: Advance Distinct Check. [message #344498 is a reply to message #344463] Sat, 30 August 2008 01:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The main problem with trigger is that it does not work in multi-users environment.

Regards
Michel
Re: Advance Distinct Check. [message #344592 is a reply to message #343787] Sat, 30 August 2008 18:59 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Yes, you are right of course Michel. Everyone please try to keep this in mind when building triggers; that is, how do you account for a multi-user environment?

To clarify for this particular example:

Consider two sessions each about to insert a row with the same key. Assume the rule is: we do not want more than three rows with the same key. Assume also that there are already two rows in the table with the key these two sessions are about to insert.

If both sessions count rows at about the same time, then both session will get a rowcount for the common key of 2, for that is the commited number of rows in the table at the time each sessions counted rows. Having passed the rowcount check, each session would add its additional row thus leaving us with 4 rows in the table after each session commits, but we wanted to allow at most three (oops).

One solution would be to serialize on a user defined lock for the purpose of the count. Given both transactions would attempt to lock the user defined resource before doing the count, one transaction would be allowed to proceed and the other would wait. Once the first trasaction commited, the second would proceed, count three rows, and stop with an error.

This situation is not highly desireable as having to serialize means it will not scale in an OLTP environment. However, I have a hard time believing the materialized view version would fare any better in scaling. This is not a critique of the materialized view method, only an attempt to point out that it too has its share of problems particularly given that most people understand how a trigger would do what it does, but an understanding of the intimate details of materialized view solutions of this nature are less apparent. Most people can see that the MVIEW solution seems to work, but ask them to describe it behavior in light of the same questions of locking, transaction management, commit time, and multi-user environment, and they show that they do not really understand the details of the mechanism.

Again, I thank Michel for pointing out the hole.

For examples user define locks, consult dbms_lock.
Re: Advance Distinct Check. [message #344611 is a reply to message #344592] Sun, 31 August 2008 00:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The best solution is the one provided by Thomas, but of course it requires modification of the table and likely the application code.

Regards
Michel
Re: Advance Distinct Check. [message #344655 is a reply to message #343787] Sun, 31 August 2008 10:27 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Yes, after a second review of Thomas' proposal I would agree, it is best, especially since adding the additional column could easily be hidden from the application with a view and instead-of-trigger. And excepting for possibly one obscure siutation, it would work without side affects in a multi-user scenario.

Thanks everyone, I have been helped here too. Kevin
Previous Topic: Estimation Template for PLSQL routines
Next Topic: materialized view question
Goto Forum:
  


Current Time: Fri Dec 09 09:27:11 CST 2016

Total time taken to generate the page: 0.06309 seconds