Skip to main content

Questions tagged [trigger]

Procedural code automatically executed in response to a database event.

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

postgresql (15) Python event_trigger

What's the syntax used to write a Postgresql event trigger in Python? This CREATE OR REPLACE FUNCTION log_schema_changes() RETURNS EVENT_TRIGGER LANGUAGE plpython3u AS $$ import plpy plyp.warning(...
gerardw's user avatar
  • 103
0 votes
1 answer
22 views

PostgreSQL16 concurrency upsert rows with subquery in trugger funtion got maybe bad read

I have two tables: create table name ( id bigserial primary key, name text not null ); create table name_score ( id bigserial ...
zxx's user avatar
  • 3
0 votes
1 answer
49 views

DB2 TRIGGER "FOR EACH STATEMENT", with "AFTER UPDATE OR INSERT" gives SQL code -104, SQL state 42601

IBM DB2 on Cloud, version 11.5.9.0 Trying to create a trigger on a table (object names are made up, for simplification): CREATE OR REPLACE TRIGGER MY_TRIGGER AFTER UPDATE OR INSERT ON MY_TABLE ...
C C's user avatar
  • 125
-1 votes
1 answer
17 views

Problem with trigger in pqsql

BEGIN SELECT n_volume_ritardo FROM Lettore WHERE cf = d.cf; IF n_volume_ritardo >= 5 THEN RETURN NULL; END IF; RETURN NEW; END; The system says ERROR: trigger ...
Unknown's user avatar
1 vote
2 answers
72 views

Is the execution of after insert trigger finished when the next entry is inserted?

I have an SQL Server database with a table with a trigger which is executed after insert. If I insert two entries directly one after another, it is important that the second entry is inserted only ...
SomeBody's user avatar
  • 113
-2 votes
1 answer
60 views

Trigger based on if data is selected from view [closed]

Can a Trigger be written for a View, that if data is selected from that view to trigger off another transaction?
sroth79's user avatar
0 votes
1 answer
29 views

Create composed key (YEAR+COUNT) field with trigger after insert in SQLITE

I have a table Product with two columns: name of product and price CREATE TABLE IF NOT EXISTS YearCounters ( year INTEGER PRIMARY KEY, counter INTEGER DEFAULT 0 ); -- Product Table CREATE ...
kiaderouiche's user avatar
1 vote
1 answer
50 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
54 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
35 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
35 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
64 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
25 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
27 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
20 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
  • 339
0 votes
1 answer
46 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
93 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
41 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
51 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
82 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
47 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
109 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
116 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
227 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
11 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
559 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.2k
1 vote
1 answer
81 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
68 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
33 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
173 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
20 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
298 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
89 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
86 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
50 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
41 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
137 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
62 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
36 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
61 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
142 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
331 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
  • 567
0 votes
0 answers
88 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
197 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
67 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
91 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
90 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
25 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

1
2 3 4 5
31