Questions tagged [trigger]
Procedural code automatically executed in response to a database event.
1,436
questions
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 ...
- 137
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 ...
- 301
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 ...
- 13
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 ...
- 5,034
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 ...
- 101
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 ...
- 109
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 ...
- 19
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 .
- 57
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 ...
- 101
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
...
- 3
-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 ...
- 11
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 (...) ...
- 29
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 ...
- 197
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 ...
- 13
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 ...
- 493
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'...
- 364
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 ...
- 364