SQL 如何在oracle中创建一个触发器，它会根据条件限制对表的插入和更新查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9654623/
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
how to create a trigger in oracle which will restrict insertion and update queries on a table based on a condition
提问by Ashish dmc4
I have account table as this--
我有这样的账户表——
create table account
(
acct_id int,
cust_id int,
cust_name varchar(20)
)
insert into account values(1,20,'Mark');
insert into account values(2,23,'Tom');
insert into account values(3,24,'Jim');
I want to create a trigger which will ensure that no records can be inserted or update in account table having acct_id as 2 and cust_id as 23.
我想创建一个触发器，以确保无法在 acct_id 为 2 且 cust_id 为 23 的帐户表中插入或更新任何记录。
My code is --
我的代码是——
create trigger tri_account
before insert or update
on account
for each row
begin
IF (:new.acct_id == 2 and :new.cust_id == 23) THEN
DBMS_OUTPUT.PUT_LINE('No insertion with id 2 and 23.');
rollback;
END IF;
end;
so this trigger is created , but with compilation error. now when I insert any record with acct_id as 2 and cust_id as 23,it doesent allow. But I get an error saying ORA-04098: trigger 'OPS$0924769.TRI_ACCOUNT' is invalid and failed re-validation
所以这个触发器被创建，但编译错误。现在，当我插入 acct_id 为 2 且 cust_id 为 23 的任何记录时，它不允许。但是我收到一条错误消息说 ORA-04098: trigger 'OPS$0924769.TRI_ACCOUNT' is invalid and failed re-validation
I don't understand this.I also want to show a message that dis insertion is not possible. please Help...
我不明白这一点。我还想显示一条消息，说明无法插入。请帮忙...
回答by Justin Cave
- The equality operator in Oracle is
=, not==. - You cannot commit or rollback in a trigger. You can throw an exception which causes the triggering statement to fail and to be rolled back (though the existing transaction will not necessarily be rolled back).
- It does not appear that this trigger compiled successfully when you created it. If you are using SQL*Plus, you can type
show errorsafter creating a PL/SQL object to see the compilation errors. - You should never write code that depends on the caller being able to see the output from
DBMS_OUTPUT. Most applications will not so most applications would have no idea that the DML operation failed if your trigger simply tries to write to theDBMS_OUTPUTbuffer.
- Oracle 中的相等运算符是
=，不是==。 - 您不能在触发器中提交或回滚。您可以抛出一个异常，导致触发语句失败并回滚（尽管现有事务不一定会回滚）。
- 创建此触发器时，它似乎没有成功编译。如果您使用 SQL*Plus，您可以
show errors在创建 PL/SQL 对象后键入以查看编译错误。 - 你永远不应该编写依赖于调用者能够看到
DBMS_OUTPUT. 大多数应用程序不会，因此如果您的触发器只是尝试写入DBMS_OUTPUT缓冲区，大多数应用程序将不知道 DML 操作失败。
Putting those items together, you can write something like
把这些项目放在一起，你可以写出类似的东西
create trigger tri_account
before insert or update
on account
for each row
begin
IF (:new.acct_id = 2 and :new.cust_id = 23) THEN
raise_application_error( -20001, 'No insertion with id 2 and 23.');
END IF;
end;
回答by David Faber
A trigger is more flexible, but you can also accomplish this through the use of a CHECK CONSTRAINT:
触发器更灵活，但您也可以通过使用 CHECK CONSTRAINT 来实现：
ALTER TABLE account ADD CONSTRAINT check_account CHECK ( acct_id != 2 OR cust_id != 23 )
ENABLE NONVALIDATE;
The NONVALIDATE clause will ensure that the check constraint does not attempt to validate existing data, though it will validate all future data.
NONVALIDATE 子句将确保检查约束不会尝试验证现有数据，尽管它将验证所有未来数据。
Hope this helps.
希望这可以帮助。
回答by Neil Garcia
IF (:new.acct_id = 2 and :new.cust_id = 23) THEN
must be OR, not and.
必须是OR，不是and。
回答by user3007498
While using conditional checks you don't need to use colons (:). This will always cause errors.
使用条件检查时，您不需要使用冒号 ( :)。这将始终导致错误。
Note: Exclude the colon only in cases where condition checking is performed.
注意：仅在执行条件检查的情况下排除冒号。
