Tagged Questions
1
vote
1answer
18 views
Trigger not Working
I created trigger on table PENDING.
Pending table has 3 columns
uniqueId
duration
maxDuration
I have another table COUNT with 2 columns
req_id
total
Here is my trigger--
CREATE TRIGGER ...
1
vote
2answers
32 views
Federated tables and triggers
This is the scenario.
Have two MySQL servers (S1, S2) on different machines, with a database on each (DB1, DB2).
I have a table (T2) on DB2 that needs to "fectch" rows from another table (T1) on ...
0
votes
1answer
73 views
Synchronise data from one table to another & vice-versa using triggers
We are migrating from one database structure to another - very slowly. The system is MySQL. There are two databases. For example's sake, we will call the databases old_db and new_db. Both databases ...
0
votes
1answer
40 views
MySQL Replication and Triggers
I have stumbled upon an interesting MySQL Error message that I do not really know how to interpret.
The setup:
There are two tables A and B.
When data is written or updated in the table A, then a ...
1
vote
1answer
34 views
Trigger not firing when deleting a record from stored procedure MySql
Bare with me, as I'm fairly new to stored procedures / triggers.
I have the following Stored Procedure:
DELIMITER $$
CREATE DEFINER=`username`@`serveraddress` PROCEDURE `TASK_APPROVE`(IN idtask ...
1
vote
1answer
57 views
Is it possible for a trigger to update a row in a table using a value that was deleted?
I have a trigger which is updating a column in a different table after the row is inserted:
mysql>delimiter $$
mysql> create trigger status
after insert on table2
FOR EACH ROW
BEGIN ...
0
votes
0answers
60 views
Resolving a “Multiple triggers with the same action” error [closed]
What's wrong with my trigger? I'm getting this error:
1235 - This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'
whats causing this ...
0
votes
1answer
43 views
How can I modify this trigger with loops and concat?
I have this trigger below which inserts new values in the an emplog table. I want to have my trigger insert in the description column the event that took place. For example, if I changed the last_name ...
0
votes
1answer
32 views
Is there a workaround for lack of support of cascade triggers with MySQL?
As things stand, cascaded foreign key actions do not activate triggers with MySQL. I don't want to get into the debate as to whether or not this is good, I just would like to know whether there is a ...
0
votes
1answer
166 views
Execution of a java program by a trigger
Can an update trigger execute a java program?
I have a table T1 with a column named Flag. Whenever Flag changes, I want to run a trigger that results in an execution of a java program.
0
votes
2answers
80 views
Should I disable triggers, timestamps in a slave in mysql?
We're using MySQL. We have a master that eventually will have 2 slaves. There are triggers in the db that execute when data changes in certain tables. I am wondering whether to disable the triggers in ...
0
votes
2answers
139 views
mysql trigger on update with insert statement
I am trying to create a simple trigger which is handled on update operations. The trigger is checking before update if a record of an update exists, then if not it is created.
DELIMITER ;;
CREATE ...
0
votes
1answer
96 views
Two insert statements inside trigger - Duplicate entry '521-2' for key 'PRIMARY'
I'm trying to create an insert trigger so that when a new user is inserted into users table (via site, httpd and php), part of this information is inserted in another table that manages access to a ...
2
votes
1answer
160 views
MariaDB, Triggers and last_insert_id
I have a situation where I have three tables: sample, sample_name and run (extra columns removed to be only relevant information).
CREATE TABLE `sample` (
`sample_id` int unsigned NOT NULL ...
0
votes
0answers
76 views
mysql alternative to prepared statements in triggers
I'm trying to get some stored procedures to fire from a MySQL 5.1 trigger. When these were called without triggers, I used prepare and execute statements to build queries and other SQL calls. I have a ...
0
votes
0answers
36 views
Duplicate entry '0' for key 'UQ_CallsIDCC'
I have my own Server with Voice Switch and not really familiar with SQL.
Having problem with following:
02-25 12:05:32 MySQL error description : Duplicate entry '0' for key 'UQ_CallsIDCC'
02-25 ...
2
votes
2answers
764 views
how to add trigger in mysql workbench, without UDF, using EER design
I have designed a simple database using EER in workbench. However, i want to have a minimum value for the attribute salary (compared with min_value attribute from artist table). Here is the schema:
...
1
vote
0answers
205 views
Trouble with triggers and insert .. on duplicate key update
I have two tables with identical schemas. one is a "main" table that is pruned periodically and kept small. The other is a "reporting" table that is an archive of everything that ever was. For ...
2
votes
2answers
310 views
Comparing dates in a BEFORE INSERT trigger
I need help making a TRIGGER that compares two dates being entered into a table, but I'm not 100% on the syntax I should use.
Something like:
CREATE TRIGGER chk_dates
BEFORE INSERT ON ...
2
votes
1answer
205 views
trigger in MySQL to disallow multiple row deletions at a time on a table
How do we create a trigger in MySql to disallow deletion of more than 2 rows from (say) employee table by a single query?
delimiter \\
create trigger not_more_than_2_rows
before delete on employee
...
1
vote
1answer
183 views
How to write signal function in MySQL that can be called from Triggers and Stored Functions?
In my Database I have a table: Employee with recursive association, an employee can be boss of other employee.
The Table Description:
mysql> DESC Employee;
...
1
vote
1answer
140 views
processlist showing '%' as host from trigger
I'm trying to populate a certain column in a table with the host/IP of the caller. I have an insert trigger on the table which sets the column like so:
CREATE TRIGGER access_insert_trg BEFORE INSERT ...
1
vote
2answers
357 views
How to insert into junction table using triggers
Sorry in advance if this is "basic SQL." I wanted to know how to update my junction tables automatically. For example, these are my tables.
Artist and Song are base tables and SongArtist is the ...
3
votes
2answers
474 views
How to make a trigger that will compare input with a value of other table?
I had made two tables with the following fields:
TABLE 1: "Bookings"
FIELD: "fk_flight_number"
FIELD: "date_of_reservation"
TABLE 2: "Flights"
FIELD: "flight_number"
FIELD: ...
2
votes
1answer
132 views
MySQL generic trigger to save identification data for later accessing of the changed row
I am pretty inexperienced with this.
I need a generic trigger, able to create and save in a fixed table some sort of identification data for a changed row from generic (any) table. The ...
1
vote
1answer
364 views
How do triggers affect last_insert_id()?
I am unable to test this right now, however I'm curious about the answer.
If I have a trigger on table A that writes a new record to table B, will LAST_INSERT_ID() tell me the updated ID for A or B? ...
1
vote
1answer
2k views
Declaring and using variables in triggers
I have been testing something with triggers today, but I am stuck with it and I do not have any idea what is causing my problem.
DELIMITER $$
USE `pucko`$$
CREATE TRIGGER `after_delete_Alien`
AFTER ...
4
votes
1answer
136 views
Prevent users deleting more than a certain number of rows in MySQL
The problem:
I have a table with rows, nothing special about it
When a database user removes a row, it should increment a number
A database user is only allowed to remove 3 rows max in the table
A ...
1
vote
1answer
1k views
MySQL trigger - delete data from multiple tables
I would like to delete data in multiple tables, from a trigger but it seem like my syntax is incorrect.
I got the error: "you've got a error in your MySQL syntax please check the manual".
This ...
2
votes
2answers
1k views
MySQL: Loop over cursor results ends ahead of schedule
I'm executing a loop that iterates over a cursor's results. The code is inside a trigger function, and the part that matters looks like that:
EDIT: Sorry, stupid mistake. The trigger is executed ...
2
votes
1answer
278 views
create trigger which inserts rows depending on enum values?
I'm quite new to triggers and I ran into general problems. I'd like to achieve this:
CREATE TABLE `searcharticles` (
`articleID` int(11) unsigned NOT NULL,
`ean` char(13) COLLATE utf8_unicode_ci ...
2
votes
2answers
185 views
simulate sequence on mysql in preparation for move to oracle
I've been developing an application against MySQL while a new development server has been slowly being set up. The new dev server will use oracle, and since we're getting close to it being ready, I ...
5
votes
1answer
2k views
Trigger to UPDATE after UPDATE?
I want to make a trigger to record the time of any update as
CREATE TRIGGER col_update
AFTER UPDATE ON col
FOR EACH ROW BEGIN
UPDATE col SET updated=NOW() WHERE id=NEW.id; // or OLD.id
END
The ...
4
votes
2answers
379 views
Implementing a Circular Buffer (Sliding window) in MySQL
I intend to store some Java objects in a MySQL database, accompanied by a timestamp. These objects should be kept in a Sliding Window fashion (also known as Circular Buffer), meaning that only the ...
1
vote
1answer
338 views
Triggers vs Views in MySQL
We are in the process of denormalizing a large amount of data for performance reasons. Basically a Query that would normally JOIN over 10 tables is replaced with a Query that has no JOINs, yet all the ...
1
vote
1answer
669 views
Keeping 2 tables in sync
I need to write a trigger that will keep one table synced with 2 other tables. I have table_a, table_b and table_c on slave. The master has only table_a and whenever this table is updated on master, ...
1
vote
1answer
214 views
MySQL: Try to Port a Trigger from SQL Server
Here, there's a sample of a trigger with "INSTEAD OF UPDATE" clause:
CREATE TRIGGER [dbo].[Test_PTA_Table_Update_trg]
-- ALTER TRIGGER [dbo].[Test_PTA_Table_Update_trg]
ON [dbo].[Test_PTA_Table]
...
1
vote
1answer
348 views
when will Innodb support activation of triggers on cascade foreign key? [closed]
The Manual says: http://dev.mysql.com/doc/refman/5.6/en/innodb-restrictions.html
Currently, cascaded foreign key actions do not activate triggers.
Any Idea when will this be implemented.
Is ...
2
votes
1answer
332 views
How to speed up audit trigger?
The task: write a trigger to log all updates within tbl1 table for future audit users' actions.
The tbl1 table:
id INT(11)
y SMALLINT(6)
country SMALLINT(6)
-- really here ~20 fields but only some ...
1
vote
3answers
1k views
Is it normal to use many Triggers?
I have many so-called summary columns throughout my database to count number of rows in other tables. For example, counting the number of comments by a user (a column in the user table). Currently, I ...
1
vote
1answer
513 views
Soft delete: Copy row data to archive table when deleting
How can I move data from my current table to an archive table when a delete is called on the table?
My current table and archive table needs to be in sync in their schema too.
Can someone help ...
2
votes
1answer
471 views
MySQL using trigger instead of check
Is there any way that I could use trigger instead of check? like I have
CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER test BEFORE UPDATE ON apply
FOR EACH ROW BEGIN ...
1
vote
1answer
146 views
Randomly generated password for mysql admin over time?
I have this idea of making my database's Admin account secured by creating the following:
Trigger is activated every hour in mysql this trigger activates a stored procedure
Stored procedure ...
2
votes
3answers
1k views
Disable trigger for just one table
Is it possible to disable a trigger momentarily but for just one table.
For example I have table, TableA with an on insert, update and delete trigger. I also have a table B with the same triggers but ...
2
votes
4answers
6k views
Call a stored procedure from a trigger
I have created a stored procedure in mysql using the following syntax.
DROP PROCEDURE IF EXISTS `sp-set_comment_count`;
DELIMITER $$
CREATE PROCEDURE `sp_set-comment_count` (IN _id INT)
BEGIN
-- ...
2
votes
1answer
272 views
MySQL Trigger problem with the email address (cause contains the @ sign)
I'm trying to set a trigger in a table (users) to "copy" the email address of a new row to another table (prova). Here is the trigger:
CREATE TRIGGER inserisciemail AFTER INSERT ON users FOR EACH ROW
...
2
votes
1answer
192 views
Has anyone ran into this replication breaking bug before?
I've been having this issue where replication was breaking on colliding primary keys. The annoying thing was these were auto increment generated.
I think my issues been tracked down to ...
1
vote
1answer
667 views
How to set up triggers in MySQL for copying a field
I have an in-house application that I've put together, and I'm VERY new to SQL in general.
I have a table that contains most of our data. Let's pretend it looks like this.
Matrix
|Name | ...
3
votes
2answers
1k views
MySQL trigger: Compare value in another table
I have the following trigger (generated using PHP via mysqli)...
DROP TRIGGER IF EXISTS post_queue_insert;
CREATE TRIGGER post_queue_insert AFTER INSERT ON posts
FOR EACH ROW BEGIN
IF ...
4
votes
3answers
536 views
Making a column immutable in MySQL
I want to make a column in a relation unmodifyable for consistency reasons.
The backstory is that I have a n:n relationship where the "connecting" relation has additional values. I don't want that ...
