英文:
How to convert this trigger from TSQL to PL/pgSQL
问题
表和插入
CREATE TABLE authentification (
id serial primary key,
utilisateur varchar(15),
typeCompte varchar(15)
);
CREATE TABLE droit (
id serial primary key,
description varchar(15)
);
INSERT INTO droit (description) VALUES ('Description...');
CREATE TABLE permission (
id_authentification int references authentification (id),
id_droit int references droit (id),
primary key(id_authentification, id_droit)
);
触发器
CREATE OR REPLACE FUNCTION affecte_permission()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO permission (id_authentification, id_droit)
VALUES (NEW.id, 1);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER affectePermission
AFTER INSERT ON authentification
FOR EACH ROW
EXECUTE FUNCTION affecte_permission();
注意:我已经进行了必要的更改,以便在PostgreSQL中运行该SQL代码。
英文:
I have this code in SQL Server and need an equivalent in PostgreSQL.
Table and insertion
CREATE TABLE authentification (
id int primary key,
utilisateur varchar(15),
typeCompte varchar(15)
)
CREATE TABLE droit (
id int primary key ,
description varchar(15),
)
insert into droit values (1, 'Description...')
CREATE TABLE permission (
id_authentification int references authentification (id),
id_droit int references droit (id),
primary key(id_authentification,id_droit)
)
Trigger
go
create trigger affectePermission
on authentification
after insert
as
begin
declare @idAuthentification int
set @idAuthentification = (select id from inserted)
insert into permission values (@idAuthentification,1)
end
go
答案1
得分: 2
当然,让我们来翻译这个触发器从TSQL(SQL Server的SQL方言)到PL/pgSQL(PostgreSQL的SQL方言)的部分。涉及的触发器称为affectePermission,在向authentification表插入记录后触发,以在permission表中插入相应的记录。
以下是如何在PostgreSQL中定义类似的触发器:
CREATE OR REPLACE FUNCTION affecte_permission() RETURNS TRIGGER AS $$
BEGIN
INSERT INTO permission (id_authentification, id_droit)
VALUES (NEW.id, 1);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER affecte_permission
AFTER INSERT ON authentification
FOR EACH ROW EXECUTE PROCEDURE affecte_permission();
在TSQL触发器中,您使用了inserted特殊表来获取新插入行的id。在PostgreSQL中,您改为使用NEW特殊变量。NEW变量包含插入/更新操作的新数据库行,适用于行级触发器。此外,请注意,在PostgreSQL中,我们首先声明一个包含触发器操作的函数(在本例中为affecte_permission),然后使用CREATE TRIGGER语句将此函数绑定到表上。
然而,您应该考虑到,虽然使用触发器可以使数据库更加自包含和“智能”,但有时它可能会使应用程序逻辑更难理解或调试,因为部分逻辑嵌入在数据库中。如果您使用像Sequelize、TypeORM或Hibernate这样的ORM,可能更容易在应用程序层实现此类逻辑,从而更易于维护。
请记住始终要仔细权衡在数据库中实现逻辑与在应用程序中实现逻辑之间的权衡。
希望这对您有所帮助,祝您编码愉快!
英文:
Sure, let's dive into the translation of this trigger from TSQL (SQL Server's SQL dialect) to PL/pgSQL (PostgreSQL's SQL dialect). The trigger in question is called affectePermission, and it fires after an insert into the authentification table to insert a corresponding record into the permission table.
Here is how you might define a similar trigger in PostgreSQL:
CREATE OR REPLACE FUNCTION affecte_permission() RETURNS TRIGGER AS $$
BEGIN
INSERT INTO permission (id_authentification, id_droit)
VALUES (NEW.id, 1);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER affecte_permission
AFTER INSERT ON authentification
FOR EACH ROW EXECUTE PROCEDURE affecte_permission();
In the TSQL trigger, you used the inserted special table to get the id of the newly inserted row. In PostgreSQL, you use the NEW special variable instead. The NEW variable contains the new database row for INSERT/UPDATE operations in row-level triggers. Note also that in PostgreSQL, we first declare a function (affecte_permission in this case) that contains the trigger's operations, then we bind this function to a table using the CREATE TRIGGER statement.
However, you should consider that while using triggers might make the database more self-contained and "intelligent", it can sometimes make application logic more difficult to understand or debug, because parts of it are tucked away in the database. If you're using an ORM like Sequelize, TypeORM, or Hibernate, for example, it may be more maintainable to implement this sort of logic at the application layer.
Remember to always carefully weigh the trade-offs of implementing logic in the database versus in your application.
I hope this helps, and happy coding!
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论