Tagged Questions
1
vote
1answer
23 views
PostgreSQL 9.3: Primary key violated by a trigger INSERT
My problem
Consider a table t with many frequent updates from users, from which only the last few are relevant.
In order to keep the table size reasonable, whenever a new row is inserted old rows ...
2
votes
0answers
65 views
Postgresql trigger - get all changed rows after transaction
I have invoice_item table and invoice table. I need to check total price of Order after each transaction. Since I still want to be able to change things, I need trigger after each transaction.
CREATE ...
2
votes
1answer
30 views
Rewrite sqlite trigger to postgresql
Can you help me please with this problem? I need rewrite this simply trigger from sqlite to PostgreSQL format:
In sqlite:
CREATE TRIGGER connections_INSERT_update_connection_root_trg
...
1
vote
2answers
68 views
how should i do this trigger?
I am a newbie to the sql world :)
Right now i'm trying to create my first mysql trigger that would either update a row or insert a new one. Could you help me with that ?
Here is the code :
DELIMITER ...
0
votes
0answers
57 views
How to avoid locking problems while disabling/enabling triggers in a PostgreSQL function?
Whenever I try to disable a trigger from within a function, I get following error:
ERROR: cannot ALTER TABLE "datace" because it is being used by active queries in this session
It seems logical ...
1
vote
0answers
91 views
Storing changes to stored procedure and triggers in PostgreSQL
I want a mechanism where I can store/track changes in stored procedure and triggers.
I want to track these logs in some table. What is the best possible way ?
1
vote
3answers
77 views
Trigger or Stored Procedure?
Let's say I have two tables, foo and bar:
CREATE TABLE foo(
foo_id serial NOT NULL,
count integer NULL,
PRIMARY KEY(foo_id)
);
CREATE TABLE bar(
bar_id serial NOT ...
-1
votes
1answer
85 views
Access table field on creating a trigger postgresql
How can I access table field on creating a trigger?
CREATE TRIGGER foo_trigger
AFTER INSERT OR UPDATE OR DELETE ON foo
FOR EACH ROW EXECUTE PROCEDURE proc(foo.field);//here goes the parameter
1
vote
1answer
186 views
I have an INSTEAD OF trigger, but PostgreSQL still complains while I insert into view
I have created a view and an INSTEAD OF trigger for inserting/ updating/ deleting from that view. Now I have tried to insert some data to the view, and PostgreSQL returns following error (translation ...
2
votes
1answer
122 views
How to reuse an update trigger for multiple tables in postgresql?
In the database for a new project, I've started the convention of having each table have a last modified timestamp column. To implement this, I wrote a trigger for each table:
CREATE TRIGGER ...
0
votes
1answer
52 views
How to ensure a trigger is fired after variable number of inserts?
I have a 1:N relationship between two tables - let's call them "foo" (the parent table) and "bar". I have some triggers on both "foo" and "bar" tables. I insert data in a single transaction, first to ...
4
votes
1answer
128 views
How to access NEW or OLD field given only the field's name?
I'm writing a validation trigger. The trigger must validate that the sum of an array equals another field. Since I have many instances of this validation, I want to write a single procedure and create ...
1
vote
2answers
101 views
Basic trigger in PL/pgSQL, to flip a boolean field if another field has text
I want a BOOLEAN field to be set to TRUE when a sibling TEXT field is set to a non-empty string. Vice versa, the BOOLEAN is set to FALSE when TEXT field is set to empty string.
The following function ...
3
votes
2answers
284 views
Insert RECORD into a table in a trigger function
I would like to insert a RECORD data type variable (NEW variable) into a table in a trigger. What would the SQL look like?
The following attempts were unsuccessful:
EXECUTE 'INSERT INTO my_table ...
3
votes
2answers
404 views
Call multiple functions from trigger?
I run PostgreSQL-9.2.4
Is it possible to call 2 functions from a trigger?
Let's say I have two functions for two different tables to be executed if following trigger fires:
Trigger:
CREATE TRIGGER ...
4
votes
1answer
62 views
selective undoing of commited transactions in PostgreSQL
I have inherited a small (<1M rows) PostgreSQL DB which contains complex business rules split across several tables. Updates are very light, reads not so heavy, but the data is extremely valuable.
...
1
vote
1answer
95 views
Create constraint for stopping mass update to a table
I want to create a constraint so that if more than 5 rows in a table are getting updated with an UPDATE statement it should warn the user / block the query.
4
votes
1answer
183 views
Stored procedure deadlocking itself
I have a strange situation, seen from the log:
Process 37278 waits for ExclusiveLock on advisory lock [16421,999999,12864385,2]; blocked by process 53807.
Process 53807 waits for ExclusiveLock on ...
0
votes
2answers
462 views
Recursive update for tree structure in PostgreSQL
I have the following structure of table.
id chNum parentid
--- ------ ---------
1 1 NULL
2 1.1 1
3 1.1.1 2
4 2 ...
1
vote
1answer
436 views
Disabling triggers in stored procedures
I am building a database application that uses functions and triggers. One trigger I have limits the possible data that can be put in on INSERT to make sure a record goes through a defined set of ...
2
votes
2answers
226 views
Trigger update does not seem to finish
I'm new to PostgreSQL triggers & functions and such.
I've got a table: keywords and each keyword should be unique. So when I try to insert a keyword that already exists, it should simply update ...
1
vote
1answer
319 views
postgres+JDBC: fire trigger with non autocommit transaction
i have a jdbc client that make an two INSERT query on database whitout autocommit, and a trigger on database that fires when a insertion is done on a certain table
public foo() throws MyException {
...
4
votes
1answer
493 views
What are the privileges required to execute a trigger function in PostgreSQL 8.4?
What are the privileges required to execute a trigger function in PostgreSQL 8.4?
It seems that the privileges set to a role does not matter to execute a trigger function. I think I have seen some ...
1
vote
1answer
162 views
Postgresql - how to drop a trigger [closed]
A while ago I've created a trigger in Postgresql, update_user, which sets the update_date to now() for table users. Now I want to drop this trigger.
The following queries don't work:
drop trigger ...
0
votes
1answer
183 views
Postgres - Schema information within Trigger?
Background : (Multi-tenant design;separate schema per customer, all table structures identical;Data-warehouse / non-OLTP system here; Postgres 9.2.x).
Reviewing patterns for the following problem: I ...
-1
votes
1answer
319 views
Passing argument in trigger dynamically
CREATE TRIGGER audit_proc_tr
AFTER INSERT OR UPDATE OR DELETE
ON "log".hi
FOR EACH ROW
EXECUTE PROCEDURE "log".audit_proc(argument);
CREATE OR REPLACE FUNCTION ...
2
votes
1answer
192 views
INSERT trigger dying with with #target columns not equal to #expressions, but they are equal
I am trying to write my first trigger. It is an insert trigger as follows:
<b>Fatal error</b>: postgres7 error: [-1: ERROR: INSERT has more target columns than expressions at character ...
0
votes
1answer
1k views
one trigger for multiple tables
i have a trigger in PostgreSql 9.1 that i want to fire on inserts of several tables. is it possible to have it affect all these tables instead of creating the trigger for all these tables?
i have 58 ...
1
vote
1answer
586 views
How can I ensure that in a insert trigger a column gets filled before other column?
The bigger picture:
tableX represents pipelines and tableY streets. A pipeline crosses/goes along many streets, and I want to know which street is crossed the most. First, I am segmentizing each ...
1
vote
1answer
548 views
Inherit audit columns and triggers
Background
I have a database with dozens of tables, some of which should have audit columns.
Problem
I'd like to avoid the drudgery of creating audit columns manually, and write the update and ...
1
vote
1answer
65 views
PostgreSQL custom domain with trigger
Is it possible to create custom domain / data type and associate some trigger with it, for example hash password?
I will have for example password domain and if I set some value to this column, it ...
2
votes
2answers
823 views
Postgresql after 'for each row' triggers don't actually execute after each row?
If I have a single sql statement that inserts or updates lots of rows and a db trigger that runs after each row is inserted or updated, it seems like the triggers run after all the rows are inserted ...
5
votes
1answer
548 views
Check what event called the trigger on PostgreSQL?
I'm trying to check what event called my trigger, like INSERT, UPDATE or DELETE.
Oracle triggers can check this in a simple 'IF' statement:
IF INSERTING, IF UPDATING or even IF DELETING
Is there a ...
1
vote
1answer
69 views
Deadline rule date input after now
Hi I'm trying to find a way to ensure deadlines inputted are later than the current time and date.
Currently I'm attempting this with a rule.
CREATE OR REPLACE RULE "DeadlineInput" AS
ON INSERT ...
1
vote
1answer
508 views
Triggers not being run on a partitioned table
I'm using PostgreSQL. I have a table that I have partitioned into three sub-tables - and as far as I can tell, this all works correctly. Rows are correctly inserted into the appropriate partition and ...
2
votes
2answers
165 views
Validation of availability for a new order
I have a management application of sales, stock and payment on a warehouse whole saler from a web interface. In particular, when a order is effectuated it must create a line corresponding to each ...
2
votes
2answers
778 views
How to specify trigger execution order under PostgreSQL?
I'm using "classic" time-based partitioning using triggers. I have found a need for a separate trigger, which runs on the original table.
CREATE TABLE twitter_interactions(...);
CREATE OR REPLACE ...
3
votes
1answer
575 views
PostgreSQL pl/perl trigger, differentiate null vs empty
I've been trying to speed up a generic auditing trigger that I had written in pl/pgsql some time back. On update, it generates a list of columns in the table being updated and inserts rows in the ...
9
votes
2answers
3k views
Trigger: move deleted rows to archive table
I have a small (~10 rows) table called restrictions in my PostgreSQL database, where values are deleted and inserted on a daily basis.
I would like to have a table called restrictions_deleted, where ...
19
votes
1answer
2k views
Is there a good way to run a trigger for each record in a postgres table?
I have a system where I can't control the design of some tables (replicated via Slony-I), and so I have a series of what we refer to as 'shadow tables', where I extract some information out of the ...