Questions tagged [trigger]
Procedural code automatically executed in response to a database event.
1,524 questions
1
vote
0
answers
60
views
what server events I can use to "do something" on before restore of a database?
on this page: DDL Events you can see a complete list of the ddl events.
these events are server or database things that happen - like - create a database, alter a sequence, update statistics, create a ...
- 16.6k
2
votes
2
answers
72
views
Why does a create trigger (that already exists) block all processes in SQL Server 2016 SP3?
I have a situation where the following DDL Statement is rising to the top of my list of processes which I have ordered by the number of processes that each process is blocking (directly or indirectly)....
- 151
1
vote
3
answers
80
views
Make A Table Ignore Trivial Updates
The scenario:
I own an ETL process. That process involves ingesting data from disparate sources and making my database sync with the latest.
Let's say that I have a table called [catalog]. It contains ...
- 49
0
votes
1
answer
57
views
how to write trigger to simulate before update to set foreign key field to null upon violation
I have two databases that synchronize with each other. One database is a full set of data, the other is a limited dataset. This means that the limited database sometimes doesn't have the data the ...
- 11
3
votes
1
answer
122
views
Is it safe to alter a trigger on a live database?
I have a Microsoft SQL Server with a table. There is a trigger that fires if a row is inserted into the table. The system is live, as in that rows are constantly inserted into the table and the ...
0
votes
1
answer
69
views
Postgres trigger check if insert affected column
I have a table where some columns have to be "protected", meaning they should not be written to by most users. I want to enforce this with a trigger function (as there is additional logic to ...
- 1
0
votes
0
answers
61
views
server side trigger for after restoring database
there is a brilliant script to create a server side trigger on an event of a create database.
here is the script:
IF EXISTS (SELECT NULL FROM sys.server_triggers WHERE name = 'ddl_trig_database')
...
- 16.6k
0
votes
1
answer
75
views
Locking write to tables while Postgres procedure is executing
I have created a procedure in Postgres that will create a table with the contents of another three tables (SELECT with JOINS). Then the resulting table will be altered adding two columns that didnt ...
- 103
1
vote
1
answer
126
views
server side triggers - how to prevent a linked server creation?
This Trigger actions on database creation - shows an example of a server side trigger that is fired when someone creates a new database.
This other question - Trigger to change Database collation on ...
- 16.6k
0
votes
0
answers
52
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(...
- 103
0
votes
1
answer
29
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 ...
- 3
0
votes
1
answer
248
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
...
- 125
-1
votes
1
answer
30
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 ...
- 9
1
vote
2
answers
84
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 ...
- 113
-2
votes
1
answer
62
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?
- 1
0
votes
1
answer
32
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 ...
1
vote
1
answer
77
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 ...
- 11
1
vote
1
answer
97
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-...
- 151
0
votes
1
answer
47
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 ...
- 3
0
votes
1
answer
41
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 ...
- 1
0
votes
1
answer
358
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 <> ...
1
vote
1
answer
55
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? ...
0
votes
1
answer
37
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 ...
- 37
0
votes
0
answers
26
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?
- 359
0
votes
1
answer
60
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 ...
0
votes
3
answers
260
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 ...
- 113
1
vote
1
answer
47
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, ...
0
votes
1
answer
119
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 ...
0
votes
1
answer
214
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,
`...
0
votes
1
answer
41
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
...
- 384
0
votes
1
answer
76
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 ...
0
votes
0
answers
185
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?
1
vote
2
answers
127
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 ...
1
vote
1
answer
359
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 ...
- 1,550
0
votes
0
answers
20
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 ...
8
votes
2
answers
724
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
...
- 70.5k
1
vote
1
answer
127
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(...
- 111
0
votes
0
answers
69
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 ...
1
vote
1
answer
43
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 ...
- 121
0
votes
1
answer
238
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 ...
- 1
0
votes
1
answer
28
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 ...
- 1
3
votes
1
answer
406
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 ...
0
votes
1
answer
119
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 (...
- 101
0
votes
1
answer
93
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....
- 360
1
vote
0
answers
71
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 ...
- 421
1
vote
1
answer
75
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&...
- 306
2
votes
1
answer
175
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 ...
- 428
0
votes
1
answer
70
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 ...
- 1
0
votes
1
answer
47
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 ...
0
votes
1
answer
83
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 ...
- 445