I would like some help. I have this code in sql Server , and now I want to do it in PostgreSQL
I encountered some problems, so I ask for your help, and I thank you in advance for your cooperation
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
I encountered some problems, so I ask for your help, and I thank you in advance for your cooperation
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条答案
按热度按时间wnavrhmk1#
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:
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!