如何编写一个PostgreSQL触发器，该触发器在将某个不正确的数字值插入现有SQL表列后会引起警告消息？

发布于 2025-02-08 03:03:02 字数 728 浏览 1 评论 0原文

我需要添加一个触发器，该触发器在SQL表中的“等级”列中的“等级”列中的现有行中插入或更改时，该触发器会引起警告消息。此代码示例仅在创建新行时才提出此消息。

当现有行中的一个值更改时，如何引起消息？
“等级”列中的值通过键与另一个表“级萨拉里”存储的列中的一列绑定。如何以一种方式编写插入/更改检查，以使消息在不指定具体的值的情况下启动消息（2、3、5-7），但仅指出“如果更改值，则在列中指定的值之外表“ grade_salary”的“然后提出错误消息”（不要让值修改）？

CREATE TRIGGER person
BEFORE INSERT ON hr."position"
FOR EACH ROW EXECUTE PROCEDURE person();

CREATE OR REPLACE FUNCTION person()
RETURNS TRIGGER
SET SCHEMA 'hr'
LANGUAGE plpgsql
AS $$
BEGIN
IF ((NEW.grade < 2) or (NEW.grade > 3 and NEW.grade < 5)
or (NEW.grade > 7)) THEΝ
    RAISE EXCEPTION 'Incorrect value';
END IF;
RETURN NEW;
END;
$$;

原文

I need to add a trigger that raises a warning message when a certain out of bounds (not 2, 3, 5-7) numeric value is inserted into or altered in an EXISTING row in a "grade" column in the sql table. This code example raises such a message ONLY when a NEW row is created.

How to raise the message when a value in the EXISTING row is altered?
Values in the "grade" column are tied via key to a column in another table "grade_salary" where they are stored. How to write the insertion/alteration check in such a way that raises the message without specifying the concrete correct values (2, 3, 5-7), but stating only that "IF changed value lies outside of the values specified in column "grade" of the table "grade_salary" THEN raise the error message" (and not let the value be modified)?

CREATE TRIGGER person
BEFORE INSERT ON hr."position"
FOR EACH ROW EXECUTE PROCEDURE person();

CREATE OR REPLACE FUNCTION person()
RETURNS TRIGGER
SET SCHEMA 'hr'
LANGUAGE plpgsql
AS $
BEGIN
IF ((NEW.grade < 2) or (NEW.grade > 3 and NEW.grade < 5)
or (NEW.grade > 7)) THEΝ
    RAISE EXCEPTION 'Incorrect value';
END IF;
RETURN NEW;
END;
$;

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

伪心 2025-02-15 03:03:02

一个人检查新值是否对应于一列中的现有值：

IF new_value not in (SELECT DISTINCT grade FROM grade_salary)
THEN RAISE EXCEPTION 'Inadmissible value.'

完全：

CREATE OR REPLACE FUNCTION person()
RETURNS TRIGGER
SET SCHEMA 'hr'
LANGUAGE plpgsql
AS $
declare 
new_value numeric;
begin
  select new.grade from "position" into new_value;
IF new_value not in (SELECT DISTINCT grade FROM grade_salary)
THEN RAISE EXCEPTION 'Inadmissible value.';
END IF;
RETURN NEW;
END;
$;

One checks whether the new value corresponds to an existing one in a column with:

IF new_value not in (SELECT DISTINCT grade FROM grade_salary)
THEN RAISE EXCEPTION 'Inadmissible value.'

In full:

CREATE OR REPLACE FUNCTION person()
RETURNS TRIGGER
SET SCHEMA 'hr'
LANGUAGE plpgsql
AS $
declare 
new_value numeric;
begin
  select new.grade from "position" into new_value;
IF new_value not in (SELECT DISTINCT grade FROM grade_salary)
THEN RAISE EXCEPTION 'Inadmissible value.';
END IF;
RETURN NEW;
END;
$;

回复收藏 0 原文

~没有更多了~