All Questions
75 questions
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 ...
0
votes
1
answer
123
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
216
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
0
answers
150
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 ...
1
vote
0
answers
280
views
update trigger after insert on same table
So, I'm supposed to make a trigger that applies md5 to a row after an INSERT was made on it.
Here is what I came up with:
delimiter $$\
create trigger pd_encrip before insert on usuario\
for each row\
...
0
votes
2
answers
1k
views
how to run trigger on table on postgresql except one column
I'm using postgresql 13/14.
lets say I have a table with column A,B,C,D,E
I have created a trigger for the table that would run every insert/update/delete. (but in this problem, I only need it for ...
0
votes
1
answer
2k
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 ...
2
votes
1
answer
59
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.
...
2
votes
2
answers
3k
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
...
0
votes
1
answer
2k
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 ...
0
votes
0
answers
102
views
How to run both "before" and "after" update in trigger
I need to calculate prior values in a table before the column values are updated. Basically, I have to calculate the difference between the before the update and after update values. If there is any ...
0
votes
1
answer
180
views
Postgres preventing update on "events" table but insert "warning" event
I'm facing a bit of a problem on some restrictions I'm trying to implement on my postgresql database.
My dilemma is as follows: I have an event table that logs basically everything that happens on my ...
0
votes
1
answer
744
views
Postgres per-statement update trigger relationship between old and new transition tables
Is there anyway to associate rows between the old transition table and new transition table for a per-statement update trigger in Postgres? docs
My use-case is that I have a "shadow" table ...
0
votes
1
answer
498
views
MS SQL Creating Tables and Adding Values by a Trigger
Table_A is an existing table which has 3 columns Id (PK), Name, Stock.
I would like to create a trigger to create a table.
For example, Table_A has an item, Id (PK) = 3, Name = Apple, Stock = 10.
By ...
0
votes
1
answer
907
views
SQL Trigger to Insert a row in Table B is the record is inserted in Table A [closed]
I have a requirement where I have to Insert a Record into Table "Site" after the record is inserted into InventOperationalSiteStaging Table. I created a SQL trigger which is working fine for ...
4
votes
2
answers
9k
views
PostgreSQL Trigger to Update Inserted Row With Data From Another Table
I have 2 tables in PostgreSQL-12, SENSORS (receives timeseries data feed from sensors) and LABELS (contains sensor labels & meta-data).
I am trying to create a PostgreSQL trigger to update the ...
1
vote
1
answer
1k
views
SQLite: Using count in triggers to update another column
UPDATE: Fixed. Not 100% sure why sqlfiddle.com didn't like my syntax, but using https://dbfiddle.uk/?rdbms=sqlite_3.27 worked. I did change the last WHERE clause to be based on the id column, so that ...
1
vote
1
answer
2k
views
MS SQL Server 2019 - deadlock after row update by primary key, possibly caused by trigger
I've a table:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[users](
[id] [int] IDENTITY(1,1) NOT NULL,
[username] [nvarchar](65) NOT NULL,
[nickname] [nvarchar](65) ...
1
vote
1
answer
1k
views
How do you combine multiple update statements for the same row using MySQL trigger
Background:
Each time a column is modified, I need to update the associated column (which has the same name) in a second table. This is my first attempt at using a trigger.
Code:
Here's a simplified ...
1
vote
0
answers
2k
views
Create a Postgresql trigger that updates column with PK of other table
Hello and thanks for taking the time in advance to look at my question. This is my first try at using triggers and functions in PostgreSQL (using DBeaver) and I appreciate your feedback.
I'd like to ...
-1
votes
1
answer
1k
views
How to update records of two tables through trigger?
I have two tables as employee and employee_backup whenever i update employee table the trigger must update employee_backup table also :
this is the piece of code i was trying to achieve specified goal:...
0
votes
1
answer
68
views
Update main and second table with a before update trigger for message logging
I currently have the following testing schema:
CREATE TABLE userMessage (
message_id BIGSERIAL PRIMARY KEY,
creator_id BIGINT NOT NULL REFERENCES "User" (id),
created_at BIGINT ...
0
votes
0
answers
58
views
Creating a trigger which stores updated value with old value in separate table
I'm trying to create a trigger which adds an old value to a table with the new value next to it.
For example, if someone were to update their address, I'd like the trigger to add the old address to ...
0
votes
1
answer
254
views
Create an incremental number with year and month without updating the entire table using a trigger
My situation and code
I'll add a number to my MS SQL Server table. It must be an unique number beginning with the year and month of the time when it's created, followed by an incremental index number....
1
vote
1
answer
3k
views
Can a MySQL trigger include a where clause
I need to create a trigger that, any time a goat's sireRegNo column is updated, its sireName column is automatically updated to match.
I have a database that stores animal genealogy. Each row stores ...
0
votes
3
answers
1k
views
How to prevent trigger from overwriting values that were explicitly set?
I tried this but doesn't work because NEW.updated is never NULL (I suppose NEW gets populated by the values currently in the row in addition to the values in the update-statement?):
create temp table ...
1
vote
1
answer
100
views
What is the fastest way to (session) lock table?
I have some triggers to log changes on a table to Log table.
On insert and delete I add row to the Log table and for update I add two rows.
The log table contain identity column, and I want the 2 ...
0
votes
1
answer
1k
views
Pass parameter to trigger dynamically
I have a table on a mysql 5.7 db, containing say athletes with their mean, max, avg times in a specific sport.
I have another table that lists some calculated statistics based on those values.
I ...
0
votes
2
answers
1k
views
Why is a trigger preventing update? [closed]
I have a script that runs weekly. It updates multiple tables in my database. It turns out one statement attempts to update 150,000 rows on a table that contains 250,000 rows. That failed this week ...
0
votes
1
answer
1k
views
Checking update value before update
First time posting so apologies if this has been asked already.
I have a table in SQL Server that is being updated from many different locations with data that I don't want in the table. I have come ...
2
votes
1
answer
6k
views
Insert Trigger to a Linked Server error
I created an insert trigger in a table that will fire when an insert/update/delete is made. The trigger is supposed to send information about the query that fired it, but every time it fires, the ...
0
votes
2
answers
2k
views
creating a trigger after update exclude a column?
I want to create a trigger that set a column to to_sync true if the other columns has been changed.
CREATE TRIGGER [dbo].[product_trig]
ON dbo.product
AFTER UPDATE
SET NOCOUNT ON;
IF NOT ...
-1
votes
1
answer
3k
views
Trigger to check if a column is updated and another one has a specific value
I'm writing a trigger for a SQL Server database. What I want is, when a table is updated, check if a specific column is updated or not and if another column has a value = 'W' or not. If it is so, the ...
2
votes
1
answer
2k
views
Trigger on 2 tables : issue with INSERT and UPDATE
This is related to my previous question:
Trigger to cascade updates to a second table.
In my Postgres 9.6 DB, I had 2 triggers on emprise (formerly table1), supposed to insert or update some fields ...
1
vote
1
answer
2k
views
Trigger to cascade updates to a second table
In my PostgreSQL 9.6 database, I am trying to update 2 fields in table2 with a trigger when table1 is modified.
Function:
CREATE OR REPLACE FUNCTION schema.maj_tbl2()
RETURNS trigger AS
$BODY$
...
-2
votes
1
answer
103
views
Simple Trigger Is Not Created in Mysql
I'm creating a trigger in mysql, but even with the correct syntax and the query being accepted the trigger is not created. If anyone can help. The following is the sql:
DELIMITER $
CREATE TRIGGER ...
1
vote
1
answer
957
views
How to check with update trigger if only one column was updated postgresql
I have a table with many columns and one special column that indicates if is synchronized or not. When an update occurs in the other columns I processed and update a 'log' table, this updates comes ...
21
votes
3
answers
34k
views
SQL Server : How to disable trigger for an update only for your current session?
I am working on SQL Server 2008 R2.
I have a table benefit which has a AFTER INSERT, UPDATE trigger named tiu_benefit.
I want to write an UPDATE statement for this table to update 1 row but I dont ...
2
votes
3
answers
20k
views
UPDATE Trigger with condition
I'm trying to trigger an update on a specific user in my user table.
Basically is someone tries to change that user then I want to rollback whatever query was executed and disable the user(using a SP),...
0
votes
1
answer
1k
views
How do you perform a MYSQL After Update Trigger Insert Loop?
I have 3 mysql tables, one called batches, one called base_formula and one called raw_materials_stock.
I want to perform an after update trigger on the batches table to insert information into the ...
1
vote
1
answer
588
views
PL/SQL Insert Update Before Trigger
I have two tables ProjectsTable and ExpenseTable given following and a trigger ExpenseSum which update ProjectsTable when a value is inserted in ExpenseTable table. Now, I want to modify Trigger so ...
1
vote
2
answers
84
views
Audit Trail trigger can't detect if dates are the same
I know this is a common problem with SQL language triggers, but I just cannot seem to get this right.
Basically I'm trying to write an "Audit Trail" trigger for all of my fields. I'm using an IF ...
4
votes
1
answer
3k
views
Execute dynamic INSERT ON CONFLICT DO UPDATE in plpgsql function
I have this table that is created every month. ex. model201707 ('model' = fixed; '201707' = year & month based on the current date):
| COLUMN_NAME | TYPE |
|---------------|-------------|...
1
vote
1
answer
130
views
Update coordinates after edit
I have a roads table. The road lines are stored in a user-defined type column called SHAPE.
Normally, spatial entities just have x and y dimensions. In my case, my roads have a measure(m) dimension; ...
0
votes
2
answers
10k
views
Why does this trigger slow down updates so much
I have a table. Lets call it table tableA. There's another table which keeps data about the history of tableA. Let's call that tableA_records.
On tableA, there's a trigger on update which looks ...
1
vote
1
answer
6k
views
Error updating multiple rows on a table with triggers
I have a Table Products with a Trigger to create a record on each update made to Products (something like a Log).
Everything works fine if I update one record at a time but if I try to update ...
-1
votes
1
answer
163
views
Mysql trigger on insert based on number match [duplicate]
I am using MySQL and I want a trigger on my table sms after every insert which will do the following:
Update sms set provider = 'gp' where number like '88017%';
Update sms set provider = 'blink' ...
0
votes
1
answer
52
views
Default Value Of Table
In my example syntax below I want the field delayed to be the difference in days between gotitdate and dunitdate I know that this syntax will get me the result, and I was going to set this up as an ...
3
votes
4
answers
4k
views
Prevent UPDATE TRIGGER to fire when no changes made in SYBASE
In Sybase I have a main table test and an audit table test_a. The audit table is updated by triggers on insert, update and delete. Now the issue is, when I use update query with old data (no changes), ...
1
vote
0
answers
3k
views
PostgreSQL Partitioned table update trigger
I have a date-partitioned table (inheritance partitioninng just like the one pg_partman does) which is referenced by quite a few tables in my database. To resolve the FKs checking for referenced field ...