Questions tagged [postgresql-triggers]
44 questions
0
votes
2 answers
Create notify trigger for specific JSON value in inserted row
I have a table with event entity
create table event_entity
(
id varchar(36) not null
constraint constraint_4
primary key,
details_json varchar(2550),
event_time bigint,
type varchar(255),
…
hoozr
- 403
- 4
- 15
0
votes
1 answer
How to select all inserted rows to execute an insert trigger with a stored procedure in postgresql?
I'm trying to set an "after insert" trigger that executes a procedure. The procedure would take all inserted rows in table A, group them by a column and insert the result in a table B. I know about "new" variable but it gets inserted rows one by…
JoanS
- 15
- 4
0
votes
0 answers
Need to create audit in postgresql for two tables
We have two tables product and product_unapproved :
Product…
Nidhi Maheshwari
- 1
- 1
0
votes
2 answers
How to prevent recursion in trigger on UPDATE operation?
I have simple trigger:
CREATE OR REPLACE FUNCTION nmck_decrease_percent_calc() RETURNS TRIGGER AS
$BODY$
DECLARE
s_price integer;
BEGIN
SELECT "lotMaxPrice" into s_price FROM lots WHERE "purchaseNumber" = new."purchaseNumber";
…
Dmitry Bubnenkov
- 9,415
- 19
- 85
- 145
0
votes
0 answers
PostgreSQL insert row with trigger function
I have a table (machines_data_paired) like this one:
timestamp
id
data
2022-07-01 17:23:13.437
1
{'foo': 'bar'}
2022-07-01 17:23:14.937
2
{'foo': 'bar'}
2022-07-01 17:23:15.437
1
{'foo': 'bar'}
When a new data element is inserted in…
gpare
- 41
- 1
- 7
0
votes
0 answers
Creating SQL Functions
I've created the function, but have some mistakes. I understand the logic, but can't connect it.Maybe you will be able to help me to understand it.
Given database schema:
• Contacts (user ID, fname, lname, cell, city, country) PK(user ID)
•…
Alex
- 11
- 2
0
votes
2 answers
PostgreSQL OLD not working in after update statement level trigger
I'm trying to update course points by the sum of course's lessons points.
It is working perfectly if I do select particular course ID like this:
BEGIN
UPDATE course
SET points = (SELECT COALESCE(SUM("lesson"."points"), 0) AS "sum_points" FROM…
Jamolkhon Akhmedov
- 116
- 6
0
votes
1 answer
PostgreSQL trigger function not working properly
I have a trigger function:
CREATE OR REPLACE FUNCTION Day_21_bankTriggerFunction()
RETURNS TRIGGER
LANGUAGE plpgsql
AS
$$
DECLARE
act VARCHAR(30);
BEGIN
SELECT account_number INTO act
DELETE FROM depositor
WHERE depositor.account_number = act;
…
Evan Day
- 23
- 4
0
votes
1 answer
Postgresql trigger not update - stack depth limit reached
everyone!
i want to do a very simple trigger that sees if some conditionals are 0 they set false on the same table, buy i am doing wrong and it s not updating
CREATE or REPLACE FUNCTION chequeo_datos() RETURNS trigger AS $$
BEGIN
IF NEW.val_cuota =…
Gonzalo
- 11
- 2
0
votes
2 answers
Postgres Trigger to INSERT UPDATE DELETE on similar derivative table
Description:
I am running postgresql 13
I have two tables under different schemas, t1 and t2.
t2 is derivative of t1 in the sense that they share all the same
columns and data, but t2 is always downstream of t1 as far as
validity.
The rows in both…
Algorant
- 81
- 7
0
votes
1 answer
How to execute same query in remote postgres server from inside "BEFORE TRIGGER"
I am planning to run exact same queries on the remote database server exactly when query executing on local server from inside BEFORE INSERT,UPDATE and DELETE TRIGGERs.
My use case is something like:
Using db_link for making connection to remote…
Ankit Kumar
- 1
- 3
0
votes
1 answer
insert trigger to manipulate timestamps of existing rows
I have a table with 2 date columns
CREATE TABLE test
(
id serial PRIMARY KEY,
code integer NOT NULL,
eff_date timestamp default now(),
exp_date timestamp default '2025-12-31'
);
I want to update the exp_date of an existing row when a new…
Niv
- 271
- 1
- 7
- 16
0
votes
1 answer
Setting NEW.column in "before update" trigger sets values from previous trigger call
I'm trying to create a before update trigger for a table. Basically, on each row update I need to set additional column on the same row on the same table.
However, for some reason, each trigger call results in setting values from a previous trigger…
Denis Yakovenko
- 3,241
- 6
- 48
- 82
0
votes
1 answer
How to acheive a "stolen_at" field which is populated when the "status" field is updated? TRIGGER function and GENERATED ALWAYS AS attempts attached
How to update stolen_at such that it is populated with the current timestamp when status is updated from INSTOCK to STOLEN?
I've attempted a trigger based approached but it contains a bug:
SQL Error [2F005]: ERROR: control reached end of trigger…
Greg
- 8,175
- 16
- 72
- 125
0
votes
1 answer
Trigger placing a point in qgis to autocomplete a column by adding the next in an alphanumeric sequence in Postgresql
In QGIS, I have a table of points, and a table of areas. When I place a point in an area, I need the following to happen. Grab the ID of the area (called plat_page) the new point is being placed in. Then I want to insert the next value in a sequence…
AThomspon
- 135
- 12