创建触发器 [英] Creating a Triggers

问题描述

我如何启动触发器，以使没有人能在未付余额超过50美元的情况下租借电影?

How do I start a trigger so that this allows nobody to be able to rent a movie if their unpaid balance exceeds 50 dollars?

推荐答案

这里有一个跨行表约束-即您不能只将单个Oracle CONSTRAINT放在列上，因为这些只能一次查看一行中的数据.

What you have here is a cross-row table constraint - i.e. you can't just put a single Oracle CONSTRAINT on a column, as these can only look at data within a single row at a time.

Oracle仅支持两种跨​​行约束类型-唯一性(例如主键和唯一约束)和参照完整性(外键).

Oracle has support for only two cross-row constraint types - uniqueness (e.g. primary keys and unique constraints) and referential integrity (foreign keys).

在您的情况下，您必须自己手动编写约束代码-随之而来的责任是确保在存在多个会话的情况下不会违反约束，每个会话都看不到由谁插入/更新的数据其他并发会话(至少，直到它们提交).

In your case, you'll have to hand-code the constraint yourself - and with that comes the responsibility to ensure that the constraint is not violated in the presence of multiple sessions, each of which cannot see data inserted/updated by other concurrent sessions (at least, until they commit).

一种简单的方法是添加一个触发器，该触发器发出查询以计算有多少条记录与新记录发生冲突；但这不会起作用，因为触发器无法看到其他会话已插入/更新但尚未提交的行；因此触发器有时会允许成员租用6个视频，只要(例如)他们让两名出纳员在单独的终端中输入数据即可.

A simplistic approach is to add a trigger that issues a query to count how many records conflict with the new record; but this won't work because the trigger cannot see rows that have been inserted/updated by other sessions but not committed yet; so the trigger will sometimes allow members to rent 6 videos, as long as (for example) they get two cashiers to enter the data in separate terminals.

解决此问题的一种方法是将序列化的一些内容放入-例如触发器将首先要求对成员记录进行锁定(例如，使用SELECT FOR UPDATE)，然后才允许检查租金；这样，如果第二个会话尝试插入租金，它将等到第一个会话进行提交或回滚.

One way to get around this problem is to put some element of serialization in - e.g. the trigger would first request a lock on the member record (e.g. with a SELECT FOR UPDATE) before it's allowed to check the rentals; that way, if a 2nd session tries to insert rentals, it will wait until the first session does a commit or rollback.

另一种方法，解决此问题的方法是使用聚合的物化视图，该视图基于旨在查找任何未通过测试的行的查询；期望MV将为空，并且您在MV上放置了一个表约束，这样，如果在MV中曾经出现过一行，则该约束将被违反.这样做的结果是，刷新MV时，任何试图插入违反约束的行的语句都会导致违反约束.

Another way around this problem is to use an aggregating Materialized View, which would be based on a query that is designed to find any rows that fail the test; the expectation is that the MV will be empty, and you put a table constraint on the MV such that if a row was ever to appear in the MV, the constraint would be violated. The effect of this is that any statement that tries to insert rows that violate the constraint will cause a constraint violation when the MV is refreshed.

根据您的设计编写查询的过程留给读者练习:)

Writing the query for this based on your design is left as an exercise for the reader :)

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