在PostgreSQL中创建触发器 [英] Create a trigger in postgresql

问题描述

我的触发器有问题：

 创建或替换功能process_fillDerivedFrom_used（）返回触发器为$ fillDerivedFrom_used $ 
 DECLARE 
 prog varchar（255）; 
当前varchar（255）; 
 BEGIN 
选择u.iduseentity作为prog，g.idCreatedEntity作为当前
 FROM实体e 
 JOIN使用u ON e.identity = u.iduseentity 
 JOIN活动a在g上生成的a.idactivity = u.idusedactivity 
 JOIN在g.idcreatoractivity = a.idactivity 
 
插入到派生变量值中（prog，current）
 END; 
 
 $ fillDerivedFrom_used $ LANGUAGE plpgsql; 
创建触发器fillDerivedFrom_used 
在emp 
上插入，更新或删除后，为每个行执行过程process_fillDerivedFrom_used（）; 
  

我使用3个表，查询是正确的，所以我认为错误不是来自这里。但是，当我在终端中复制完该触发器后，什么也没有发生，也没有错误消息，我可以在终端中进行写操作，但无法执行任何查询，就像我忘记了触发器中的某些内容一样，所以我认为它并不是完全创建的。 / p>

我想我混合了两种我在学校学习过的语言PL / SQL和PostgreSQL。

谢谢为您提供帮助

解决方案

我不知道您遇到了什么问题。一个很明显。 as 不用于变量； 成是。另外，您应该命名变量，以免它们与列名冲突：

 创建或替换功能process_fillDerivedFrom_used（）以T的形式返回触发器fillDerivedFrom_used $ 
 DECLARE 
 prog v_varchar（255）; 
 current v_varchar（255）; 
 BEGIN 
选择u.iduseentity作为prog，选择g.idCreatedEntity作为当前
 INTO v_proc，v_current 
从实体e联接
使用u 
 ON e。身份= u.iduseentity加入
活动a 
在a.idactivity上= u.idusedactivity加入JOIN 
由g 
在g.idcreatoractivity = a.idactivity 
 
插入到派生变量值（v_prog，v_current）
 END； 
  

当然，这引出了为什么要使用变量的问题：

 创建或替换功能process_fillDerivedFrom_used（）将触发器返回为$ fillDerivedFrom_used $ 
 BEGIN 
插入DERIVEDFROM（程序，当前）- -或任何列名称为
 SELECT u.iduseentity作为prog，g.idCreatedEntity作为当前
 FROM实体e JOIN 
使用u 
 ON e.identity = u.iduseentity JOIN 
活动a 
在a.idactivity上= u.idusedactivity加入由g 
在g.idcreatoractivity = a.idactivity上生成的
； 
 END； 
  

I have a problem with my trigger:

CREATE OR REPLACE FUNCTION process_fillDerivedFrom_used() RETURNS TRIGGER AS $fillDerivedFrom_used$
  DECLARE
    prog      varchar(255);
    current   varchar(255);
  BEGIN
    SELECT u.iduseentity as prog ,g.idCreatedEntity as current
    FROM entity e
    JOIN used u ON e.identity=u.iduseentity
    JOIN activity a ON a.idactivity=u.idusedactivity
    JOIN generatedby g ON g.idcreatoractivity=a.idactivity

    INSERT INTO DERIVEDFROM VALUES (prog,current)
  END;

$fillDerivedFrom_used$ LANGUAGE plpgsql;
CREATE TRIGGER fillDerivedFrom_used
AFTER INSERT OR UPDATE OR DELETE ON emp
    FOR EACH ROW EXECUTE PROCEDURE process_fillDerivedFrom_used();

I use 3 tables, the query is correct so I think the error doesn't come from here. But when I copy past this trigger in my terminal nothing happen, no error message, I can write in terminal but I can't execute any query, like I have forgot something in my trigger so I think it's not completely created I think.

I think I mix 2 languages PL/SQL that I have learn at school and the postgresql.

Thank you for you help

解决方案

I don't know what problems you are having. One is obvious. as is not used for variables; into is. Also, you should name variables so they do not conflict with column names:

CREATE OR REPLACE FUNCTION process_fillDerivedFrom_used() RETURNS TRIGGER AS $fillDerivedFrom_used$
  DECLARE
    prog      v_varchar(255);
    current   v_varchar(255);
  BEGIN
    SELECT u.iduseentity as prog, g.idCreatedEntity as current
    INTO v_proc, v_current
    FROM entity e JOIN
         used u
         ON e.identity = u.iduseentity JOIN
         activity a
         ON a.idactivity = u.idusedactivity JOIN
         generatedby g
         ON g.idcreatoractivity = a.idactivity

    INSERT INTO DERIVEDFROM VALUES (v_prog, v_current)
END;

Of course, this begs the question of why you are using variables at all:

CREATE OR REPLACE FUNCTION process_fillDerivedFrom_used() RETURNS TRIGGER AS $fillDerivedFrom_used$
  BEGIN
    INSERT INTO DERIVEDFROM (prog, current)  -- or whatever the column names are
        SELECT u.iduseentity as prog, g.idCreatedEntity as current
        FROM entity e JOIN
             used u
             ON e.identity = u.iduseentity JOIN
             activity a
             ON a.idactivity = u.idusedactivity JOIN
             generatedby g
             ON g.idcreatoractivity = a.idactivity;    
END;

这篇关于在PostgreSQL中创建触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！