Questions tagged [trigger]
Procedural code automatically executed in response to a database event.
1,484
questions
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
...
- 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 ...
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 $$ ...
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 ...
- 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 ...
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 ...
-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 ...
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 ...
- 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 ...
- 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 ...
- 885
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 ...
- 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, ...
- 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,
...
- 169
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 ...
- 115
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 ...
- 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 ...
- 115
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....
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 ...
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 ...
- 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 ...
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 ...
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 ...
- 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)
...
- 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 ...
- 3
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 ...
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 ...
- 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 ...
- 3
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.
...
- 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, ...
- 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,
...
- 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 ...
- 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 --> ...
- 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 ...
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 ...
- 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.
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. ...
- 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....
- 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 ...
- 37
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 ...
-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), ...
- 3
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 ...
- 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`, `...
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 ...
- 121
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 ...
- 427
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 ...
- 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. ...
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 ...
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 ...
- 1
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 ...
- 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). ...
- 3