Skip to main content

All Questions

Tagged with
Filter by
Sorted by
Tagged with
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 ...
ymudyruc's user avatar
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 ...
Roberto Iglesias's user avatar
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, `...
Steve Miller's user avatar
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 ...
Alain Lagune's user avatar
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\ ...
Jade Fernandes's user avatar
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 ...
Fire's user avatar
  • 1
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 ...
PRAMOD MANN's user avatar
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. ...
студент001's user avatar
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 ...
Minioua's user avatar
  • 31
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 ...
whitehat101's user avatar
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 ...
BrownBatman's user avatar
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 ...
Omaruchan's user avatar
  • 101
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 ...
Joe's user avatar
  • 179
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 ...
Nikola's user avatar
  • 1
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 ...
Bhaswanth Koka's user avatar
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 ...
tango91a's user avatar
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 ...
Hermes_88's user avatar
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) ...
Anthony's user avatar
  • 113
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 ...
pbarney's user avatar
  • 171
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 ...
PGerrits's user avatar
  • 123
-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:...
ashiq pervez's user avatar
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 ...
Blastcore's user avatar
  • 103
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 ...
MOwneZ's user avatar
  • 1
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....
H. Pauwelyn's user avatar
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 ...
donutguy640's user avatar
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 ...
davidtgq's user avatar
  • 749
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 ...
SHR's user avatar
  • 884
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 ...
pkaramol's user avatar
  • 211
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 ...
youcantryreachingme's user avatar
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 ...
Adam's user avatar
  • 1
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 ...
Roxzurafa's user avatar
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 ...
m3asmi's user avatar
  • 217
-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 ...
Abdullah Salim's user avatar
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 ...
Leehan's user avatar
  • 207
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$ ...
Leehan's user avatar
  • 207
-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 ...
o2junior's user avatar
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 ...
Geist's user avatar
  • 35
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 ...
srh's user avatar
  • 313
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),...
Michael R.'s user avatar
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 ...
Robert Low's user avatar
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 ...
Abu Abed Md. Shohaeb's user avatar
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 ...
au42's user avatar
  • 13
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 | |---------------|-------------|...
tacticz03's user avatar
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; ...
User1974's user avatar
  • 1,497
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 ...
dragmosh's user avatar
  • 105
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 ...
joseasrocha's user avatar
-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' ...
Ehsanul Karim's user avatar
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 ...
user2676140's user avatar
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), ...
samule's user avatar
  • 25
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 ...
Nick's user avatar
  • 37