如何使用 liquibase 为 postgreSQL 数据库创建触发器？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18751174/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I create triggers for a postgreSQL db using liquibase?
提问by Ann Kilzer
I'm using the dropwizard-migrations module for liquibase db refactoring. See the guide here: http://dropwizard.codahale.com/manual/migrations/
我正在使用 dropwizard-migrations 模块进行 liquibase 数据库重构。请参阅此处的指南：http: //dropwizard.codahale.com/manual/migrations/
When I run java -jar my_project.jar db migrate my_project.yml
当我运行 java -jar my_project.jar db migrate my_project.yml
I get the following error:
我收到以下错误：
ERROR [2013-09-11 20:53:43,089] liquibase: Change Set migrations.xml::11::me failed. Error: Error executing SQL CREATE OR REPLACE TRIGGER add_current_date_to_my_table BEFORE UPDATE ON my_table FOR EACH ROW EXECUTE PROCEDURE change_update_time();: ERROR: syntax error at or near "TRIGGER" Position: 19
错误 [2013-09-11 20:53:43,089] liquibase：更改集 migrations.xml::11::me 失败。错误：错误执行 SQL CREATE OR REPLACE TRIGGER add_current_date_to_my_table BEFORE UPDATE ON my_table FOR EACH ROW EXECUTE PROCEDURE change_update_time();：错误：“TRIGGER”位置或附近的语法错误位置：19
Here are some relevant changesets from my migrations.xml file:
以下是我的 migrations.xml 文件中的一些相关变更集：
<changeSet id="1" author="me">
<createProcedure>
CREATE OR REPLACE FUNCTION change_update_time() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
NEW.updated_at := CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$;
</createProcedure>
<rollback>
DROP FUNCTION change_update_time();
</rollback>
</changeSet>
<changeSet id="2" author="me">
<preConditions>
<not>
<tableExists tableName="my_table"/>
</not>
</preConditions>
<createTable tableName="my_table">
<column name="_id" type="integer" defaultValue="0">
<constraints nullable="false"/>
</column>
<column name="updated_at" type="timestamp without time zone" defaultValue="now()">
<constraints nullable="false"/>
</column>
</createTable>
</changeSet>
<changeSet id="3" author="me">
<sql splitStatements="false">
CREATE OR REPLACE TRIGGER add_current_date_to_my_table BEFORE UPDATE ON my_table FOR EACH ROW EXECUTE PROCEDURE change_update_time();
</sql>
<rollback>
DROP TRIGGER add_current_date_to_my_table ON my_table;
</rollback>
</changeSet>
Is there any way I can create the trigger add_current_date_to_my_table? Is this redundant with the "RETURNS trigger" from creating the function?
有什么办法可以创建触发器 add_current_date_to_my_table？这与创建函数的“RETURNS 触发器”是多余的吗？
回答by Ann Kilzer
The solution is:
解决办法是：
<changeSet id="3" author="me">
<sql>
DROP TRIGGER IF EXISTS add_current_date_to_my_table ON my_table;
CREATE TRIGGER add_current_date_to_my_table BEFORE UPDATE ON my_table FOR EACH ROW EXECUTE PROCEDURE change_update_time();
</sql>
<rollback>
DROP TRIGGER add_current_date_to_my_table ON my_table;
</rollback>
</changeSet>
H/T Jens.
H/T 延斯。
