Questions tagged [trigger]

Procedural code automatically executed in response to a database event.

Filter by
Sorted by
Tagged with
3 votes
1 answer
216 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
  • 495
0 votes
0 answers
12 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
16 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
123 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
42 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
0 votes
1 answer
40 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
-1 votes
0 answers
22 views

have a table with an 'after delete' trigger in one database. I want to insert a record into a table in another database when a record is deleted

Your scenario is as follows: you have a table with an "after delete" trigger, and you want to insert a record into another database's table if someone deletes a record. The problem is that ...
Mohammad Safyar's user avatar
0 votes
0 answers
27 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
  • 1
0 votes
0 answers
4 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
33 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
35 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
0 votes
0 answers
16 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
  • 101
0 votes
1 answer
58 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
20 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
323 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
47 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
11 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
15 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
103 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
300 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
25 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
101 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
20 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
45 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
165 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
30 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
47 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
32 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
317 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
41 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
34 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
0 votes
2 answers
140 views

(PostgreSQL trigger function) How to use TG_ARGV as column names of NEW and OLD?

I am trying to create a trigger function in PostgreSQL that will take TG_ARGV[0] and TG_ARGV[1] and use those as column names of NEW and OLD, so that I can pass them in as arguments like this: CREATE ...
Starscream512's user avatar
0 votes
0 answers
28 views

Finde the difference in UPDATE in PostgreSQL

I would like to determine the difference in an UPDATE that was carried out on a table DE_projects. So between old and new values. The aim is to monitor changes in the permissions column in the ...
MTwain's user avatar
  • 1
0 votes
1 answer
774 views

(PostgreSQL CREATE TRIGGER) Is there an equivalent to IF NOT EXISTS?

On PostgreSQL 9.6, how can I do the equivalent of CREATE TRIGGER IF NOT EXISTS? I noticed that in PostgreSQL 14 you can do CREATE OR REPLACE TRIGGER but I'm using 9.6 which doesn't have that.
Starscream512's user avatar
0 votes
2 answers
78 views

Variable Value Set in TRY CATCH is Discarded When Trigger is Coded a Certain Way

I know this goes against all things logical, however, I am attempting to push this through anyway :( There is a scenario in which records are written to a table with the trigger below attached. ...
Ross Bush's user avatar
  • 495
0 votes
0 answers
43 views

Trigger with EXEC and Only Debug Statements After First Exception Saved [duplicate]

I apologize for the verbosity of this post. To catch DDL changes after a snapshot or full load (using aws/dms here) has been performed, there is sourceDB.tblAWSObjects which replicates to targetDB....
Ross Bush's user avatar
  • 495
0 votes
2 answers
101 views

Oracle: what are the entries that can occur in ora_dict_obj_type

I'm looking to do some database level system triggers as I'm building a proxy access system into my database to stop my team sharing passwords. I have also granted roles, but some development needs ...
SeanGaff's user avatar
0 votes
0 answers
148 views

ORA-00600: internal error code, arguments: [psdghi:baseTblColName]

I am trying to create a 'before' trigger for each row on a view. The trigger will be based on an event that occurs on a specific column, which is an invisible column (hidden_column = YES). I have ...
Ghosh Prabir's user avatar
-1 votes
1 answer
48 views

How to Link newly inserted row with Foreign Key?

In my database I have 2 tables linked together with a foreign key relationship. Parent Table = controls.slot_table Child Table = controls.IO_table When a new row is inserted into the parent table (A), ...
Hibbert's user avatar
0 votes
1 answer
109 views

Json filling in trigger

Good afternoon There is a label in which records are added. Each entry has a json column. Json has several 6 fields. When adding a new record, the data from it may refer to the old record. It is ...
Ivan's user avatar
  • 1
0 votes
1 answer
78 views

Create a log using trigger

I created a table name as log to store some log after the table 'lançamento' is changed: CREATE TRIGGER CHECK_UPDATE AFTER INSERT ON lançamento FOR EACH ROW BEGIN INSERT INTO log (`Data`, `...
DAVI AMÉRICO's user avatar
0 votes
0 answers
50 views

Is this a reasonable setup for a Firebase backend for a group chat app?

This is my idea so far: Firestore will store group info that will never/rarely change, such as groupID, groupName, groupAdmin: Lock down Firestore such that a user can only read from a collection ...
whatwhatwhat's user avatar
2 votes
3 answers
317 views

Trace what is calling a trigger? Extended Events Question

Good morning all - I have a monitoring software that is showing one of my audit triggers is firing 1+ million times an hour, when I look at the audit tables there are about 3000 records entered - The ...
user1910240's user avatar
12 votes
1 answer
517 views

Is there a reliable way to check if a trigger being fired was the result of a DML action from another *specific* trigger?

Is there any way to tell from within a trigger, when it gets fired, that it is being fired as the result of a DML action that occurred within another specific trigger? Any chance any information about ...
J.D.'s user avatar
  • 35k
0 votes
0 answers
30 views

How do I automatically insert a row into parent table when a row is created in a child table?

I am working on a MySQL database that has to treat organizations and people as interchangeable in some instances and not others. Because of this, I created a table called LegalEntity as a superclass. ...
SlowlySwift's user avatar
0 votes
0 answers
26 views

How to run a PL/pgSQL procedure when an user connects to PostgreSQL DB?

I am creating a software for parking slots reservations in parking buildings. One requirement is to whenever an user logs into the app, his Public IP address should be stored along with his user name ...
Smooth Researcher's user avatar
0 votes
0 answers
123 views

How to create sequence for two columns on pgsql

I have a table CREATE TABLE public.mytable ( sequence integer NOT NULL, caller_address TEXT, contract_address text, contract_sequence integer, value integer, CONSTRAINT ...
habbas33's user avatar
1 vote
1 answer
337 views

Deadlock in AFTER INSERT trigger

I have an AFTER INSERT trigger for table A that updates multiple rows in table B (incrementing a column in table B). Normally, the web server would insert multiple rows into table A using a single ...
Alvin's user avatar
  • 35
0 votes
3 answers
595 views

Audit trigger to only track when specific columns change, and only log the old and new values for the changed columns

I have a table with 75 columns and need to track about 15 of those columns to log out the old and new values anytime the data in any of those 15 columns change (updates only, no inserts or deletes). ...
Lando_'s user avatar
  • 3

1
2 3 4 5
30