Questions tagged [trigger]

Procedural code automatically executed in response to a database event.

Filter by
Sorted by
Tagged with
0 votes
1 answer
45 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
69 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
5 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
426 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
  • 69.9k
1 vote
1 answer
30 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
60 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
18 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
76 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
17 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
224 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
60 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
80 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
  • 350
1 vote
0 answers
41 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
  • 335
1 vote
1 answer
31 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
  • 296
2 votes
1 answer
54 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
45 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
31 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
54 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
78 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
254 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
37 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
20 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
149 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
50 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
53 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
52 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
10 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
  • 465
0 votes
1 answer
103 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
38 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
36 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
59 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
40 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
367 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
65 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
0 votes
0 answers
17 views

INSERT INTO SELECT FROM information_schema.processlist.info inside trigger bringing back INSERT query not original query

I have a trigger on a table that needs to get the Querytext that caused the trigger, Using a similar syntax as the following BEFORE INSERT ... INSERT INTO ... SELECT information_schema.processlist....
Anthony Sibley's user avatar
1 vote
1 answer
18 views

How to ignore all row modifications on a FOR EACH STATEMENT trigger without cancelling the query on PostgreSQL?

How to ignore all kind of table modifications (INSERT, UPDATE, DELETE) in a FOR EACH STATEMENT TRIGGER without throwing/raising an EXCEPTION cancelling the whole transaction? I know it can be achieved ...
Tiago Stapenhorst's user avatar
1 vote
2 answers
111 views

SQL Trigger Calling out Domain\Group

I am creating a trigger in SQL Server 2019 to secure the number of connections users can have into my SQL server. I am having an issue getting my SYSDBAs access using the domain group. I would rather ...
JukEboX's user avatar
  • 121
0 votes
1 answer
1k views

How to disable FOREIGN KEY referential integrity trigger in PostgreSQL without disabling other triggers in table?

FOREIGN KEYs referential integrity in PostgreSQL can be disabled by executing: ALTER TABLE schema.my_table DISABLE TRIGGER ALL; But unfortunately the command will also disable all other triggers ...
Tiago Stapenhorst's user avatar
1 vote
1 answer
34 views

SHOW TRIGGERS privilege without also being able to DROP TRIGGER?

Is it possible to give a user privilege to view Triggers, i.e. execute the SHOW TRIGGERS command, without also giving them permission to DROP TRIGGER? Background is, I want to give certain users the ...
BrokenPhysics's user avatar
1 vote
1 answer
284 views

How to update multiple records after insert/update in SQL Trigger?

I have below query, which updates timestamp for 10 records. UPDATE [dbo].[file] SET timestamp = GETDATE() WHERE id <= 10 I created a trigger on that table Insert/Update. SET ANSI_NULLS ON GO SET ...
Mike's user avatar
  • 13
0 votes
1 answer
35 views

Error when creating trigger to insert values

I am new to databases and I know this is a simple question but I cannot find an answer anywhere, so I appreciate the help. I have 2 tables, each with the same structure. Table1 CaseNum Number(9,0) ...
bk_32's user avatar
  • 103
0 votes
1 answer
50 views

Storing segmented time series - performance issue caused by triangle relationship / maximum search

I am trying hard to create a database design from scratch, that allows to store multiple different time series, where each one may be segmented into individual epochs. Inserting new data points for a ...
rellachs's user avatar
0 votes
1 answer
302 views

Finding when columns are added- SQL Server

In SQL Server is there any way to get information about when columns are created and which columns are new? Also, I need to find any change in columns, for example which columns' data type changed. I ...
Farid Zahidov's user avatar
0 votes
1 answer
36 views

Accessing NEW.* keys dynamically in trigger in PostgreSQL

How you can access the NEW.* RECORD keys dynamically? For example here I'm trying to trim spaces: CREATE OR REPLACE FUNCTION trim_spaces() RETURNS TRIGGER AS $$ DECLARE _a HSTORE = hstore(NEW); _k ...
raspi's user avatar
  • 131
0 votes
1 answer
63 views

Restricting Direct Changes in Database Data

Is it possible for Oracle database administrator to make direct changes in data contained in a row, for example, changing an amount of 50 to 500 either by typing into the database cell or by executing ...
itsec80's user avatar
1 vote
1 answer
65 views

insert trigger to auto-set key fields for composite primary key

Basically the next step for Error "UNIQUE constraint failed" with composite primary key: I created a trigger after insert to set SEQ to the highest SEQ plus one if it's less than one. ...
U. Windl's user avatar
  • 125
0 votes
1 answer
606 views

Error "UNIQUE constraint failed" with composite primary key

With littele SQL(ite) practice, I tried to create a table with a composite primary key: CREATE TABLE NAMES ( TYPE INTEGER NOT NULL, SEQ INTEGER NOT NULL, NAME VARCHAR(20), PRIMARY KEY (TYPE, ...
U. Windl's user avatar
  • 125
0 votes
0 answers
87 views

postgresql: Trigger function not firing

I have two tables as follows: CREATE TABLE public.main_tab ( smid serial4 NOT NULL, smgeometry public.geometry(point, 32639) NULL, specific_id int4 NULL, mother_tab_smid int4 NULL, ...
Sajjad's user avatar
  • 13
1 vote
0 answers
13 views

Why am I getting a privilege error when creating a table from a trigger in Oracle? [duplicate]

I'm trying to create a trigger that creates a backup of a table before it is truncated. However, I'm getting a permission error when attempting to create a new table. Why? create or replace trigger ...
Matt's user avatar
  • 111
0 votes
1 answer
46 views

Simultaneous INSERTs which lock the target table inside a before-insert trigger result in a deadlock

I'm working on a web application which uses a PostgreSQL database for storage. There are many logical entities in this application which are related to each other like hotel --> hotel_floor --> ...
Alex F's user avatar
  • 101

1
2 3 4 5
30