Questions tagged [postgresql-triggers]
44 questions
0
votes
1 answer
Mixed SRID's in trigger stopping qgis from committing changes
I have a trigger that takes a line, grabs its ST_StartPoint and ST_EndPoint and then grabs the nearest point to those endpoints, and assigns a node_id to a column. This Fiddle shows the trigger as well as some example data. When running this…
AThomspon
- 135
- 12
0
votes
1 answer
PostgreSQL trigger function to capture updated field
I am querying data from server to update fields in my local DB.
But, I have more than 100 columns hence I just want to know which column has new value(different than previous) so that I can use only those fields to prepare/make/build update command…
R_S_C
- 75
- 1
- 10
0
votes
0 answers
PostgreSQL Trigger on last deleted line
I created a trigger that updates the number of species counted from the "effectif" table to the "citation" table.
It works well except when I delete the last row, the calculation does not perform and remains at the last state. For example, if I…
Mathias
- 13
- 2
0
votes
0 answers
PostgreSQL TRIGGER on WITH (UPDATE ..)UPDATE on 2 tables
I have an SQL query like this:
WITH whatever1 AS (
UPDATE
table1
SET
field1 =
WHERE
id =
RETURNING id, field1
)
UPDATE
table2
SET
…
Lukas Salich
- 959
- 2
- 12
- 30
0
votes
0 answers
Automatically copy schema changes to another schema in postgresql
I have a database that is not well designed. To design a new database, I came up with the idea of having 4 copies of each schema (A1, Backup_A1, History_A1, Test_A1). So if a row is added/updated in any of the tables in A1, the same will replicate…
DumbLoawai
- 11
- 4
0
votes
2 answers
PostgreSQL create trigger: Syntax error on insert action
I want to create a trigger in PostgreSQL.
I have a products table and a categories table. If a new tuple is inserted into the products table with a category_id, which does not exist yet, I want to create a category with this exact category_id. I…
asdf
- 11
0
votes
0 answers
Instead of Insert Trigger to prevent from duplicate record in my database
Actually my issue is that,
I have an Azure time trigger function that hitting an API on a daily basis and fetching data from that API and mapping that data through my model classes and inserting that data into my application database.
So there are…
Puneet Gahlot
- 11
- 1
- 2
0
votes
1 answer
Postgres Function Trigger Sequence Dependency Error
I have the following functions and trigger with sequence setup:
I want to create a function and trigger that anytime I add a new row to STRATEGY_SITES table, the 'SITE_NUM' field will have the new sequential number from SITENUM_SEQ. Schema name is…
sanc_mn
- 3
- 3
0
votes
2 answers
How will it be in PostgreSQL?
i have code for MySQL but i need to PostgreSQL. Can you help me convert from MySQL to PostgreSQL?
Create TRIGGER date_reservation
BEFORE INSERT ON reservation
FOR EACH ROW
BEGIN
IF
NEW.date_reservation IS NULL OR NEW.date_reservation = ''
THEN…
piotras8845
- 11
- 2
0
votes
2 answers
Update another table through a trigger where new value is the result of a SELECT query
I have these tables:
Users
Skills (name - string, count - integer)
Has_skills (skill_id - skills.id, user_id users.id)
Has_skills is a many to many table between the first two through these FK:
user_id (users.id) and skill_id (skills.id).
What I…
krcky
- 81
- 2
- 11
0
votes
2 answers
Keep a table column at the earliest related value of a column in another table
I have two related tables. Lets call them event_types and occurrences with the following columns:
event_types
-------------
id VARCHAR(30)
name VARCHAR(50)
first_event Timestamp
and
occurrences
-------------
id VARCHAR(30)
event_type_id…
Stoecki
- 585
- 1
- 3
- 16
0
votes
1 answer
PostgresSQL : Update Certain Column in Table-B After Certain Column in Table-B is Updated
I have 2 table in Postgres, let's say Category and Thread with relation one-to-many
Category
id | title | created_at | deleted_at
-----+-------+------------+-----------
Thread
id | category_id | title | created_at | deleted_at…
Jim Johnson
- 71
- 4
0
votes
0 answers
PostgreSQL: Execute a trigger after all associated rows are inserted/updated
I need to track data changes in DB and execute a trigger. The base records have a few associated (many-to-one) records in different tables. When any of the records (base or associated) are updated/inserted, the trigger has to be executed to perform…
chumakoff
- 6,807
- 2
- 23
- 45
-1
votes
1 answer
Why is the column value not persisting after Postgres function triggers after update?
I have a table called Projects where I need to set a certain column value after an update is executed and the record matches certain conditions. The function triggers correctly and there are no errors but the updated column value does not…
Samuel Alito
- 15
- 2