Questions tagged [trigger]
Procedural code automatically executed in response to a database event.
1,395
questions
1
vote
0
answers
26
views
Clever upserting and deleting
Let's say I have a table that has roughly the following schema (for brevity, I only kept the relevant fields):
CREATE TABLE IF NOT EXISTS some_table
(
id SERIAL NOT ...
- 111
0
votes
1
answer
21
views
MySQL Trigger to prevent update in one table if particular data is available in another table
I need a MySQL trigger that can prevent update of a particular column data, if some specific data is available in another table.
I have two tables product and sales.
Product
Sale
I need to prevent ...
- 13
0
votes
0
answers
20
views
mysql Trigger to prevent entry being written to a table AFTER writing to another table
Is there a way to use SIGNAL in a trigger to prevent a query being written to a table, but only AFTER you have logged the details in another table?
for example:
CREATE TABLE `test2` (
`txtvals` ...
- 1,932
1
vote
1
answer
57
views
PostgreSQL - Recursive Foreign Key - Depth of 2 Check
In my system, I have employees that belong to a single department. However, some departments are large enough to have a sub-departments, so I have created a single department table like so:
CREATE ...
- 453
1
vote
1
answer
62
views
Triggers: List of ALL the OLD record's column names
I have a trigger that creates a jsonb field of all the OLD columns of the relevant table in an audit table. However, I need to exclude any geometry columns, in my case, the column name being the_geom. ...
- 13
1
vote
0
answers
31
views
Trigger on partitioned table and performance [duplicate]
I'm not sure if what I've found is a bug, but it certainly looks like it. I wasn't able to find much information about I'm not sure if what I've found is a bug, but it certainly looks like it. I wasn'...
- 354
6
votes
2
answers
671
views
Trigger on partitioned table and performance
I'm not sure if what I've found is a bug, but it certainly looks like it. I wasn't able to find much information about it, so I've decided I'll put it here.
So, in a nutshell, I'm facing terrible ...
- 354
-2
votes
1
answer
44
views
Can't create MS SQL Server trigger
I'm experiencing unexpected behaviour in my database.
I'm trying to create a trigger, and getting the error that it already exist:
Msg 2714, Level 16, State 2, Procedure xxxx_xxxxx_INSERT, Line 2
[...
- 27
0
votes
2
answers
45
views
Help With Creating a Trigger
I'm having a lot of doubts about creating a Trigger After Insert that after entering a new flight checks if it is the first flight of the assigned plane.
In this case, update the Aircraft.description ...
0
votes
0
answers
24
views
Copying a record using Trigger in the same table
I have a table issues with following fields.
Whenever a new record is inserted in the issues table, I want that inserted record to be copied (as a backup) in the same table(issues). I have created a ...
0
votes
1
answer
90
views
Normalizing an old database
I have a need to normalize an old PostgreSQL database, and I have an idea on how to do it which I would like to hear your opinions about.
The aspect that needs to be fixed is this:
We have a consumer ...
- 101
0
votes
1
answer
87
views
Trigger execution order after delete
Let's say I have two tables, one called ParentTable, and another called ChildTable, with a one-to-many relationship, where one Parent may have many Children.
I'm creating an AFTER DELETE trigger on ...
- 139
0
votes
1
answer
31
views
PostgreSQL triggered update function lock mechanism
I'm fairly new to database administration and PostgreSQL, and there's some things I'm not familiar with, even tho I've read a lot of the PostgreSQL documentation.
Here is a simplified version of the ...
- 103
0
votes
1
answer
119
views
"Function unaccent(text) does not exist" in update trigger
I've created a trigger for table x1 to update a column y with the expression to_tsvector(unaccent(x1.col1 || ' ' || x2.col1)). This trigger function throws:
function unaccent(text) does not exist
...
- 103
2
votes
1
answer
125
views
"column new.total does not exist" error while creating a trigger
I have spent a lot of time investigating this error and couldn't find a solution.
I am trying to create a trigger function and a trigger and then call the procedure when the when () condition in the ...
- 21
1
vote
1
answer
143
views
Use variable as column name
I'm trying to create trigger in PostgreSQL 14:
create or replace function add_stats_to_player() returns trigger as $add_stats_to_player$
declare
equipment_id int := new....
- 11
2
votes
1
answer
52
views
Understanding `WHERE x = NEW.x` Syntax
I'm writing an application that uses SQLite to store court information obtained from a government database in a client table. I've set up a DB Fiddle here, which is a simplification of my application.
...
- 123
2
votes
1
answer
345
views
Trigger works for UPDATE, but for INSERT
In my database schema an organization can have multiple addresses but only one default address. I'm trying to create a trigger where if the is_default column is set to true on an insert or update, it ...
- 123
0
votes
0
answers
89
views
During the trigger execution getting error : ORA-00604: error occurred at recursive SQL level 1 ORA-00900: invalid SQL statement
When I am trying to execute this trigger getting following error:
CREATE OR REPLACE TRIGGER create_table_trigger AFTER CREATE ON SCHEMA DECLARE
lv_sql VARCHAR2(5000);
obj_name VARCHAR2(...
1
vote
0
answers
68
views
Prevent Dacpac dropping columns via ALTER_TABLE trigger in TSQL
We're using SSDT to deploy database updates to clients, but sadly they will soon have the ability to add their own columns to tables in their databases. Obviously these won't be included in our model, ...
- 11
2
votes
2
answers
459
views
how to update one column with trigger mysql
i have 2 tables the first one is
Table KEYS
game
keys
WOW
AAAAAAA-AAAAAAA-AAAAA
WOW
BBBBBBB-BBBBBBB-BBBBB
L4D2
AAAAAAA-AAAAAAA-AAAAA
TABLE inventory
game
country
language
pieces
WOW
USA
EN
0
...
- 31
2
votes
1
answer
65
views
Multiple Computed, Dependent Columns on INSERT in SQL Server?
I'm relatively new to database programming and am looking for some some theory/best practices and feedback on an issue involving multiple dependent, cascading, computed columns for an OLTP SQL Server/...
- 21
2
votes
1
answer
177
views
SQLite: Can you use a DELETE trigger to delete other records in the same table?
In this specific case I'm using a SQLite database, but I believe this to be more of a conceptual question with practical implications.
I have a nested-set model in my database and I would like to ...
- 123
0
votes
0
answers
22
views
most efficient way to synchronize tow databases with minimum resources
i have two databases ,
one of them is in HFSQL server i use it as primary database.
and the second is hosted in MySQL server .
how can i synchronize the second(MYSQL) to follow my primary(HFSQL)
...
logiciel makseb
1
vote
2
answers
1k
views
Use default value instead of inserted null
I have a table definition in Postgres that use now() for timestamp and current_user() for auditing.
date_created date NULL DEFAULT now(),
edited_by varchar NULL DEFAULT "current_user"(),
...
- 273
0
votes
1
answer
569
views
Postgresql error syntax error at or near "END;"
Can someone explain why it throws error ERROR: syntax error at or near "END;" at this block of code:
CREATE OR REPLACE FUNCTION find_equal(object objects []) RETURNS text [] AS $$
DECLARE ...
- 11
0
votes
1
answer
347
views
How to handle possible NULL in a Trigger Updating a table without a Primary Key
I'm using Postgres Triggers to keep two tables in sync for a short term migration. However, I have one join table that doesn't have a primary key, but two columns that function as a composite primary ...
- 101
1
vote
1
answer
280
views
Trigger uses a procedure or a function?
PostgreSQL version 10 and before use the term "Trigger Procedure" (https://www.postgresql.org/docs/10/plpgsql-trigger.html). But later versions use the term "Trigger Function".
...
1
vote
0
answers
317
views
Postgresql trigger insert on another table after update and update other tables
I have a post table and when I soft delete a post I want to also soft delete rows on the comment and post_image tables using a trigger.
I have an intermediate table deleted_post where I insert the ...
- 111
0
votes
1
answer
126
views
Triggers: counting rows in a bridge table after INSERT
I'm using a trigger in order to make a Statistic Table. I have this many-to-many relationship where I have an Order that can have multiple Product's and viceversa. So after an INSERT on Order, using ...
1
vote
1
answer
483
views
Postgres' conditional trigger: performance consideration
In my DB i need to make conditional trigger, and i stumbled upon this article. Discussed in the article, there are 2 options to implement the conditions:
Option A: condition resides in the trigger ...
- 177
1
vote
1
answer
96
views
Deadlock reading table inside an update trigger
I am using Microsoft SQL Server 2019 with an UPDATE trigger that reads the table that is being updated. If the trigger is called simultaneously, there will be a deadlock situation.
The trigger roughly ...
- 177
0
votes
0
answers
30
views
phpmyadmin Triggers
welcome
I have three tables in a database and I want to link them
videos
channels
channels2
i have code
SET @channel=(Select channelnumber from channels where flag='0' and channelnumber not in (...
5
votes
1
answer
192
views
Why does UPDATE FROM fail and MERGE works on a View with an INSTEAD OF Trigger?
Sometimes for refactoring, I use a View to abstract the changes and use INSTEAD OF Triggers to mimic the previous functionality.
I've run into error 414 (or 415) in the past
UPDATE is not allowed ...
- 1,865
0
votes
0
answers
47
views
Is it possible to run a query for EVERY DDL change that occurs in a server?
I want to run a query on each and every database that is change in whatsoever DDL change possible.
For example, if I have 10 databases, A, and B, and ... in my SQL Server instance, and if a developer ...
- 179
0
votes
2
answers
51
views
Oracle 12cR2 - After Logon Exception
Logon trigger as below:
create or replace TRIGGER USER3.MY_TRIGGER
AFTER LOGON
ON DATABASE
BEGIN
IF SYS_CONTEXT ('USERENV', 'SESSION_USER') IN ('USER30') THEN
IF SYS_CONTEXT ('USERENV', 'HOST') NOT IN ...
- 29
0
votes
0
answers
19
views
What's the practical difference between these two triggers?
Say I have a table with a column to inform whether its row is a default of the application or not:
CREATE TABLE IF NOT EXISTS "mytypes" (
"idx_type" INTEGER NOT NULL,
&...
- 123
11
votes
3
answers
635
views
MERGE into a view with INSTEAD OF triggers
I have a view with instead of triggers and I'm trying to use it with EF Core, which tries to batch inserts together in a form of merge statement. Here's my table and view:
create table tbl (id ...
- 213
0
votes
1
answer
57
views
MySQL trigger communication between two tables
The problem is: I'm trying to find a value from one table where their vaccine choice is 'Janssen', I want the trigger to then copy their patient_id, the result of this will update the patient table ...
1
vote
1
answer
328
views
MariaDB error with trigger and end statement
I am getting a MariaDB server version for the right syntax to use near 'END IF; END'
I've tried to remove the semi colons sitting getting the errors, I thought I had done it in the correct syntax.
Can ...
1
vote
1
answer
85
views
Do existing connections immediately honor a change to the TRUSTWORTHY setting?
When you change the TRUSTWORTHY setting, will existing connections immediately honor this change?
I had an experience that seems to suggest existing connections don't immediately honor the change but ...
- 439
1
vote
1
answer
143
views
Dynamic trigger function to delete oldest rows of a table
I am trying to generalize a trigger that deletes the oldest rows in a table as new rows are added. Since the number of rows to retain depends on the table, I am passing that number to the trigger ...
0
votes
1
answer
89
views
How to create a trigger that generate a random specific output to insert into a table in MariaDB?
I have an account table which is laid out in 3NF:
CREATE TABLE account (
account_id INT AUTO_INCREMENT,
customer_id INT NOT NULL,
account_type_id VARCHAR(15) NOT NULL,
is_active ...
- 13
1
vote
0
answers
97
views
How to log changed table fields in SQLite?
I have a table that looks like this
CREATE TABLE IF NOT EXISTS "ebooks"
(
"id" INTEGER PRIMARY KEY,
"name" TEXT NOT NULL,
"author"...
- 25
1
vote
2
answers
330
views
What is the difference between tsvector_update_trigger and ts_vector generated column?
Newbie to full text search, I have a feed_items table as follows
feed_items (...title varchar, summary varchar, fullarticle varchar,...)
I want to create a ts_vector column to search query from ...
- 266
0
votes
0
answers
18
views
Trigger implementation , did i do it properly?
FOLDER(CodFO,name,path,dim,ContainerFolder)
FILE(CodFI,name,CodFO,path,dateC,dateE,dim)
After a file is inserted into the FILE class , its path field gets updated after the instert in a way that it ...
1
vote
1
answer
825
views
How to make mysql trigger to access another table's entry?
Say there is a DB with 3 tables
Books table - contain book_id and its title
Inventory - A list of book_ids in the inventory
Logs - A list of log strings.
Every time I add a new book_id to the ...
- 45
1
vote
1
answer
103
views
Postgres before insert trigger access field of a type
In the DB, I have a type and a table that one of it's rows is that type, such as this:
CREATE TYPE state (last_active timestamp, ...)
CREATE TABLE devices ( state state, ... )
For any interaction ...
- 113
2
votes
2
answers
197
views
What are the Cons of using triggers in place of count(*) for counting related entities?
What are the cons of using the below approach?
What steps can be taken to prevent someone from incorrectly updating the count?
Is the below approach an anti-pattern? If yes, what better approaches ...
- 185
0
votes
0
answers
19
views
Actualizar en Trigger de delete en Mysql8
tengo una tabla en MySQL8 y una situación similar a esta:
Tengo solucionado el trigger de insert, pero al querer hacer el trigger de delete (por ejemplo) me dice que no puedo modificar la tabla ya ...
