Questions tagged [trigger]

Procedural code automatically executed in response to a database event.

Filter by
Sorted by
Tagged with
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 ...

1
2 3 4 5
27