触发 Oracle 以防止更新或插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17641216/
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
TRIGGER Oracle to prevent updating or inserting
提问by Aizat Zafran
I am having problems with this code below, which is a trigger used in Oracle SQL:
我在使用以下代码时遇到问题，这是 Oracle SQL 中使用的触发器：
CREATE OR REPLACE TRIGGER TRG_TUTOR_BLOCK
BEFORE INSERT OR UPDATE ON tutors
FOR EACH ROW
DECLARE
BEGIN
IF :new.tutorName = :old.tutorName
THEN
RAISE_APPLICATION_ERROR(-20101, 'A tutor with the same name currently exists.');
ROLLBACK;
END IF;
END;
/
This trigger is used to prevent users from entering the same tutor name at different records.
此触发器用于防止用户在不同的记录中输入相同的导师姓名。
After I insert two records with the same tutorname, the trigger does not block me from inserting it. Is there anyone can tell me what are the problems with this coding? Here are the sample format and insert values:
在我插入两个具有相同导师姓名的记录后，触发器不会阻止我插入它。有没有人可以告诉我这个编码有什么问题？以下是示例格式和插入值：
INSERT INTO tutors VALUES (tutorID, tutorName tutorPhone, tutorAddress, tutorRoom, loginID);
INSERT INTO tutors VALUES ('13SAS01273', 'Tian Wei Hao', '019-8611123','No91, Jalan Wangsa Mega 2, 53100 KL', 'A302', 'TianWH');
回答by suPPLer
Trigger in Kamil's example will throw ORA-04091, you can see this with your own eyes here. ROLLBACK in a trigger is unnecessary, it runs implicitly when a trigger makes a statement to fail.
Kamil 示例中的触发器将抛出ORA-04091，您可以在这里亲眼看到这一点。触发器中的 ROLLBACK 是不必要的，它会在触发器使语句失败时隐式运行。
You can prohibit any DML on table by altering it with read only clause:
您可以通过使用只读子句对其进行更改来禁止表上的任何 DML ：
alter table tutors read only;
At last, integrity should be declarated with integrity constraintsand not with triggers.
最后，完整性应该用完整性约束而不是触发器来声明。
Good luck!
祝你好运！
回答by Gabriele D'Antona
You don't need a trigger for this in Oracle.
在 Oracle 中，您不需要为此设置触发器。
You can do it with an "unique index" on the tutorName column (see http://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes003.htm#i1106547).
您可以使用tutorName 列上的“唯一索引”（请参阅http://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes003.htm#i1106547）。
Note: about your trigger, it fails on checking for another record with the same tutorName because it's not scanning the tutors table for another record with the same tutorName, it's just comparing the tutorName values of the row you are creating (in this case, old.tutorName is just NULL, because the row doesn't exist yet).
注意：关于您的触发器，它无法检查具有相同导师名称的另一条记录，因为它没有扫描导师表以查找具有相同导师名称的另一条记录，它只是比较您正在创建的行的导师名称值（在这种情况下，旧的.tutorName 只是 NULL，因为该行尚不存在）。
回答by Kamil
Check the case in yours trigger body
检查您的触发器主体中的外壳
IF :new.tutorName = :old.tutorName
It returns true only if 'tutorName' value is the same in new and old record. When you'll trying to updat some value you'll get
仅当新旧记录中的 'tutorName' 值相同时才返回 true。当您尝试更新某些值时，您会得到
IF 'someTutorName' = 'someTutorName'
which will return TRUE. Inserting row cannot fire this rule because you're trying to compare something like that:
这将返回 TRUE。插入行无法触发此规则，因为您正在尝试比较类似的内容：
'someTutorName' = NULL
This case always returns FALSE. Try to use something like that
这种情况总是返回 FALSE。尝试使用类似的东西
CREATE OR REPLACE TRIGGER TRG_TUTOR_BLOCK
BEFORE INSERT OR UPDATE ON tutors
FOR EACH ROW
DECLARE
rowsCount INTEGER;
BEGIN
SELECT COUNT(*) FROM tutors WHERE tutorName is :new.tutorName INTO rowsCount;
IF rowsCount > 0
THEN
RAISE_APPLICATION_ERROR(-20101, 'A tutor with the same name currently exists.');
ROLLBACK;
END IF;
END;
/
But the best solution is the one mentioned by friol- use unique indexby executing SQL like this
但是最好的解决方案是friol提到的一个-通过像这样执行 SQL 来使用唯一索引
ALTER TABLE tutors
ADD CONSTRAINT UNIQUE_TUTOR_NAME UNIQUE (tutorName);
回答by Davut Gürbüz
If you wanna completely ignore recording a row to a table you can follow these steps
如果您想完全忽略将行记录到表格中，您可以按照以下步骤操作
rename table to something else and create a view with the same name and create an instead of trigger.
将表重命名为其他名称并创建一个具有相同名称的视图并创建一个而不是触发器。
create table usermessages (id number(10) not null)
GO
alter table usermessages rename to xusermessages
GO
create or replace view usermessages as (select * from xusermessages)
GO
create or replace trigger usermessages_instead_of_trg
instead of insert or update on usermessages
for each row
begin
Null ;
end ;
GO
insert into usermessages(123)
Live test available here below
下面提供现场测试
