在Oracle plsql中使用触发器填充代理密钥的优势 [英] advantage of using a trigger to populate surrogate key in oracle plsql

问题描述

我正在开发一种具有以下模式的代码库，用于为表生成代理键.

I am working on a codebase which has the following type of pattern for generating surrogate key for tables.

create or replace
TRIGGER TEST_TRIG
BEFORE INSERT  OR UPDATE
ON my_table
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
BEGIN
   IF INSERTING THEN
      IF NVL(:NEW.column1, 0) = 0 THEN
         SELECT my_table_seq.NEXTVAL
         INTO  :NEW.column1
         FROM  dual;

      END IF;
   END IF;
END;

以及调用此代码的PL/SQL逻辑，

And the PL/SQL logic that calls this code as,

begin
     insert into my_table(0);
end;

我的问题是触发需要什么? 为什么不做一个，

My question is What is the need for a trigger? Why not just do a,

insert into my_table(my_table_seq.next_val)

并称其为一天? 任何见解将不胜感激.谢谢.

and call it a day? Any insight would be much appreciated. Thanks.

推荐答案

此特定模式实际上非常危险，因为它允许某人手动输入一个新ID，该ID可能与现有的替代键或您的序列可能生成的替代键发生冲突.将来.

This particular pattern is actually quite dangerous as it allows someone to manually input a new ID that might clash with an already extant surrogate key or one that your sequence might generate in the future.

您的触发器实际上应该看起来像这样，以确保每个新记录都获得唯一的键.如果您使用的是11.2或更高版本，则无需select ... into ...

Your trigger should really look like this to ensure that for every new record gets a unique key. If you're using 11.2 or higher there's no need for the select ... into ...

CREATE OR REPLACE TRIGGER TEST_TRIG
BEFORE INSERT ON my_table
FOR EACH ROW
BEGIN
   :new.column1 := my_table_seq.NEXTVAL;
END;

这种方法的好处是，它总是 完成.任何人在此列中输入的任何值都将被覆盖为将起作用并使用正确顺序的值.如果有人忘记将其添加到语句中，则仍然可以使用.

The benefit of this approach is that it's always done. Whatever value anyone puts in for this column gets overwritten to something that will work and which uses the correct sequence; if someone forgets to add it in the statement will still work.

这使得无法破解代理密钥.

It makes it impossible to break your surrogate key.

根据您的建议，假设有人放置一个1来代替；您会收到主键冲突.如果有人忘记了，那还会有更多的错误.您将永远无法保证对表的每次更新都将通过单个入口点进行，因此触发器将保证正确填充PK.

With what you suggest, imagine that someone places a 1 instead; you get a primary key violation. If someone forgets then there's more errors. You'll never guarantee that every update to your table will be through a single point of entry so the trigger guarantees that the PK is populated correctly.

值得注意的是，从12c开始，您可以使用身份列，用于明确表和自动增量之间的链接；不需要触发器或序列.创建表DDL的语法为:

It's worth noting that from 12c you can use an identity column, which makes explicit the link between table and auto-increment; there's no need for a trigger or a sequence. The syntax for the table creation DDL would be:

create table <table_name> ( <column_name> generated as identity );

这篇关于在Oracle plsql中使用触发器填充代理密钥的优势的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！