All Questions
427 questions
1
vote
1
answer
77
views
MySQL Trigger updating second table based on History and computed values
The Goal:
Update an secondary table called Lottery_Cycle when an INSERT occours on table Lottery_History with some computed values and checking previous data.
Let's say a lottery with 25 numbers of ...
- 11
0
votes
1
answer
37
views
how to auto trigger mysql
I have two tables, User table and a points table. I designed these tables to be in one-to-one relation, as points contains a foreign key to the user table. as new users get created, I use query INSERT ...
- 37
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,
`...
3
votes
1
answer
409
views
Creating a Trigger in MySQL that has a JOIN
PROBLEM:
I'm trying to create a TRIGGER that will copy records from 2 different tables (users, addresses) in database A to a single table (users) in database B.
Basically, I'm trying to consolidate ...
1
vote
2
answers
158
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
1
vote
1
answer
210
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 ...
0
votes
1
answer
146
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
71
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. ...
1
vote
1
answer
232
views
MySQL 1054 Error After Creating Trigger to Update on Insert
I am learning basic SQL and have gotten stuck with triggers. I have a database with two tables, customer and invoice. The table characteristics follow:
CREATE TABLE customer (
CUST_NUM ...
- 11
0
votes
1
answer
2k
views
How to get around 'Can't update table in stored function/trigger because it is already used by statement which invoked'
In MariaDB/MySQL I am looking to create a trigger that automatically changes a boolean to true when 4 Users are a part of it. The issue is I am running a running a query on the table that is being ...
- 1
3
votes
3
answers
2k
views
MySQL: How to make Trigger to insert row in another table?
Please read everything, included the notes and edits at the end
Problem
I have a MySQL DB with 3 different table:
To save users
mysql> describe users;
+-------------+-----------------------------...
- 31
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
1
answer
547
views
Mysql Trigger - Update status_id if the value exist in other Table
Currently, I have two Tables:
ocok_country
status
country_id
1
1
1
2
0
3
0
4
** country_id 3 and 4 not updated with the trigger **
ocok_zone_to_geo_zone
name
country_id
ABC
1
DEF
2
new-...
- 23
0
votes
0
answers
49
views
problem with writing a sql trigger
I'm writing a SQL trigger to whenever update the (engage_step_user_response) table (response_json) column after updated. I need to increase no of retakes that I included same table as a column.
This ...
- 1
1
vote
1
answer
195
views
help with mariadb after update trigger
I have a table that is shared among 7 other tables and I want to synchronize a new part is added to the parts table. new rows are created in each of the 7 other tables, using the part_id from the ...
0
votes
2
answers
88
views
Determine new/changed data(rows) on a table during a table clone operation
I'm trying to alter some heavy data tables tables, this operation may cause some impact on our production service, so to minimize outages the plan is:
create a copy table that inherits table ...
- 13
0
votes
0
answers
686
views
MySQL IF NULL of a variable is not working as expected in a trigger
We have the following trigger to ensure that a version field is updated in a related table, normally the trigger contains only the UPDATE statement, the INSERTs are added for debugging and to explain ...
- 101
0
votes
2
answers
919
views
Triggers for multiple rows at once
So I have these three tables. The orders table is list of orders existed, while the order details consist of items that is in an order (because 1 order can have more than one catalog). I'm using mysql
...
- 3
0
votes
1
answer
188
views
Sync different table structure in MySQL Database
I have two table in different database host host. activity_table in Host A and presence_table in Host B. presence_table used by binary app where i don't have access to the source code but i have full ...
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 ...
- 13
0
votes
0
answers
187
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` ...
- 2,166
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
...
- 31
1
vote
1
answer
814
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 ...
0
votes
1
answer
253
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
2k
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 ...
0
votes
1
answer
468
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
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
4k
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
0
votes
0
answers
53
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 ...
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 ...
1
vote
2
answers
3k
views
Error: Dynamic SQL is not allowed in stored function or trigger
Im trying to convert multivalued column in Table A into rows of Table B(The data type of the column is varchar).
For ex:
Table A
id value
ae [1,2,4,5]
ac [1,6]
Table B
id value
ae 1
ae 2
ae ...
0
votes
1
answer
1k
views
Stored procedure to change string to datetime in MySQL
This is a follow up to this question. Turns out the driver that I am using (can't change it) is not compatible with timestamp values. Lets say it is set in stone that I have to load the data into my ...
- 105
0
votes
1
answer
1k
views
Row Level Trigger for insert/update to a table
Given a table ITEM as below :
CREATE TABLE ITEM (
ItemNo int not null,
category varchar(20) not null,
Quantity int not null,
Price decimal(10,2) not null,
Total decimal(10,...
- 13
2
votes
2
answers
2k
views
MySQL best way to define a couple of columns as both null or both not-null
On a MySQL DB, I would like to find a way to force a couple of columns be both null or both not null at the insert / update time. This means that on each record, both columns are null or both columns ...
- 131
-1
votes
2
answers
188
views
Create a trigger for a table whose values are dependent on another table
I have a hospital database and in that database I have an Appointments table and a Visits table. In the Appointments table I have a column named Validation that can have values 0 and 1.
I want to ...
-1
votes
1
answer
99
views
Hello I want to create a before insert that looks if the value is on another table before inserting [closed]
I have a database for hospitals and I have a table for patients and one table for appointments. I need to create a trigger that if someone wants to add a person on the appointments table that person ...
1
vote
1
answer
520
views
MySQL 8.0.22 Trigger won't run unless definer is logged in
I have the following tigger that used to work before upgrading to MySQL 8 from MySQL 5. Now the trigger will only run if the definer is logged in via phpmyadmin or some other database admin tool. The ...
- 113
1
vote
1
answer
944
views
MySQL create trigger after insert not inserting all values
I am new to MySQL triggers. i want to insert the newly inserted booking from booking table to bookings_clone table.
PROBLEM:
All the field in bookings table are filled but in bookings_clone only these ...
- 113
1
vote
2
answers
1k
views
Unknown table 'table' in field list on referencing temporary table inside a trigger
I am creating a trigger BEFORE INSERT that checks a record from another table to see if it exists, otherwise signal sqlstate. The trigger definition shown below
DELIMITER //
DROP TRIGGER IF EXISTS ...
- 13
0
votes
1
answer
157
views
Mysql falls every day
My database Mysql fallas every day:
Looking at the tail -100 /var/log/mysqld.log
Shows:
2020-08-16 20:56:03 7f787c7fb700 InnoDB: Error (Duplicate key) writing word node to FTS auxiliary index table.
...
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 ...
- 171
1
vote
0
answers
84
views
Catch contraint error inside trigger on the table being inserted/updated
I would like to know if it is possible to automate some task when an insertion or update on a table generates an error (for example error codes 1062, 1451 or 1452)
Finding an answer to that is not ...
- 111
1
vote
0
answers
330
views
InnoDB trigger deadlocks itself?
I have a MySQL 5.7.22 database using the InnoDB engine.
There's a table respondents that has 6 associated triggers - three of them make CRUD operations on a completed_respondents table, and the other ...
- 111
1
vote
0
answers
44
views
is there any method should i use for trigger update?
I try to modify the syntax of SQL trigger to mysql trigger
[check 32:01 for reference] ]1
someone suggested me to use this syntax in mysql, however I got error in mysql.
DELIMITER //
CREATE TRIGGER ...
- 11
0
votes
1
answer
564
views
MySQL : Stored Procedure from Trigger giving duplicate results
I have created a stored procedure to insert data into an audit table. The procedure accepts an argument that takes table name and same value is inserted into the audit table along with PK.
When i'm ...
- 157
0
votes
1
answer
64
views
Are having TRIGGERs for every upvote/downvote detrimental in terms of performance?
I plan to have three tables: Users, User_Reputation and Voting_Records. The Users table would hold the primary key id for the registered users, along with personal information such as email and ...
- 103
0
votes
3
answers
338
views
Can we prevent table from update directly but allow update using trigger from another table?
So, let's say I have 3 tables:
stocks table, it have columns: id, product_id, stock
sell table, it have columns: id, product_id, qty
buy table, it have columns: id, product_id, qty
What I want to do:...
0
votes
0
answers
112
views
Cascading Triggers
I'm running MariaDB 10.2.31 on Ubuntu 18.04.4 LTS. My data consists 99% of JSON-encoded strings from a remote API.
Example (orders) very much simplified:
{
"id":123,
"referrer": "webshop",
...
- 1
0
votes
1
answer
60
views
update all rows after insertion in mysql
I am working with mysql and I have a table with the following structure (a summary):
CREATE TABLE `costs` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`utility` DECIMAL(9,2) UNSIGNED NOT NULL,
`...
- 101
0
votes
0
answers
30
views
MySQL - Check out trigger not executing
Im implementing a trigger to be executed when the end date of a booking is passed
Its supposed to change a cats status within checked in from 1 to 0
when updating the table changing an ex booking ...
