Questions tagged [trigger]

Procedural code automatically executed in response to a database event.

Filter by
Sorted by
Tagged with
0 votes
0 answers
9 views

ORA-04091 table is mutating trigger/function may not see it when cascading, works otherwise

I have the following two tables: CREATE TABLE entries ( id NUMBER(20, 0) NOT NULL PRIMARY KEY, first_name VARCHAR2(512) DEFAULT NULL ...
0 votes
0 answers
25 views

Does enabling or disabling a trigger cause table statistics to go out of date (SQL Server)?

Whenever I modify a table (adding an index or adding or dropping a new column), I always run a sp_recompile on the table to make sure the statistics are updated. When a trigger is enabled or disable ...
1 vote
1 answer
33 views

help with mariadb after update trigger

I have a table that is shared among 7 other tables and I want to synchronize a new part is added to the parts table. new rows are created in each of the 7 other tables, using the part_id from the ...
0 votes
0 answers
40 views

PostgreSQL lower case or remove double quotes on every table name before select query

We are migrating from OracleDB to PostgreSQL and all the tables name in database are lower case while the script from application is mixed with double quotes. For example the code in application like ...
0 votes
1 answer
43 views

Extended Events for Enable Disable Trigger

We have a table that has a trigger defined on it Sometimes, some application runs disable trigger command, then does something, then runs enable trigger to enable it back Scanning the GIT, developers ...
1 vote
1 answer
42 views

Create postgres trigger that will populate additional table column on insert

Problem (Note: I have added potential solution to the bottom of the post) I don't know what's wrong with the approach that I currently have, but when I try to run it dbeaver just instantly quits. I ...
  • 237
-2 votes
1 answer
36 views

TRIGGER THAT UPDATES A COLUMN FROM INSERTED ROW (ORACLE)

Basically I need a trigger that updates a column of the inserted row, where it takes a value from another table by comparing the foreign key. Something like this: Thanks in advance.
0 votes
1 answer
31 views

Is it possible to write a generic trigger for all tables in Postgres?

I want to listen to every transaction that is recorded in the database And I noticed that there is a concept in Postgres under the title LISTEN and NOTIFY (Of course, I am not sure that this is the ...
0 votes
1 answer
20 views

How I can make a single trigger for both create and update at sqlite3?

I made the following table: CREATE table IF NOT EXISTS redirect ( id INTEGER PRIMARY KEY AUTOINCREMENT, url_from TEXT not null, url_to TEXT not null, ...
0 votes
2 answers
38 views

What I make wrong and Cannot inset a value at sqlite3 using custom trigger?

I am making the follwing table at SQLITE3: CREATE table IF NOT EXISTS redirect ( id INTEGER PRIMARY KEY AUTOINCREMENT, url_from TEXT not null, url_to TEXT not null, ...
0 votes
0 answers
18 views

trigger for consistency check - only one foreign key is allowed

I have a contacts table that contains a column named id. This id is the primary key of contacts. I also have two tables called persons and organizations that have a primary key called id, but they are ...
  • 11
1 vote
1 answer
23 views

update current row doesn't trigger attached function

To update a column in PostgreSQL using the function which is invoked by a trigger on each row add/update- Steps I followed: First, let's add the field "line_updated_dt_tm" to the table of ...
0 votes
1 answer
40 views

Recursive trigger into function

I have problems with recursive trigger: the table that fires the trigger and give a specific error for that; the trigger inserts a record, that fires the trigger, that fires on the table, that fires ...
3 votes
1 answer
118 views

check if value exist in another table before insert SQL

I have table: " daysoff " and table: " rdv " the rdv table looks like this : temps_rdv etat_rdv ID_RDV 12-10-2022 reservee 2 the daysoff table looks like this : ID DATE_OFF ...
  • 41
0 votes
0 answers
43 views

Update Trigger my SQL for other table value

I am new to SQL. I have a table Order_Detail with a column Quantity_Ordered. I have a table stock with columns Stock_Location. Using Stock location 20 Below code works when I increment stock out ...
6 votes
2 answers
249 views

Make two columns unique both ways

I'm creating a database which stores football matches. Each round, any given football club may only play once (home or away). For example, after the insert of INSERT INTO Match (Round, Home, Away) ...
  • 63
0 votes
0 answers
22 views

MySql Trigger Deadlock

I'm having issues with a trigger sometimes causing a deadlock. Only happens when the db is getting hit pretty hard but happens enough to be annoying. May it's just bad practice to use this trigger the ...
  • 161
0 votes
1 answer
41 views

Running a sequential trigger

I have my own triggers in my database. But my customer wants customizations. I need to give a trigger order according to the priorities he wants. I'm aware that I can do all of them with 1 trigger, ...
  • 91
0 votes
1 answer
35 views

If checking while deleting the record meets the requirements, I do want to delete it

I have a table. I have a field in my table that says "islocked" record is locked. This number of fields can be more than one. isApproved, isAccepted etc. There are various triggers (after ...
  • 91
0 votes
1 answer
27 views

getting the name of the function which caused trigger to activate in postgresql

Is there a way to get name of the function/procedure, which contains query, which caused a TRIGGER to activate? For example I have function update_user_whatever and it updates user data and I want to ...
0 votes
1 answer
208 views

After Insert Trigger not working in Sql Server

I have created the 2 After Insert Trigger on same Table. 1st Trigger executed successfully. But 2nd Trigger is not executing and giving the exception from c# code 'Database operation expected to ...
0 votes
0 answers
158 views

Foreign key cascade & triggers

I have a question on how to elaborate a schema in PostgreSQL which involves: A trigger function used to keep a certain consistency calculation in a table; A foreign key whose value in the original ...
  • 101
1 vote
0 answers
46 views

How do I test the estimated query plan of a portion of a trigger that uses inserted/deleted?

I'm performance tuning a SQL Server trigger that has several statements using the "inserted" and "deleted" tables. How can I break out those individual queries from the trigger ...
  • 301
0 votes
0 answers
37 views

How to prevent user to delete more than 2 rows at the same time

QGIS 3.22 & PostgreSQL 14. Many colleagues insert features in a Postgres/PostGIS database using QGIS and import/export to PostgreSQL database plugin. Among many parameters, there is add to or ...
  • 187
0 votes
1 answer
57 views

how to run trigger on table on postgresql except one column

I'm using postgresql 13/14. lets say I have a table with column A,B,C,D,E I have created a trigger for the table that would run every insert/update/delete. (but in this problem, I only need it for ...
  • 1
0 votes
0 answers
32 views

How should the procedure structure using Transaction be?

I want to make both error checks and insert and update in a single procedure. There are triggers in the tables that I insert and update in the procedure, and other procedures work in these triggers. ...
  • 91
0 votes
2 answers
36 views

Determine new/changed data(rows) on a table during a table clone operation

I'm trying to alter some heavy data tables tables, this operation may cause some impact on our production service, so to minimize outages the plan is: create a copy table that inherits table ...
0 votes
0 answers
41 views

MSSQL Server Database Trigger Permissions - issue after migrating from 2008 to 2017 Server

I have some triggers set up on a database (Dynamics NAV) across various tables for Insert, Update, Delete. The triggers write to a separate database to log the DB, Table, RowId and date/time. The ...
1 vote
2 answers
143 views

Disable all triggers in a database

How do I disable all triggers in a database from another database which are currently enabled .And then enable it back only the ones I disabled .
0 votes
0 answers
98 views

MySQL IF NULL of a variable is not working as expected in a trigger

We have the following trigger to ensure that a version field is updated in a related table, normally the trigger contains only the UPDATE statement, the INSERTs are added for debugging and to explain ...
1 vote
0 answers
57 views

How to keep up-to-date sums in the tree?

I have a tree table. I need to ensure that when inserting or updating a record, the sumvalue field of all parents is updated - this is the sum of all child elements. I wrote a trigger (even a query is ...
  • 119
0 votes
2 answers
193 views

Triggers for multiple rows at once

So I have these three tables. The orders table is list of orders existed, while the order details consist of items that is in an order (because 1 order can have more than one catalog). I'm using mysql ...
-2 votes
1 answer
144 views

PSQL avoid insert failing because a trigger failed

I want to put a trigger on a table, the code itself could work sometimes, while sometimes it might fail unexpectedly. So I need the data to get inserted on the table regardless if one of its triggers (...
0 votes
0 answers
27 views

Can't edit a value in a table with triggers

So I'm trying to make a simple scheme of order-> payment-> shipping. Here is the structure of the main tables: cust_order (order_id, customer_id, catalog_id, items_ordered, order_date, ...
  • 1
0 votes
1 answer
111 views

How to access OLD/NEW vaules in a trigger dynamically?

I have a code in the DB trigger like _association_id bigint; if TG_OP = 'INSERT' then select concat('NEW.',association_field_id) INTO _association_id from association_audit_configs where ...
1 vote
1 answer
92 views

Oracle PL/SQL: Trigger Syntax

I'm kinda new(like 1 year of experience) in PL/SQL and i have to make a kind of tricky trigger, and i want to check if my syntax is correct, mainly the selects, inserts and variable value store i make ...
0 votes
1 answer
74 views

Sync different table structure in MySQL Database

I have two table in different database host host. activity_table in Host A and presence_table in Host B. presence_table used by binary app where i don't have access to the source code but i have full ...
0 votes
0 answers
137 views

Generating Triggers from the Materialized View Query

We are currently using materialized views to reduce the response time of our website. Some views take a few seconds to refresh. This results in long loading times for the user. Generally speaking, it ...
  • 1
0 votes
1 answer
34 views

Simplify duplicate INSERT commands placing NULL for values that are not found

I have many trigger functions written in PL/pgSQL that follow the below logic: SELECT my_name INTO l_my_name FROM data WHERE data.my_number = NEW.test_num; IF NOT FOUND THEN INSERT INTO test_event ...
0 votes
1 answer
21 views

How to change all similar rows in PSQL if one value is changed?

I have a table like this: name email domain rank contact 1 email website.com null contact 2 email website.com null contact 3 email website2.com null The first two contacts are from the same ...
  • 101
1 vote
1 answer
413 views

Mobile finance service transaction table design

I try to create an MFS(mobile financial service/p2p money transfer, bank with limited work) for learning. The transaction looks difficult. The user can do 3 things with his account. Deposit money to ...
0 votes
0 answers
26 views

Apex page DML Mail Alert Trigger

I have a Trigger and Function which sends mail with "updated" column(s), when i am updating in my back-end (sql developer), i receive a mail with that particular column. But when i do UPDATE ...
0 votes
0 answers
71 views

Oracle 19c - Multiple If Conditions

create or replace TRIGGER trigger_name ... ... BEGIN IF SYS_CONTEXT (...) IN (...) THEN IF SYS_CONTEXT (...) NOT IN (...) THEN IF SYS_CONTEXT (...) NOT IN (...) THEN INSERT INTO owner.table_name (...) ...
1 vote
0 answers
28 views

Clever upserting and deleting

Let's say I have a table that has roughly the following schema (for brevity, I only kept the relevant fields): CREATE TABLE IF NOT EXISTS some_table ( id SERIAL NOT ...
0 votes
1 answer
474 views

MySQL Trigger to prevent update in one table if particular data is available in another table

I need a MySQL trigger that can prevent update of a particular column data, if some specific data is available in another table. I have two tables product and sales. Product Sale I need to prevent ...
0 votes
0 answers
120 views

mysql Trigger to prevent entry being written to a table AFTER writing to another table

Is there a way to use SIGNAL in a trigger to prevent a query being written to a table, but only AFTER you have logged the details in another table? for example: CREATE TABLE `test2` ( `txtvals` ...
  • 1,994
1 vote
1 answer
149 views

PostgreSQL - Recursive Foreign Key - Depth of 2 Check

In my system, I have employees that belong to a single department. However, some departments are large enough to have a sub-departments, so I have created a single department table like so: CREATE ...
1 vote
1 answer
286 views

Triggers: List of ALL the OLD record's column names

I have a trigger that creates a jsonb field of all the OLD columns of the relevant table in an audit table. However, I need to exclude any geometry columns, in my case, the column name being the_geom. ...
  • 23
1 vote
0 answers
31 views

Trigger on partitioned table and performance [duplicate]

I'm not sure if what I've found is a bug, but it certainly looks like it. I wasn't able to find much information about I'm not sure if what I've found is a bug, but it certainly looks like it. I wasn'...
7 votes
2 answers
837 views

Trigger on partitioned table and performance

I'm not sure if what I've found is a bug, but it certainly looks like it. I wasn't able to find much information about it, so I've decided I'll put it here. So, in a nutshell, I'm facing terrible ...

1
2 3 4 5
29