logo - 跳到首页

VastbaseG100

基于openGauss内核开发的企业级关系型数据库。

Menu

使用 DML 触发器检查完整性

本节主要介绍 DML 触发器的一些应用。

概述

用户可以使用触发器和约束来维护父表和子表之间的引用完整性，如下表所示。

表 在表上声明约束 在表上创建触发器
父表 PRIMARY KEY或者UNIQUE 一个或多个触发器确保当PRIMARY KEY或 UNIQUE值被更新或删除时，所需的操作（RESTRICT、CASCADE 或 SET NULL）发生在相应的 FOREIGN KEY 值上。插入父表不需要任何操作，因为不存在依赖外键。
子表 FOREIGN KEY，如果父表和子表在同一个数据库中。禁用此外键约束以防止删除相应的 PRIMARY KEY 或 UNIQUE 约束（明确使用 CASCADE 选项除外）。 一种触发器，可确保在 FOREIGN KEY 中插入或更新的值对应于父表中的 PRIMARY KEY 或 UNIQUE 值。

子表的外键触发

以下示例中的触发器确保在 INSERT 或 UPDATE 语句影响外键值之前，相应的值存在于父键中。

本节使用以下表作为示例。

CREATE TABLE vb_score (
  id     INT,
  name     VARCHAR2(10),
  object       NUMBER);

CREATE TABLE vb_class (
  id   INT,
  name     VARCHAR2(10),
  class       CHAR(1));

向表中插入数据。

insert into vb_score values (1, 'apple', 74);
insert into vb_score values (2, 'banana', 87);
insert into vb_score values (3, 'peach', 95);
insert into vb_class values (1, 'apple', 'C');
insert into vb_class values (2, 'banana', 'B');
insert into vb_class values (3, 'peach', 'A');

1、创建触发器函数，检查如果向 vb_score 表中插入的 id 不存在于 vb_class，则报错 Invalid Insertion on a Child Table.，并返回不合法的 id。

CREATE OR REPLACE FUNCTION check_class_func() RETURNS TRIGGER AS
$$
DECLARE
  vb_id  INTEGER;
      invalid_class EXCEPTION;
      valid_class EXCEPTION;
  CURSOR class_check_cursor (vb_id NUMBER) IS
    SELECT id FROM vb_class
    WHERE id = vb_id;
BEGIN
  OPEN class_check_cursor (NEW.id);
  FETCH class_check_cursor INTO vb_id;

  IF class_check_cursor%NOTFOUND THEN
    RAISE invalid_class;
  ELSE
    RAISE valid_class;
  END IF;
  CLOSE class_check_cursor;
    EXCEPTION
      WHEN invalid_class THEN
          RAISE 'Invalid Insertion on a Child Table, Invalid id：%' , NEW.id USING ERRCODE = '20000'; 
          CLOSE class_check_cursor;
  WHEN valid_class THEN
        CLOSE class_check_cursor;
END
$$ LANGUAGE PLPGSQL;

2、创建触发器，引用创建的触发器函数 check_class_func()。

CREATE TRIGGER score_class_trigger
  BEFORE INSERT OR UPDATE OF id ON vb_score
  FOR EACH ROW 
  WHEN (NEW.id IS NOT NULL)
  EXECUTE PROCEDURE check_class_func();

3、向 vb_score 中插入数据。

insert into vb_score values (4, 'grape', 60);

返回结果如下所示。

ERROR:  Invalid Insertion on a Child Table, Invalid id：4

父表的 UPDATE 和 DELETE 的 SET NULL 触发器

本节使用以下表作为示例。

CREATE TABLE vb_score_2 (
  id     INT,
  name     VARCHAR2(10),
  object       NUMBER);

CREATE TABLE vb_class_2 (
  id   INT,
  name     VARCHAR2(10),
  class       CHAR(1));

向表中插入数据。

insert into vb_score_2 values (1, 'apple', 74);
insert into vb_score_2 values (2, 'banana', 87);
insert into vb_score_2 values (3, 'peach', 95);
insert into vb_class_2 values (1, 'apple', 'C');
insert into vb_class_2 values (2, 'banana', 'B');
insert into vb_class_2 values (3, 'peach', 'A');

以下示例中的触发器确保在 vb_class_2 上执行 DELETE 或 UPDATE 语句后，如果 id 列发生变化，则将 vb_score 对应的 id 设置为 NULL。

1、创建触发器函数，如果 id 列发生变化，则将 vb_score_2 对应的 id 设置为 NULL。

CREATE OR REPLACE FUNCTION class_set_null_func() RETURNS TRIGGER AS
$$
BEGIN
  IF OLD.id != NEW.id OR NEW.id IS NULL THEN
    UPDATE vb_score_2 SET vb_score_2.id = NULL
    WHERE vb_score_2.id = OLD.id;
	RETURN OLD;
  END IF;
END
$$ LANGUAGE PLPGSQL;

2、创建触发器，引用创建的触发器函数 class_set_null_func()。

CREATE TRIGGER class_set_null
  AFTER DELETE OR UPDATE OF id ON vb_class_2
  FOR EACH ROW
  EXECUTE PROCEDURE class_set_null_func();

3、从 vb_class_2 表中删除 id = 1 的行。

DELETE FROM vb_class_2 WHERE id =1;

4、查询 vb_score_2 表。

SELECT * FROM vb_score_2;

查询结果如下所示。

 id |  name  | object
----+--------+--------
  2 | banana |     87
  3 | peach  |     95
    | apple  |     74
(3 rows)

父表的删除级联触发器

本节使用以下表作为示例。

CREATE TABLE vb_score_3 (
  id     INT,
  name     VARCHAR2(10),
  object       NUMBER);

CREATE TABLE vb_class_3 (
  id   INT,
  name     VARCHAR2(10),
  class       CHAR(1));

向表中插入数据。

insert into vb_score_3 values (1, 'apple', 74);
insert into vb_score_3 values (2, 'banana', 87);
insert into vb_score_3 values (3, 'peach', 95);
insert into vb_class_3 values (1, 'apple', 'C');
insert into vb_class_3 values (2, 'banana', 'B');
insert into vb_class_3 values (3, 'peach', 'A');

以下示例中的触发器确保在 vb_class_3 上执行 DELETE 语句后，级联删除 vb_score_3 表上对应的数据。

1、创建触发器函数，在 vb_class_3 上执行 DELETE 语句后，级联删除 vb_score_3 表上对应的数据。

CREATE OR REPLACE FUNCTION class_del_cascade_func() RETURNS TRIGGER AS
$$
BEGIN
  DELETE FROM vb_score_3
  WHERE vb_score_3.id = OLD.id;
  RETURN OLD;
END
$$ LANGUAGE PLPGSQL;

2、创建触发器，引用创建的触发器函数 class_del_cascade_func()。

CREATE TRIGGER class_del_cascade
  AFTER DELETE ON vb_class_3
  FOR EACH ROW
  EXECUTE PROCEDURE class_del_cascade_func();

3、从 vb_class_3 表中删除 id = 1 的行。

DELETE FROM vb_class_3 WHERE id =1;

4、查询 vb_score_3 表。

SELECT * FROM vb_score_3;

查询结果如下所示。

 id |  name  | object
----+--------+--------
  2 | banana |     87
  3 | peach  |     95
(2 rows)

父表的更新级联触发器

本节使用以下表作为示例。

CREATE TABLE vb_score_4 (
  id     INT,
  name     VARCHAR2(10),
  object       NUMBER);

CREATE TABLE vb_class_4 (
  id   INT,
  name     VARCHAR2(10),
  class       CHAR(1));

向表中插入数据。

insert into vb_score_4 values (1, 'apple', 74);
insert into vb_score_4 values (2, 'banana', 87);
insert into vb_score_4 values (3, 'peach', 95);
insert into vb_class_4 values (1, 'apple', 'C');
insert into vb_class_4 values (2, 'banana', 'B');
insert into vb_class_4 values (3, 'peach', 'A');

级联触发器是指在父表上的任何更新都会同步到子表上，下面的示例在触发函数中对父表的 INSERT、UPDATE 和 DELETE 操作进行同步。

1、创建触发器函数，对父表的 INSERT、UPDATE 和 DELETE 操作进行同步。

CREATE OR REPLACE FUNCTION update_cascade_func() RETURNS TRIGGER AS
$$
BEGIN
  IF (TG_OP = 'UPDATE') THEN
    UPDATE vb_score_4 SET id = NEW.id WHERE id = OLD.id;
    UPDATE vb_score_4 SET name = NEW.name WHERE id = OLD.id; 
  ELSEIF (TG_OP = 'DELETE') THEN
    DELETE FROM vb_score_4 WHERE vb_score_4.id = OLD.id;
  ELSEIF (TG_OP = 'INSERT') THEN 
    INSERT INTO vb_score_4 ( id, name, object ) VALUES (NEW.id, NEW.name, 0 );
  ELSE
    --在本示例中暂不考虑 Truncate 的情况
    RAISE 'Invalid Operation For a Table which owns a child table';
  END IF;
  RETURN NULL;
END
$$ LANGUAGE PLPGSQL;

2、创建触发器，引用创建的触发器函数 update_cascade_func()。

CREATE TRIGGER update_cascade_trigger
    BEFORE INSERT OR UPDATE OR DELETE ON vb_class_4 
    FOR EACH ROW
    EXECUTE PROCEDURE update_cascade_func();

3、从 vb_class_4 表中删除 id = 1 的行。

DELETE FROM vb_class_4 WHERE id =1;

4、查询 vb_score_4 表。

SELECT * FROM vb_score_4;

查询结果如下所示。

 id |  name  | object
----+--------+--------
  2 | banana |     87
  3 | peach  |     95
(2 rows)

5、向 vb_class_4 表中插入 (1,'apple','c') 的数据。

INSERT INTO vb_class_4 VALUES (1,'apple','c');

6、查询 vb_score_4 表。

SELECT * FROM vb_score_4;

查询结果如下所示。

 id |  name  | object
----+--------+--------
  2 | banana |     87
  3 | peach  |     95
  1 | apple  |      0
(3 rows)

7、更新 vb_class_4 表上 id 为 1 的列，将 name 设置为 grape。

UPDATE vb_class_4 SET name = 'grape' WHERE id = 1;

8、查询 vb_score_4 表。

SELECT * FROM vb_score_4;

查询结果如下所示。

 id |  name  | object
----+--------+--------
  2 | banana |     87
  3 | peach  |     95
  1 | grape  |      0
(3 rows)

复杂约束检查的触发器

触发器可以强制执行引用完整性以外的完整性规则。下面的示例允许触发语句运行之前进行一些复杂检查。

本节使用以下表作为示例。

CREATE TABLE vb_score_5 (
  id     INT,
  name     VARCHAR2(10),
  object       NUMBER);

向表中插入数据。

insert into vb_score_5 values (1, 'apple', 74);
insert into vb_score_5 values (2, 'banana', 87);
insert into vb_score_5 values (3, 'peach', 95);

构建约束检查需要的数据表。

CREATE TABLE scoregrade (
  Loscore               NUMBER,
  Hiscore               NUMBER);

insert into scoregrade values (0, 100);

1、创建约束检查触发器函数，该函数会检查插入到 vb_score_5 的 object 列如果超出 0 ~ 100 的范围，则会报错。

CREATE OR REPLACE FUNCTION score_check_func() RETURNS TRIGGER AS
$$
DECLARE
  Minscore               NUMBER;
  Maxscore               NUMBER;
      score_out_of_range    EXCEPTION;
BEGIN
  SELECT Loscore, Hiscore INTO Minscore, Maxscore
  FROM scoregrade;

  IF (NEW.object < Minscore OR NEW.object > Maxscore) THEN
    RAISE score_out_of_range;
  END IF;

   RETURN NEW;

    EXCEPTION
      WHEN score_out_of_range THEN
        RAISE 'object % out of range',NEW.object
        USING ERRCODE = '20002';
END
$$ LANGUAGE PLPGSQL;

2、创建触发器，引用创建的触发器函数 score_check_func()。

CREATE TRIGGER score_check
  BEFORE INSERT OR UPDATE OF object ON vb_score_5
  FOR EACH ROW
  EXECUTE PROCEDURE score_check_func();

3、向 vb_score_5 表中插入 (4,'grape',200)。

INSERT INTO vb_score_5 VALUES (4,'grape',200);

返回结果如下所示。

ERROR:  object 200 out of range

复杂安全授权的触发器

触发器通常用于对表数据强制执行复杂的安全授权。仅使用触发器来强制执行无法使用数据库提供的数据库安全功能定义的复杂安全授权。

使用触发器强制执行复杂的安全授权时，建议使用 BEFORE 语句触发器。使用 BEFORE 触发器有以下优势：

  • 安全检查是在触发语句被允许运行之前完成的，这样就不会因为未经授权的语句而浪费工作。

  • 安全检查仅针对触发语句进行，而不针对受触发语句影响的每一行。

下面的示例展示了如何使用触发器禁止在周末和非工作时间更新表。

本节使用以下表作为示例。

CREATE TABLE vb_class_6 (
  id   INT,
  name     VARCHAR2(10),
  class       CHAR(1));

向表中插入数据。

insert into vb_class_6 values (1, 'apple', 'C');
insert into vb_class_6 values (2, 'banana', 'B');
insert into vb_class_6 values (3, 'peach', 'A');

1、创建触发器函数，该函数定义了在非工作时间不可修改 vb_class_6 表。

CREATE OR REPLACE FUNCTION class_permit_changes_func() RETURNS TRIGGER AS
$$
DECLARE
  stu_id         INTEGER;
  out_weekends   EXCEPTION;
  outworking_hours  EXCEPTION;
BEGIN
   IF (TO_CHAR(Sysdate, 'DAY') = 'SATURDAY' OR
     TO_CHAR(Sysdate, 'DAY') = 'SUNDAY') THEN
       RAISE out_weekends;
   END IF;

   IF (TO_CHAR(Sysdate, 'HH24') < 8 OR
    TO_CHAR(Sysdate, 'HH24') > 18) THEN
      RAISE outworking_hours; 
   END IF;

   RETURN NULL;

    EXCEPTION
        WHEN out_weekends THEN
          Raise 'Might not change vb_class_6 table during the weekend';
        WHEN outworking_hours THEN
          RAISE 'Might not change vb_class_6 table during outworking hours'; 
END
$$ LANGUAGE PLPGSQL;

2、创建触发器，引用创建的触发器函数 class_permit_changes_func() 。

CREATE TRIGGER class_permit_changes
  BEFORE INSERT OR DELETE OR UPDATE ON vb_class_6
  EXECUTE PROCEDURE class_permit_changes_func();

3、在非工作时间向 vb_class_6 插入数据。

INSERT INTO vb_class_6 VALUES (4,'grape','d');

返回结果如下所示。

ERROR:  Might not change vb_class_6 table during outworking hours