三大数据库 sequence 之华山论剑 (二)

sequence 用法二 触发器中调用

是否可以在 INSERT 语句中不显式调用 sequence，而使其自动调用呢？

当然可以！通常有三种方法。一是通过触发器实现，二是在 DEFAULT 中调用sequence，三是通过 AUTO INCREMENT 方式。

我们先来看一下如何在触发器中实现。

可以在表的 BEFORE INSERT 触发器中，调用 sequence，从而达到在插入前自动给主键赋值。这样，在 INSERT 中就不需要显式调用 sequence 了。

Oracle

SQL> CREATE SEQUENCE seq_test2;

Sequence created.

SQL> CREATE TABLE tb_test2 (
    test_id NUMBER PRIMARY KEY,
    test_order NUMBER
);  2    3    4  

Table created.

SQL> CREATE OR REPLACE TRIGGER trg_b_ins_tb_test2
  BEFORE INSERT ON tb_test2
  FOR EACH ROW
BEGIN
  SELECT seq_test2.nextval
  INTO :new.test_id
  FROM dual;
END;  2    3    4    5    6    7    8  
  9  /

Trigger created.

SQL> INSERT INTO tb_test2 (test_order) VALUES (1);                

1 row created.

SQL> SELECT * FROM tb_test2 ORDER BY 2 DESC;

   TEST_ID TEST_ORDER
---------- ------------
	 1	      1

下面测试表明，当在 INSERT 中指定列 test_id 为 NULL 时，会从 sequence 中取值。但这是 trigger 的原理决定的，与传入的值是否为 NULL 无关。

SQL> INSERT INTO tb_test2 (test_id,test_order) VALUES (NULL,2);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM tb_test2 ORDER BY 2 DESC;

   TEST_ID TEST_ORDER
---------- ----------
	 2	    2
	 1	    1

PostgreSQL

如下示例，PostgreSQL 的 SQL 与 Oracle 的 SQL 也很类似。触发器的创建方式略有差异。

alvindb=> CREATE SEQUENCE seq_test2;
CREATE SEQUENCE
alvindb=> CREATE TABLE tb_test2 (
alvindb(>     test_id INTEGER PRIMARY KEY,
alvindb(>     test_order INTEGER
alvindb(> );
CREATE TABLE
alvindb=> CREATE OR REPLACE FUNCTION trgf_b_ins_tb_test2()
alvindb-> RETURNS TRIGGER AS
alvindb-> $$
alvindb$> BEGIN
alvindb$>     NEW.test_id := nextval('seq_test2');
alvindb$>     RETURN NEW;
alvindb$> END;
alvindb$> $$
alvindb-> LANGUAGE 'plpgsql';
CREATE FUNCTION
alvindb=> CREATE TRIGGER trg_b_ins_tb_test2
alvindb->     BEFORE INSERT ON tb_test2
alvindb->     FOR EACH ROW
alvindb->     EXECUTE PROCEDURE trgf_b_ins_tb_test2();
CREATE TRIGGER
alvindb=> \d+ tb_test2
                                   Table "public.tb_test2"
   Column   |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
------------+---------+-----------+----------+---------+---------+--------------+-------------
 test_id    | integer |           | not null |         | plain   |              | 
 test_order | integer |           |          |         | plain   |              | 
Indexes:
    "tb_test2_pkey" PRIMARY KEY, btree (test_id)
Triggers:
    trg_b_ins_tb_test2 BEFORE INSERT ON tb_test2 FOR EACH ROW EXECUTE PROCEDURE trgf_b_ins_tb_test2()
alvindb=> INSERT INTO tb_test2 (test_order) VALUES (1);
INSERT 0 1
alvindb=> SELECT * FROM tb_test2 ORDER BY 2 DESC;
 test_id | test_order 
---------+--------------
       1 |            1
(1 row)

下面测试表明，同 Oracle 中一样，当在 INSERT 中指定列 test_id 为 NULL 时，同样，这也是 trigger 的原理决定的，与传入的值是否为 NULL 无关。

alvindb=> INSERT INTO tb_test2 (test_id,test_order) VALUES (NULL,2);
INSERT 0 1
alvindb=> SELECT * FROM tb_test2 ORDER BY 2 DESC;
 test_id | test_order 
---------+------------
       2 |          2
       1 |          1
(2 rows)

MySQL

MySQL 不支持单独创建sequence。 参考 用法四 AUTO INCREMENT 中 MySQL 部分。