如何使触发器像主键约束一样? [英] how to make a trigger like primary key constraint?
问题描述
我需要定义一个触发器，该触发器要应用于表的列.触发器应限制用户输入重复的值，而不是空值.或者您可以说，我需要了解主键的逻辑.
i need to define a trigger which i want to apply on a column of table. The trigger should restrict the user to input duplicate and not null values. Or you can say, i need to know the logic of primary key.
推荐答案
只是因为您似乎打算看到此失败，并且不想从APC的观点中脱颖而出，只要它是before触发器:
Just because you seem intent on seeing this fail, and not to take anything away from APC's points, this appears to work at first glance as long as it's a before trigger:
create table t42 (id number);
create trigger trig42
before insert or update on t42
for each row
declare
c number;
begin
if :new.id is null then
raise_application_error(-20001, 'ID is null');
end if;
select count(*) into c from t42 where id = :new.id;
if c > 0 then
raise_application_error(-20002, 'ID is not unique');
end if;
end;
/
它会编译，并且如果您插入数据，您将获得您似乎想要的行为:
It compiles and if you insert data you get the behaviour you seem to want:
insert into t42 values (1);
1 rows inserted.
insert into t42 values (1);
Error starting at line 20 in command:
insert into t42 values (1)
Error report:
SQL Error: ORA-20002: ID is not unique
ORA-06512: at "STACKOVERFLOW.TRIG42", line 9
ORA-04088: error during execution of trigger 'STACKOVERFLOW.TRIG42'
insert into t42 values (null);
Error starting at line 22 in command:
insert into t42 values (null)
Error report:
SQL Error: ORA-20001: ID is null
ORA-06512: at "STACKOVERFLOW.TRIG42", line 5
ORA-04088: error during execution of trigger 'STACKOVERFLOW.TRIG42'
select * from t42;
ID
----------
1
这似乎可以满足您的要求.但是，如果您有多个会话，则不会.我没有参加本次会议；在另一个会话中，我可以执行以下操作:
Which seems to do what you want. But not if you have more than one session. I haven't committed in this session; in another session I can do:
insert into t42 values (1);
1 row created.
select * from t42;
ID
----------
1
1 row selected.
嗯，这很奇怪.好吧，也许是推迟了……让我们两个都提交:
Hmm, that's strange. Well, maybe it's deferred... let's commit them both:
commit;
select * from t42;
ID
----------
1
1
2 rows selected.
糟糕.一旦会话无法看到另一个会话的未提交数据，那么它将永远无法工作.
Oops. Once session can't see another session's uncommitted data, so this will never work.
此外，当我们在单个语句中插入多行时，变异表问题也会出现:
Also, the mutating table problem exhibits itself when we insert multiple rows in a single statement:
SQL> insert into t42 select level+1 from dual connect by level <= 5;
insert into t42 select level+1 from dual connect by level <= 5
*
ERROR at line 1:
ORA-04091: table STACKOVERFLOW.T42 is mutating, trigger/function may not see it
ORA-06512: at "STACKOVERFLOW.TRIG42", line 7
ORA-04088: error during execution of trigger 'STACKOVERFLOW.TRIG42'
SQL>
两次.
即使有一个after触发器和一个用于解决变异表问题的软件包，除非您为每次插入或更新都锁定整个表，否则您仍然会遇到这个问题(我认为).正如APC所说，约束是在数据库的深层而不是在此级别中实现的.
Even with an after trigger and a package to work around the mutating table issue, you'd still have this problem (I think), unless you lock the whole table for every insert or update. As APC said the constraint is implemented deep in the bowels of the database, not at this level.
是否无法定义触发器，该触发器会在执行前检查该值 插入它也不应为null和唯一的?
is it not possible to define a trigger, which checks the value before insertion that it should not be null and unique as well?
不是，当您进行多个会话时，不会.即使在一个会话中，除非您在该列上有索引，否则性能将不会缩放，因为count(*)会逐渐变慢.而且，如果您确实有索引，为什么不首先使其成为唯一索引呢?
Not when you have more than one session, no. And even within one session, unless you have an index on the column the performance won't scale as the count(*) will get progressively slower. And if you do have an index, well, why not make it a unique index in the first place?
最后，根据触发设计准则:
请勿创建重复数据库功能的触发器.
Do not create triggers that duplicate database features.
例如，如果可以，请勿创建触发器以拒绝无效数据 对约束进行相同的操作(请参阅"触发和约束的方式 不同" ).
For example, do not create a trigger to reject invalid data if you can do the same with constraints (see "How Triggers and Constraints Differ").
这篇关于如何使触发器像主键约束一样?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
