oracle 在插入或更新之前触发 plsql
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1320848/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
before INSERT or Update trigger plsql
提问by wantobegeek
can anyone help me write a trigger to disallow particular entry into a table (for e.g. location = 'chicago' not allowed).The table schema is as follows department(deptno,deptname,location).I am using oracle 10g.
谁能帮我写一个触发器来禁止特定条目进入表（例如，不允许 location = 'chicago'）。表架构如下部门（deptno，deptname，location）。我正在使用 oracle 10g。
回答by Svetlozar Angelov
You can easily do what you want with CHECK CONSTRAINT on your column.
您可以使用列上的 CHECK CONSTRAINT 轻松完成所需的操作。
ALTER TABLE T
ADD CONSTRAINT constraint_name CHECK (location <> 'chicago') [DISABLE];
The DISABLE keyword is optional. If you create a check constraint using the DISABLE keyword, the constraint will be created, but the condition will not be enforced.
DISABLE 关键字是可选的。如果您使用 DISABLE 关键字创建检查约束，则会创建约束，但不会强制执行条件。
Constraint States
约束状态
- ENABLE- ensure that all incoming data conforms to the constraint
- DISABLE- allow incoming data, regardless of whether it conforms to the constraint
- VALIDATE- ensure that existing data conforms to the constraint
- NOVALIDATE- existing data does not have to conform to the constraint
- ENABLE- 确保所有传入的数据符合约束
- DISABLE- 允许传入的数据，不管它是否符合约束
- VALIDATE- 确保现有数据符合约束
- NOVALIDATE- 现有数据不必符合约束
These can be used in combination
这些可以组合使用
ENABLE { [default] VALIDATE | NOVALIDATE }
启用 { [默认] 验证 | 新验证}
DISABLE { VALIDATE |[default] NOVALIDATE }
禁用{验证|[默认]新验证}
ENABLE VALIDATE is the same as ENABLE.
ENABLE NOVALIDATE means that the constraint is checked, but it does not have to be true for all rows. this will resume constraint checking on disabled constraints without first validating all data in the table.
DISABLE NOVALIDATE is the same as DISABLE.
DISABLE VALIDATE disables the constraint, drops the index on the constraint, and disallows any modification of the constrained columns. for a UNIQUE constraint, this enables you to load data from a nonpartitioned table into a partitioned table using the ALTER TABLE.. EXCHANGE PARTITION clause.
ENABLE VALIDATE 与 ENABLE 相同。
ENABLE NOVALIDATE 意味着检查约束，但它不必对所有行都为真。这将在不首先验证表中的所有数据的情况下恢复对禁用约束的约束检查。
DISABLE NOVALIDATE 与 DISABLE 相同。
DISABLE VALIDATE 禁用约束，删除约束上的索引，并禁止对受约束的列进行任何修改。对于 UNIQUE 约束，这使您能够使用 ALTER TABLE.. EXCHANGE PARTITION 子句将数据从非分区表加载到分区表中。
Here is an example of BEFORE INSERT trigger. However it is better to create constraints on your schema or to implement CUSTOM_INSERT PROCEDURE, to filter it. Hereis a good article about Data Integrity - Constraints and Triggers.
这是 BEFORE INSERT 触发器的示例。但是，最好在您的架构上创建约束或实施 CUSTOM_INSERT PROCEDURE，以对其进行过滤。这是一篇关于数据完整性 - 约束和触发器的好文章。
Triggers should not be usedto enforce business rules or referential integrity rules that could be implemented with simple constraints.
不应使用触发器来实施可以通过简单约束实现的业务规则或参照完整性规则。
Example trigger(consider it as a bad idea for filtering input):
示例触发器（将其视为过滤输入的坏主意）：
CREATE TRIGGER myTrigger
BEFORE INSERT
ON table
REFERENCING NEW AS New
FOR EACH ROW
BEGIN
IF (New.location = 'chicago') THEN
RAISE cError;
EXCEPTION
WHEN cError THEN
RAISE_APPLICATION_EXCEPTION(-20001,'Chicago is not allowed');
END;
