Questions tagged [trigger]
Procedural code automatically executed in response to a database event.
1,334
questions
0
votes
0answers
25 views
Incorrect number of likes and dislikes while aggregating from trigger
I have a table in PostgreSQL
feed_item_likes_dislikes(feed_item_id, user_id, vote)
where
feed_item_id is UUID
user_id is an integer
vote = TRUE then it is alike
vote = FALSE then it is a dislike
...
0
votes
0answers
23 views
Postgres: Event trigger for logging all dbs changes to one table
I am trying to create an generic event trigger, which I set up in every future databases, I create. This trigger should update the main db's table every time one of the other databases have add or ...
0
votes
1answer
22 views
Postgres per-statement update trigger relationship between old and new transition tables
Is there anyway to associate rows between the old transition table and new transition table for a per-statement update trigger in Postgres? docs
My use-case is that I have a "shadow" table ...
0
votes
1answer
65 views
Oracle: “table is mutating, trigger/function may not see it”
(Yes, this is one of those question again...)
I need to support Oracle database in my software and am now facing the same problem than with MS SQL Server.
I have a table I1 (called t_link in my ...
-1
votes
3answers
70 views
after insert trigger not firing after inserting with bcp
content of csv file
TEST_1|sl2sysxbar301.dv.local|{'message_type': 2, 'super_evt_non_error': {'a0': 0, 'a1': 0, 'a2': 0, 'a3': 0, 'event': 5528, 'event_string': 'ENERGY_CTRL_RIGHT_PROX_DOWN', '...
0
votes
1answer
24 views
Trigger updatedAt=NOW() for hunded-thousand rows will not actual [duplicate]
I am using SQL Trigger to update updatedAt column if there is a new data inserted/updated in PG 9.6.
Here is the function that will be called by trigger
CREATE FUNCTION sp_updatedatstamp() RETURNS ...
2
votes
1answer
75 views
Disabling triggers makes DELETE fast, but the table has no triggers?
I am deleting 130k out of 750k rows from a PostgreSQL table.
The first time, it took 8 hours to complete the delete query.
Second time, I added an index to the table, and rebuilt that index. Now it ...
0
votes
0answers
58 views
Database users are lost after restore - how to fix
We had many facilities that operate in areas where there is poor/no internet connection; the facility staff sent us monthly a backup of the facility database for us to restore it on our data center.
...
2
votes
2answers
119 views
Enforce that every company has at least one CEO
I've a requirement to ensure that every company has at least one CEO.
Example database model with requirements db<>fiddle.
Basically, a CEO can't be removed from a company without naming another ...
5
votes
3answers
151 views
Enforce uniqueness across a one-to-many-to-many bridged relationship when constraining to one-to-many
We have defined a series of configurations, where, driven by a RESTful API, end-users can build up new revisions. Some of the components of the configuration can have more than one value; a revision ...
1
vote
0answers
18 views
Update a PostgreSQL row with last user and timestamp
I would like to track who is the last user who updated the rows of a PostgreSQL table (v9) and when. This webpage explains how to do it with a trigger regarding the timestamp (I did not try it yet), I ...
-1
votes
1answer
32 views
how to get the email address from inserted data as the recipient in sp_send_dbmail
I would like to use the email address that is inserted in a column as the @recipient in sp_send_dbmail. This code is a trigger that I've been trying to achieve this with and is for test purposes only:
...
0
votes
1answer
158 views
Create a Hangfire job via SQL stored procedure
I know how to create Hangfire jobs via .NET C# code, but there are a few occasions where I'd like for a database trigger to execute a Hangfire job. I searched the web but found nothing so I attempted ...
2
votes
1answer
58 views
SQL Server DML auditing - Triggers vs SQL Server Audit Feature (vs temporal tables vs change data capture)
I am looking to implement an auditing process on an instance I have on SQL Server 2017. However, I am not finding a complete conclusion on which auditing feature would be best to use for a DB with a ...
0
votes
1answer
42 views
Stored procedure to change string to datetime in MySQL
This is a follow up to this question. Turns out the driver that I am using (can't change it) is not compatible with timestamp values. Lets say it is set in stone that I have to load the data into my ...
-1
votes
1answer
29 views
Why PG 13 does not allow deferring statement trigger constraints?
I noticed in PG 13 docs that only row constraint triggers can be deferrable.
Why not statement constraint triggers?
0
votes
0answers
30 views
How can I link together auditing of the sql and before and after values for row updates in Oracle
I had a number of triggers that used to work in Oracle 8i, that worked to capture both the before, and after values of a row, as well as the sql used to do the update.
(I realize there would be cases ...
0
votes
1answer
33 views
Insert into table if column <> “Text” for each project
I have created two triggers to insert into fact table if a new row is inserted into dim table. One checks if Project exists already and if not, then query will be triggered and adds 12 new rows per ...
0
votes
2answers
45 views
Using Trigger creates duplicates
I have created a trigger to inert into table2 if a new row is inserted into table1 (at least I thought).
The queries is like this:
CREATE TRIGGER project_insert
ON TABLE1
AFTER INSERT
AS
BEGIN
Insert ...
0
votes
2answers
55 views
Insert new rows when item is added to another table SQL [closed]
I have to tables. One dimension table with projects and one fact table with projects, value and a period(month).
Looking to add 12 new rows pr project ID into my fact table, when a new project is ...
0
votes
1answer
35 views
Cascading PostgreSQL triggers between three tables
I have a ‘test’ PostgreSQL 12 schema, defined with initial tables:
begin;
drop schema if exists test cascade;
create schema test;
set search_path = test,public;
create table jobs (
job_id serial ...
0
votes
1answer
35 views
MS SQL Creating Tables and Adding Values by a Trigger
Table_A is an existing table which has 3 columns Id (PK), Name, Stock.
I would like to create a trigger to create a table.
For example, Table_A has an item, Id (PK) = 3, Name = Apple, Stock = 10.
By ...
1
vote
1answer
34 views
Thousands of After Insert and Update Triggers Causing Performance Issues
We have a After Insert and Update trigger on one of our tables. The trigger basically makes a json payload and enqueues it in a RabbitMQ system.
Today a large insert script was run on the table (over ...
0
votes
3answers
69 views
Replicating contained SQL databases to customer in near real-time
We host a number of customer SQL Server databases that we need to replicate to external customer sites in near real-time. The main constraint we have is that, for security reasons, our databases are ...
1
vote
1answer
29 views
Junction table Trigger after update in another table
I'm just trying to create a trigger that performs an update in a junction table.
In principle, I have three tables with Many-to-Many relationship.
Table a:
a_id, a_is_active
Table b:
b_id, b_is_active
...
0
votes
1answer
26 views
How to trigger an event when row column crosses now()?
Is it somehow possible in postgres 12 to trigger an event when a table with an tsrange column crosses now(), or someone insert something that where the tsrange is prior to now()?
Triggers, as far i ...
1
vote
0answers
41 views
delete or rollback an insert if a trigger on insert fails
is it possible to delete or roll back insert statement if a trigger on that insert statement fails due to some reason.
for eg i have a trigger on insert into employe(id,name) values(1,'ali')
trigger
...
0
votes
0answers
24 views
PostgreSQL cascading triggers based on the condition of which other table triggered it or a value given by the other trigger?
I want to keep (slightly different) tables synced to one another and with only 2 tables, this is fine. Because of pg_trigger_depth() = 0 in the WHEN clause they will not trigger each other.
I ...
0
votes
1answer
27 views
Row Level Trigger for insert/update to a table
Given a table ITEM as below :
CREATE TABLE ITEM (
ItemNo int not null,
category varchar(20) not null,
Quantity int not null,
Price decimal(10,2) not null,
Total decimal(10,...
1
vote
3answers
150 views
Postgresql: How to pass an array to trigger function in TG_ARGV?
I've got some fairly generic Postgres trigger functions that I'd like to re-use across a few different tables. To that end, I'm passing arguments as part of the CREATE TRIGGER statement so they'll be ...
2
votes
2answers
239 views
MySQL best way to define a couple of columns as both null or both not-null
On a MySQL DB, I would like to find a way to force a couple of columns be both null or both not null at the insert / update time. This means that on each record, both columns are null or both columns ...
1
vote
1answer
91 views
How do I return all column values for the recently inserted row/rows?
I have these two tables in a PostgreSQL 12 database:
CREATE TABLE public.test (
entity_id uuid PRIMARY KEY DEFAULT uuid_generate_v4()
);
CREATE TABLE public.test_registration (
entity_id uuid ...
1
vote
0answers
28 views
PostgreSQL: UPDATE trigger on table with OID columns doesn't fire for lo_put-based UPDATEs
I've created an UPDATE trigger on a table containing an OID column. The intent here is to copy any changes from the OID column to a related BYTEA column.
Example table:
CREATE TABLE blah01 (
id ...
1
vote
1answer
19 views
Lock All Column Changes, Except One Column On Trigger
I want to ask how to lock all updates except one column with Trigger, is it possible to achieve this?
For example my table structure is:
--------------------------
id | name | age | point
------------...
1
vote
2answers
165 views
Calculate stock value with PostgreSQL
I'm working on inventory system web app. It is based on latest Ruby on Rails and PostgreSQL. I need to track products stock values base on purchases and sales quantities. You can check out my ...
0
votes
0answers
331 views
How can I auto-create a partition in a declarative partitioned table in Postgres when a newly created row has no matching partition?
My goal here is to make a partitioned table that's simplified below
Group
Info
A
blah1
B
blah2
but instead of having that as one table, I want to partition my table per group so that there's a ...
0
votes
1answer
16 views
how to compare two date column from two different tables by extracting specific parts in both columns using trigger/function in postgresql 10
I have written program something like below as per my question:
CREATE OR REPLACE FUNCTION hist_base_host_table_trig_func() RETURNS TRIGGER AS $hist_base_host_table_trig$
declare
username VARCHAR(40);...
1
vote
2answers
100 views
How to use tsvector_update_trigger with jsonb column?
I need to create cached ts_vector column from jsonb value. I try to create a trigger
CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
ON tags FOR EACH ROW EXECUTE PROCEDURE
...
2
votes
4answers
73 views
For every inserted row, generate series of rows in another table with AFTER INSERT trigger in PostgreSQL
Let's say that a customer places an order with certain quantity and I want to insert this information into table1 initialy. At the same time, I want to generate a series of rows in the table2 with a ...
0
votes
1answer
31 views
Postgresql - Identify process issuing a query
With this table
Table "scheduled_message"
Column | Type | Collation | Nullable | Default
----------------+--------------------------+-------...
0
votes
1answer
87 views
Perform update and raise notice in Postgres Function
I'm trying to add a notice (or something similar) to a function that is executed on update by a trigger in PostgreSQL 9.6 db. I want to let the user who edited the table that something happened.
I don'...
1
vote
1answer
58 views
Delayed or Asynchronous DML Trigger Execution
I have created a DML trigger (AFTER INSERT, UPDATE, DELETE) on a table
Trigger's logic takes about 30 seconds to execute
So if you change even 1 row, it takes ~ 30 seconds due to trigger execution
...
0
votes
1answer
33 views
Create a trigger to fill a field according to acondition in sql
I want to create a trigger to fill a field according to acondition as below description
For example:
I have a table for all insps that have attachments named: “inspectionAttachments” and I have ...
1
vote
1answer
36 views
Trigger creation for Sybase SQLAnywhere 16
I've been having the hardest time trying to create a trigger for a Sybase SAP SQLAnywhere 16 database following these docs.
Currently it's -131 syntax erroring on line 15/16 (the update statement), ...
1
vote
0answers
30 views
Two inserts in a single trigger [closed]
I have this trigger (audit trigger, in every table I want to audit).
CREATE FUNCTION changelog_trigger() RETURNS trigger AS $$
BEGIN
IF TG_OP = 'INSERT'
THEN INSERT INTO "...
0
votes
1answer
84 views
SQL Trigger to Insert a row in Table B is the record is inserted in Table A [closed]
I have a requirement where I have to Insert a Record into Table "Site" after the record is inserted into InventOperationalSiteStaging Table. I created a SQL trigger which is working fine for ...
2
votes
1answer
48 views
Limit connection to specific database with AlwaysOn Primary
Using AlwaysOn, I have a user which previously we had granted rights on a database on the primary, disabled the login on the primary server and then created the same login on the secondary so that ...
2
votes
2answers
202 views
PostgreSQL Trigger to Update Inserted Row With Data From Another Table
I have 2 tables in PostgreSQL-12, SENSORS (receives timeseries data feed from sensors) and LABELS (contains sensor labels & meta-data).
I am trying to create a PostgreSQL trigger to update the ...
1
vote
1answer
146 views
SQLite: Using count in triggers to update another column
UPDATE: Fixed. Not 100% sure why sqlfiddle.com didn't like my syntax, but using https://dbfiddle.uk/?rdbms=sqlite_3.27 worked. I did change the last WHERE clause to be based on the id column, so that ...
-1
votes
2answers
23 views
Create a trigger for a table whose values are dependent on another table
I have a hospital database and in that database I have an Appointments table and a Visits table. In the Appointments table I have a column named Validation that can have values 0 and 1.
I want to ...
