postgresql - 如何在插入显示测试消息之前创建触发器功能

Question

我有一张 table

CREATE TABLE test.emp (
  empname text,
  salary integer,
  last_date timestamp,
  last_user text
);

和函数

CREATE FUNCTION test.emp_stamp() RETURNS trigger AS $emp_stamp$
BEGIN
    -- Check that empname and salary are given
    IF NEW.empname IS NULL THEN
        RAISE EXCEPTION 'empname cannot be null';
    END IF;
    IF NEW.salary IS NULL THEN
        RAISE EXCEPTION '% cannot have null salary', NEW.empname;
    END IF;

    -- Who works for us when she must pay for it?
    IF NEW.salary < 0 THEN
        RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
    END IF;

    -- Remember who changed the payroll when
    NEW.last_date := current_timestamp;
    NEW.last_user := current_user;
    RETURN NEW;
END;
$emp_stamp$ LANGUAGE plpgsql;

如何显示消息无需更新值我如何修改我的功能

触发器是

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON test.emp
   FOR EACH ROW EXECUTE PROCEDURE test.emp_stamp();

Answer 1

我认为您需要做的就是 1) 确保它是更新而不是插入(否则对 old 的引用将没有任何意义，并且 2)与现有记录进行比较。由于 Employee name 似乎是 PK，最后两个字段只是为了捕获变化，我猜你只想测试薪水:

-- Who works for us when she must pay for it?
IF NEW.salary < 0 THEN
    RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
elsif TG_OP = 'UPDATE' and new.salary = old.salary then
    RAISE EXCEPTION 'salary is already %', NEW.salary;
END IF;

当然，您也可以将其用于任何领域。如果你想确保至少改变了一个，那就是这样的:

elsif TG_OP = 'UPDATE' and
   new.salary = old.salary and
   new.last_date = old.last_date and 
   new.last_user = old.last_user then
    RAISE EXCEPTION 'Update does not alter any data';
END IF;