如何修正程序结束触发错误

时间:2022-04-19 22:56:23

is there a way to fix this error or there is something i am missing in my code,im learning postgresql and i have been working on table inheritance and triggers i have two tables the temporary_object table and the persons table the persons table inherits all the properties the the temporary object has,then i have created a trigger function on the persons table which checks the records with the same id before updating the tables my problem comes when i try to run the insert query,im getting these errors

有办法修复这个错误或有我丢失我的代码,我学习postgresql和我一直在工作表继承和触发器有两个表temporary_object表并对其表人表继承的临时对象的所有属性,然后我已经创建了一个触发函数在人台上检查记录与相同的id之前更新表我的问题时,我试着运行插入查询,我得到这些吗错误

ERROR: end of trigger procedure achieved without RETURN
CONTEXT: function PL / pgSQL muli_function ()

ERROR: end of trigger procedure achieved without RETURN
SQL-state: 2F005
Context: The PL / pgSQL muli_function ()

this is by insert query

这是插入查询。

INSERT INTO persons (id, time_create, time_dead, First_name, Last_name) values (1, '2011-10-07 15:25:00 EDT', '2011-10-07 3:25 PM EDT', 'sathiya', 'james');

and this my trigger function and trigger itself

这是我的触发函数和触发器。

CREATE FUNCTION muli_function() RETURNS trigger AS '
BEGIN
  IF tg_op = ''UPDATE'' THEN
     UPDATE persons
     SET time_dead = NEW.time_create
     Where
         id = NEW.id
         AND time_dead IS NULL
         ;
  RETURN new;
  END IF;
END
' LANGUAGE plpgsql;

CREATE TRIGGER sofgr BEFORE INSERT OR DELETE OR UPDATE
        ON persons FOR each ROW
        EXECUTE PROCEDURE muli_function();

and these are my two tables query

这是我的两个表查询

CREATE TABLE temporary_object
(
  id integer NOT NULL,
  time_create timestamp without time zone NOT NULL,
  time_dead timestamp without time zone,
  PRIMARY KEY (id, time_create)
);

CREATE TABLE persons
(

  First_Name text,
  Last_Name text
)
INHERITS (temporary_object);

2 个解决方案

#1


2  

Try this:

试试这个:

CREATE FUNCTION muli_function() RETURNS trigger AS '
BEGIN
  IF tg_op = ''UPDATE'' THEN
     UPDATE persons
     SET time_dead = NEW.time_create
     Where
         id = NEW.id
         AND time_dead IS NULL
         ;

  END IF;
  RETURN new;
END
' LANGUAGE plpgsql;

UPD Better something like this:

UPD最好是这样:

CREATE FUNCTION muli_function() RETURNS trigger AS '
BEGIN
  IF tg_op = ''UPDATE'' THEN
    IF NEW.time_dead IS NULL THEN
     NEW.time_dead = NEW.time_create
    END IF;      
  END IF;
  RETURN new;
END
' LANGUAGE plpgsql;

#2


1  

You define the trigger procedure for an INSERT OR UPDATE trigger, but don't provide a case for the INSERT (just as mu is too short is commented), only the UPDATE. So the trigger executes the procedure for an INSERT, then it does not find anything to execute and exits without a RETURN, which is obviously an error.

您为插入或更新触发器定义触发器过程,但不提供插入的情况(就像mu太短被注释一样),只提供更新。因此,触发器执行插入过程,然后它没有找到要执行的任何东西,没有返回就退出,这显然是一个错误。

#1


2  

Try this:

试试这个:

CREATE FUNCTION muli_function() RETURNS trigger AS '
BEGIN
  IF tg_op = ''UPDATE'' THEN
     UPDATE persons
     SET time_dead = NEW.time_create
     Where
         id = NEW.id
         AND time_dead IS NULL
         ;

  END IF;
  RETURN new;
END
' LANGUAGE plpgsql;

UPD Better something like this:

UPD最好是这样:

CREATE FUNCTION muli_function() RETURNS trigger AS '
BEGIN
  IF tg_op = ''UPDATE'' THEN
    IF NEW.time_dead IS NULL THEN
     NEW.time_dead = NEW.time_create
    END IF;      
  END IF;
  RETURN new;
END
' LANGUAGE plpgsql;

#2


1  

You define the trigger procedure for an INSERT OR UPDATE trigger, but don't provide a case for the INSERT (just as mu is too short is commented), only the UPDATE. So the trigger executes the procedure for an INSERT, then it does not find anything to execute and exits without a RETURN, which is obviously an error.

您为插入或更新触发器定义触发器过程,但不提供插入的情况(就像mu太短被注释一样),只提供更新。因此,触发器执行插入过程,然后它没有找到要执行的任何东西,没有返回就退出,这显然是一个错误。