oracle 触发器子句中的 IF 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27647420/
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
IF Statement inside Trigger Clause
提问by isa
I want to use an if statement inside trigger but the value if comparison will come from an other select statement.
我想在触发器内使用 if 语句，但 if 比较的值将来自其他 select 语句。
I have done the following:
我做了以下工作：
create or replace
Trigger MYTRIGGER
After Insert On Table1
Referencing Old As "OLD" New As "NEW"
For Each Row
Begin
Declare Counter Int;
Select Count(*) From Table2 Where Table2."Email" = :New.U_MAIL Into Counter;
IF Counter < 1 THEN
//INSERT Statement here...
END IF;
End;
My logic is simple, if same email user exists, insert will not work.
我的逻辑很简单，如果存在相同的电子邮件用户，插入将不起作用。
Above code did not work. How can we do this?
上面的代码不起作用。我们应该怎么做？
回答by Glenn
A few syntax errors. Would be closer to something like this:
一些语法错误。会更接近这样的事情：
create or replace
Trigger MYTRIGGER
After Insert On Table1
Referencing Old As "OLD" New As "NEW"
For Each Row
DECLARE
v_count NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_count
FROM Table2
WHERE Email = :New.U_MAIL
;
IF v_count > 0
THEN
RAISE_APPLICATION_ERROR(-20000, 'Not inserted...');
END IF;
END;
回答by Sanders the Softwarer
Your approach is wrong. Referential integrity should not be made using triggers, it just cannot work as required. See example:
你的做法是错误的。不应使用触发器来实现参照完整性，它只是无法按要求工作。见示例：
Connected to Oracle Database 12c Enterprise Edition Release 12.1.0.2.0
Connected as test@soft12c1
SQL> create table mail_1 (email varchar2(100));
Table created
SQL> create table mail_2 (email varchar2(100));
Table created
SQL> create trigger mail_1_check
2 before insert on mail_1
3 for each row
4 declare
5 cnt integer;
6 begin
7 select count(*) into cnt from mail_2 where email = :new.email;
8 if cnt > 0 then
9 raise_application_error(-20100, 'Email already exists');
10 end if;
11 end;
12 /
Trigger created
SQL> insert into mail_2 values ('[email protected]');
1 row inserted
SQL> insert into mail_1 values ('[email protected]');
1 row inserted
SQL> insert into mail_1 values ('[email protected]');
ORA-20100: Email already exists
ORA-06512: at "TEST.MAIL_1_CHECK", line 6
ORA-04088: error during execution of trigger 'TEST.MAIL_1_CHECK'
It looks like trigger works right, but it's not true. See what happens when several users will works simultaneously.
看起来触发器工作正常，但事实并非如此。看看当多个用户同时工作时会发生什么。
-- First user in his session
SQL> insert into mail_2 values ('[email protected]');
1 row inserted
-- Second user in his session
SQL> insert into mail_1 values ('[email protected]');
1 row inserted
-- First user is his session
SQL> commit;
Commit complete
-- Second user is his session
SQL> commit;
Commit complete
-- Any user in any session
SQL> select * from mail_1 natural join mail_2;
EMAIL
--------------------------------------------------------------------------------
[email protected]
If using triggers for this task, you should serialize any attempts to use this data, say, execute LOCK TABLE IN EXCLUSIVE MODE unless commit. Generally it's a bad decision. For this concrete task you can use much better approach:
如果为此任务使用触发器，则应序列化使用此数据的任何尝试，例如，除非提交，否则在 EXCLUSIVE MODE 中执行 LOCK TABLE。一般来说，这是一个错误的决定。对于这个具体的任务，您可以使用更好的方法：
Connected to Oracle Database 12c Enterprise Edition Release 12.1.0.2.0
Connected as test@soft12c1
SQL> create table mail_1_2nd(email varchar2(100));
Table created
SQL> create table mail_2_2nd(email varchar2(100));
Table created
SQL> create materialized view mail_check
2 refresh complete on commit
3 as
4 select 1/0 data from mail_1_2nd natural join mail_2_2nd;
Materialized view created
OK. Let's see, what if we try to use same email:
好的。让我们看看，如果我们尝试使用相同的电子邮件会怎样：
-- First user in his session
SQL> insert into mail_1_2nd values ('[email protected]');
1 row inserted
-- Second user in his session
SQL> insert into mail_2_2nd values ('[email protected]');
1 row inserted
SQL> commit;
Commit complete
-- First user in his session
SQL> commit;
ORA-12008: error in materialized view refresh path
ORA-01476: divisor is equal to zero
SQL> select * from mail_1_2nd natural join mail_2_2nd;
EMAIL
--------------------------------------------------------------------------------
no rows selected
