每月检查约束的Oracle触发器 [英] Oracle trigger that check constraint on a monthly basis

问题描述

只是想知道是否有可能创建一个触发器来检查指定的约束基础上每月。

just wonder is it possible to create a trigger to check on a specify constraint base on monthly basis.

例如。

表租金

| ID |会员| book |

------------------- ---

1 |约翰| fairytale |

eg.
table rent
|ID|Member|book|
----------------------
1 | John |fairytale|

2 |约翰|摩擦|

2 | John |friction|

3 |约翰| comic |

3 | John |comic|

4 |约翰|杂志|

4 | John |magazine|

约束：成员只允许每月借用4本图书。

constraint : member are only allow to borrow 4 books monthly.

我想到使用count（book）<= 4，但不知道如何实施每月基本约束。

i thought of using count(book) <= 4 but does not know how to implement the monthly basis constraint.

任何建议？

推荐答案

虽然它似乎工作，是一个危险的方式做，因为会话并行运行不会看到插入的数据从inprogress会话。
你可以通过一个调度视图：

using a trigger, whilst it seems to work, is a dangerous way of doing it, as sessions running in parallel wont see the inserted data from the inprogress session. you can do it via a materilized view:

SQL> create table rent (id number primary key, member varchar2(30), book varchar2(20), date_rented date);

Table created.

SQL> create index rent_ix1 on rent ( member, date_rented);

Index created.

SQL> create materialized view log on rent with rowid(member,date_rented)
  2  including new values;

Materialized view log created.

SQL> create materialized view rent_month_check
  2  refresh fast on commit
  3  as
  4  select trunc(date_rented, 'mm') month, member, count(*) rentals
  5    from rent
  6   group by trunc(date_rented, 'mm'), member;

Materialized view created.

SQL> alter table rent_month_check
  2  add constraint rent_month_check_ck1 check (rentals <= 4);

Table altered.

SQL> insert into rent values(1, 'DazzaL', 'crime', sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into rent values(2, 'DazzaL', 'mystery', sysdate+1);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into rent values(3, 'DazzaL', 'fantasy', sysdate+2);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into rent values(4, 'DazzaL', 'politics', sysdate+3);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into rent values(5, 'DazzaL', 'thriller', sysdate+4);

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (TEST.RENT_MONTH_CHECK_CK1) violated


SQL> select * from rent_month_check;

MONTH     MEMBER                            RENTALS
--------- ------------------------------ ----------
01-NOV-12 DazzaL                                  4

这篇关于每月检查约束的Oracle触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！