Skip to main content
Share Your Experience: Take the 2024 Developer Survey

Questions tagged [trigger]

Procedural code automatically executed in response to a database event.

Filter by
Sorted by
Tagged with
1 vote
1 answer
31 views

MySQL Trigger updating second table based on History and computed values

The Goal: Update an secondary table called Lottery_Cycle when an INSERT occours on table Lottery_History with some computed values and checking previous data. Let's say a lottery with 25 numbers of ...
Wisdom's user avatar
  • 11
1 vote
1 answer
39 views

Weird behaviour with batch and trigger(MariaDB)

I found a weird issue involving mariadb. batch update from hibernate. the original question was added here : https://stackoverflow.com/questions/78204056/hibernate-sqlintegrityconstraintviolation-...
Rudy's user avatar
  • 151
0 votes
1 answer
28 views

Postgres: automatic action (increment count of foreign key) after update

How to automate an update in a table, after a change in another table ? Say (please see below) I have a table countries, and a table cities. How to make that, when adding a city in table cities, the ...
ymudyruc's user avatar
0 votes
1 answer
29 views

Lookup tables: check by type id or type name?

TLDR: Should I check an entity's type by lookup_table.id or lookup_table.type_name? I've learned that lookup tables are almost always preferrable to Enums due to their flexibility. However, I have ...
Afelium's user avatar
0 votes
1 answer
35 views

In a trigger check if one of 24 columns are updated (postgres)

I have a table with 50 columns. I need to make a trigger to do something, i can do that, but i need to do whene 1 of 24 columns are updated. The problem is i have to do 24 times if NEW.column <> ...
RkassaTeam Breda's user avatar
1 vote
1 answer
17 views

Postgres - trigger function not working with generated columns as input

Before I post the DDL for my table, trigger function, and trigger, I'll just ask a simple question - Can a trigger function work with generated columns assigned to variables in an AFTER INSERT event? ...
Nicholas Lombardi's user avatar
0 votes
1 answer
23 views

how to auto trigger mysql

I have two tables, User table and a points table. I designed these tables to be in one-to-one relation, as points contains a foreign key to the user table. as new users get created, I use query INSERT ...
umarkaa's user avatar
  • 37
0 votes
0 answers
18 views

What is the return value of an AFTER FOR EACH ROW trigger function?

Is it necessary to return NEW or is NULL sufficient? Has it any meaning to return anything in an AFTER trigger?
ceving's user avatar
  • 317
0 votes
0 answers
30 views

Need to create a duplicate table in a separate schema automatically whenever a new table is being created

Whenever a new table is being created in Schema 'A', I need the table to be duplicated in the Schema 'B'. This should happen automatically. I have searched for trigger concept but it wont work on DDL ...
Edvin Guromin's user avatar
0 votes
3 answers
54 views

How to change insert values from 0 to NULL for a specific field in order to avoid foreign key violation?

I have a foreign key linking INSTRUMENT.VENDOR to VENDOR.NAME. Unfortunately the program where users enter information is attempting to insert into INSTRUMENT with a value of 0 for vendor. I need to ...
Tyler N's user avatar
  • 113
1 vote
1 answer
35 views

Difficulty implementing some kind of backwards compatibility in PostgreSQL

I'm facing an issue with implementing a rule or trigger in PostgreSQL to handle inserts into a table where one of the column types (a) has been changed and old queries . I've tried a few approaches, ...
Roberto Iglesias's user avatar
0 votes
1 answer
37 views

Before update trigger not triggered when change comes via logical replication

I have a test scenario of two postgresql databases connected via logical replication. DB "A" is publishing inserts and updates for table "a1" and DB "B" is subscribed and ...
Roberto Iglesias's user avatar
0 votes
1 answer
45 views

After Insert and Update Trigger on the same table

I have the following table in MySQL: CREATE TABLE `userlogtype` ( `UserLogTypeID` int NOT NULL AUTO_INCREMENT, `UserLogType` varchar(150) DEFAULT NULL, `InsertUser` varchar(100) DEFAULT NULL, `...
Steve Miller's user avatar
0 votes
1 answer
30 views

Postgres trigger to update records of several parent tables based on values of a many-to-many join table referencing those parent table records

I have a PostgreSQL 15 join table schema.m2mjointable to link records of several parent tables (schema.table_a, schema.table_b, ...) with other records of those same parent tables: id ref_table ...
s.k's user avatar
  • 372
0 votes
1 answer
36 views

Are default values for a column still required if using audit triggers?

I have a couple of audit columns in my table: modified_by modified_date I have an AFTER trigger set up to automatically update these columns whenever data is inserted or modified, with current_user ...
greenandblue's user avatar
0 votes
0 answers
64 views

How to get last executed statement on a particular table in Oracle?

Trying to identify what ghost process is inserting records into a particular table. Is there a way to get the exact query?
Dhaval Mohandas's user avatar
1 vote
2 answers
111 views

Prevent users from sending an email attachment in sql server

In SQL Server, I want to restrict all users (except a few specific users) to send an email with attachment. For example, someone import data into excel file and send it via mail using the ...
Farid Zahidov's user avatar
1 vote
1 answer
162 views

Module signing on a trigger which accesses system views. Base table is on a custom schema

Some background: Question is follow up of this question: Is there a way in SQL Server to make a table only able to insert by trigger? I'm trying to achieve what the answer to the original question ...
Stackoverflowuser's user avatar
0 votes
0 answers
9 views

Aurora3 RDS MySQL Stored function/trigger performance when using information_schema

Since moving to Aurora3 RDS (MySQL 8 based) vs Aurora 2 (MySQL 5.7 based) noticed that execution time of certain triggers and stored functions performance has degraded. Correlation between triggers ...
BigKiwiDev's user avatar
8 votes
2 answers
479 views

OUTPUT clause returning 0 for newly inserted identity value due to INSTEAD OF trigger

Consider the following minimal, complete, and verifiable example code (see dbfiddle here): CREATE TABLE [dbo].[test] ( [i] bigint NOT NULL identity(1,1) PRIMARY KEY CLUSTERED ...
Hannah Vernon's user avatar
  • 70.1k
1 vote
1 answer
42 views

Postgres insert into `select distinct` view if not exists

I have an events table which is a denormalized table containing item data. There is a one-to-many relationship between items and events. CREATE TABLE events ( id UUID PRIMARY KEY, item VARCHAR(...
weberc2's user avatar
  • 111
0 votes
0 answers
64 views

How I can sync the data upon updating and deleting from adjacency list table into closure one?

In my sqlserver database I am trying to sync my data with a closure table. So far I made a table: CREATE TABLE mydb.dbo.files ( id bigint IDENTITY(1,1) NOT NULL, name nvarchar(255) COLLATE ...
Dimitrios Desyllas's user avatar
1 vote
1 answer
27 views

SQLite - Update of a field in two steps without variable (SpatiaLite)

I work with SpatiaLite, managing databases through SQLite. Unfortunately, this system doesn't allow the use of variables, or at least, I haven't discovered how to use them yet. My objective is to ...
Giene's user avatar
  • 121
0 votes
1 answer
105 views

EVENTDATA() function is giving only null values in sql server

I'm trying to capture logins time with a trigger using EVENTDATA() function. But It's giving Null values only for all types of events. Can anyone please help me on this? This is my Trigger CREATE ...
dbajayy's user avatar
0 votes
1 answer
18 views

Syntaxis Problem when creating a trigger in Mysqlite

how u doing?, I have a problem that I don't understand, when I create a Trigger in Mysqlite, check it: c.execute("""CREATE TRIGGER ahoraSI BEFORE INSERT ON Productos FOR ...
Carlos's user avatar
  • 1
3 votes
1 answer
257 views

Creating a Trigger in MySQL that has a JOIN

PROBLEM: I'm trying to create a TRIGGER that will copy records from 2 different tables (users, addresses) in database A to a single table (users) in database B. Basically, I'm trying to consolidate ...
Tony Stephens's user avatar
0 votes
1 answer
66 views

Optimized count for large table using triggers, views, or external cache

I have a public API method that calls a Postgres (14) database and returns a paginated list of rows belonging to a user along with a total count and page index. The count is very costly to perform (...
JackMahoney's user avatar
0 votes
1 answer
81 views

DMV sys.dm_exec_trigger_stats - duplicate entries

I am running following select: SELECT tstats.object_id, tstats.database_id, tstats.sql_handle, DB_NAME(tstats.database_id) AS [db_name], OBJECT_NAME(tstats.object_id) AS trigger_name, tstats....
Sranda's user avatar
  • 360
1 vote
0 answers
48 views

Instead of delete trigger performance

We have 3rd party tool which is deleting data via below parametrize query: delete from [sap].[PRCD_ELEMENTS] where [rowid] = @P1 Table contains around 3 billions of records and is partitioned by ...
adam.g's user avatar
  • 355
1 vote
1 answer
36 views

Why does a user use owner privileges when executing a trigger?

I came across a strange situation that gave me a big headache and after solving it I would also like to understand WHY. Basically, a user had all the privileges he needed to execute a "trigger&...
Sotis's user avatar
  • 306
2 votes
1 answer
102 views

Return state of a row when executing a function in PostgreSQL

For years I have been creating functions that are executed by triggers, and in the function I always returned OLD (for DELETE triggers) or NEW (for INSERT or UPDATE triggers). Is it mandatory to ...
Tom's user avatar
  • 428
0 votes
1 answer
58 views

Split lines against polygons in a PostGIS trigger

I have a table of lines, named segment, stored in the reference schema, and a table of polygones named communes in a donnees_externes schema. I try to create a trigger that split the features of ...
idrizza's user avatar
0 votes
1 answer
32 views

Validate "insert", "update", "delete" in the body of a trigger

I need to create a query where I can remove triggers that have "insert", "update" or "delete" in their body from the result. I'm using the following query for this SELECT ...
Igor Oliveira's user avatar
0 votes
1 answer
56 views

History Logging Trigger Causing Duplication

I'm fairly certain I'm probably just overlooking something simple but here's the situation: I'm trying to create a "reusable" historical logging trigger for a production database. The intent ...
G_Hosa_Phat's user avatar
2 votes
1 answer
116 views

How to locate a procedure in the body of a trigger

I'd like to perform a validation by capturing all my triggers that call procedures.To do the search, I'm looking for the words "Call" and "Execute" in the trigger body -- I'm using ...
Igor Oliveira's user avatar
4 votes
1 answer
277 views

Catch Block Inside of a Trigger Does Not Get Hit When a SP With Dynamic SQL Errors Out - Causing the Larger Transaction to Fail

I am not a fan of triggers and dynamic sql, however, what I am working with requires both. CREATE TRIGGER [dbo].[GenerateDynamicFormItemViews] ON [dbo].[tblFormItems] AFTER INSERT, UPDATE AS BEGIN ...
Ross Bush's user avatar
  • 505
0 votes
0 answers
62 views

Mariadb Triggers after multiple insert, update one table one one time

CREATE TRIGGER after_composers_insert AFTER INSERT ON composers FOR EACH ROW UPDATE listings SET composer_name = NEW.composer_name WHERE composer_id = NEW.composer_id; CREATE TRIGGER ...
Alain Lagune's user avatar
0 votes
1 answer
22 views

MySQL trigger doesn't response, WHY?

I am new to SQL and I don't know where to get help. I faced a problem while using the trigger, but it doesn't response. This is my code here: DELIMITER $$ ...
Ryan Huang's user avatar
2 votes
2 answers
169 views

SQL Server: Trigger with UPDATE statement causes deadlocks

I have added a trigger to perform a change tracking mechanism on a table that has a relatively high load. During testing, we encountered no issues but now as it is in production there are deadlocks ...
JannikB's user avatar
  • 21
0 votes
1 answer
57 views

How to get system columns in on statement trigger?

I have an extension in my PostgreSQL 11 installation which adds malabel system column to each row. I need to get this column from OLD table but I can't get any of system columns in statement level ...
Дмитрий Питаков's user avatar
1 vote
1 answer
69 views

Postgresql near real-time

Before asking the question I will update you on the situation I face. I have physical tables in postgresql that are continuously updated by various external flows (.NET, Java, etc...), at the moment ...
Luis Di Matteo's user avatar
0 votes
0 answers
65 views

Event driven architecture in Postgres?

I'm trying to create an automated trigger event (Postgres is my main DB) on something like this: Orders table ---------------- Order_Expiry_Date: Date Customer_email: String When as soon as ...
Neil's user avatar
  • 101
0 votes
0 answers
19 views

How can I automate trigger deployment to Mongo Atlas?

I have a couple of triggers on a Mongo Atlas cluster that I want to deploy to a number of databases on a different cluster. Is there any way to do this automatically, through a script or something ...
SQB's user avatar
  • 480
0 votes
1 answer
222 views

PostgreSQL: does the order of rows in a bulk insert control the order of `FOR EACH ROW` triggers?

I have a trigger like BEFORE INSERT ON table_a FOR EACH ROW EXECUTE FUNCTION upsert_into_table_b(). If I execute a bulk insert like INSERT INTO table_a ([columns]) VALUES ([row 1 values]), ([row 2 ...
Nathan Long's user avatar
0 votes
1 answer
45 views

SQL Trigger Connection Case With NT AUTHORITY

I am trying to limit the connections to my SQL using a CASE to monitor the connections as laid out here. In the CASE I am trying to add the NT AUTHORITY\NETWORK SERVICE to the case. Yet when it ...
JukEboX's user avatar
  • 121
1 vote
0 answers
49 views

ETL process between two Postgres Databases

I have two postgres databases, let's called them layer 1 and layer 2, that both are hosted on a separate servers. In between, there is a third server, that first, loads tables from layer 1, second, ...
Ai4l2s's user avatar
  • 111
0 votes
1 answer
60 views

What'd be the downside of a uniqueness check within a trigger for a partitioned table?

I had the following table: CREATE TABLE transactions ( id NUMERIC(20, 0) NOT NULL DEFAULT NEXTVAL('transactions_sequence') PARIMARY KEY, transaction_id VARCHAR(255) DEFAULT NULL NULL, ...
Hasan Can Saral's user avatar
0 votes
1 answer
71 views

Mariadb using IF in trigger to fill null value on insert

Trying to use a trigger to a) fill in a new column top_category with values left of a colon, found in category, then b) If there is no colon, then put entire insert value into top_category. I can't ...
JohnZastrow's user avatar
1 vote
3 answers
445 views

Maintain an ordinal column

I need to maintain a column that stores a sort order partitioned by a parent record foreign key. The column should be the contiguous counting numbers and should automatically renumber the records when ...
b_c's user avatar
  • 13
1 vote
2 answers
83 views

Mariadb update column during load data of CSV

I am loading .csv files into Mariadb 10.6 using LOAD DATA LOCAL INFILE. There is a DATE column called posted_on in the inbound data, and I need store the month name in another field monther, ideally ...
JohnZastrow's user avatar

1
2 3 4 5
31