1
vote
1answer
15 views

How to create trigger with NOT FOR REPLICATION

I tried adding "NOT FOR REPLICATION " in many parts in the below code but ended up in the below code. I searched a lot for an example where I can see how its done with a trigger. Can any one show me ...
0
votes
1answer
23 views

ALTER TABLE AUTO_INCREMENT number in a Trigger

I want to have my auto increment column to increment by step of 3 upon insert. Can I do something like: Alter table `mytable` auto_increment = (select max(id) from mytable) + 3; in a Trigger? I ...
1
vote
2answers
43 views

[SOLVED]The right syntax for handling an UPDATE trigger and logging what happened per column?

SOLVED The users table: CREATE TABLE `users` ( `id` int(8) unsigned NOT NULL AUTO_INCREMENT, `email` varchar(45) DEFAULT NULL, `username` varchar(16) DEFAULT NULL, `salt` varchar(16) DEFAULT ...
1
vote
1answer
55 views

Why do I get error 1054 using trigger on INSERT saying that column isn't on the field list?

I am trying to write a Trigger to register every new inserted genre into a news table. Here are the tables definitions. They are only two, GENEROS & NOVEDADES. This is the trigger: CREATE ...
1
vote
2answers
65 views

how should i do this trigger?

I am a newbie to the sql world :) Right now i'm trying to create my first mysql trigger that would either update a row or insert a new one. Could you help me with that ? Here is the code : DELIMITER ...
0
votes
1answer
34 views

How to perform a while loop on the query result

I created a trigger AFTER an UPDATE of a column in a MySQL database. When the trigger detects a certain value, it should perform some actions. This is what I have at the moment and is working fine. ...
0
votes
1answer
19 views

Trying to get this MySQL Trigger working

create trigger check_td before for each row begin declare v_code_ID varchar(25); declare c_Worker1 cursor for select code_ID from Worker1; declare c_Worker2 cursor for select code_ID from Worker2; ...
0
votes
0answers
32 views

Load XML is not working in mysql version 5.5

I have a requirement where i need to load xml file into a mysql table using trigger. I have written the following trigger to achieve this. CREATE TRIGGER asdb.payment_test AFTER INSERT ON asdb.abc1 ...
1
vote
3answers
62 views

Mysql to Oracle communication

I have a website running with php and mysql on Centos(Amazon instance). The newly brought ERP(to be integrated with existing system) uses Oracle as db(located on a separate Windows Server). The orders ...
2
votes
1answer
80 views

Do triggers affect DB performance

I have a database with 250 small and medium sized tables. These tables are related with both MyISAM and InnoDB storage engines. There are multiple triggers that have been created on different tables. ...
0
votes
2answers
53 views

Mysql trigger not working, syntax issues

I have this trigger CREATE TRIGGER checkcollision AFTER UPDATE ON players BEGIN SELECT RAISE(ABORT, 'collision') FROM walls WHERE NEW.x=x AND NEW.y=y; END; mysql 5.1.72-0ubuntu0.10.04.1 ...
0
votes
0answers
38 views

How to update Status using IF Condition in Mysql trigger

I'm trying to write an update trigger that will Design_Update_Status to Customized when a date is entered in Custom_Design_Update_Date field and if nothing is entered that field it should update ...
0
votes
2answers
84 views

How to autoincrement Prod_id based on Product_Name using trigger in mysql

Product_Details table : (Fields) Prod_id(Primary key), Product_Name, Prefix and Slno(Auto incremented) i need to increment Prod_Id based on the product name field and prefix field (Input will be ...
-2
votes
1answer
252 views

how to autoincrement varchar datatype depending upon the product details name using trigger

I have a product details table, the fields in the table are *Prod_Id, Product_Name, Quantity, Slno*. Now i have created trigger for varchar data type field and implemented auto increment for Prod_Id ...
2
votes
3answers
65 views

How to update cust_id from Customer table to order table Cust_id field while inserting customer name using trigger

I have two tables 1. Customer_details and 2. Order_details. 1.Customer_details table -->fields--> Cust_Id(Primary Key) and Customer_Name, 2.Order_details table --> fields-- Ord_Id(Primary ...
0
votes
2answers
35 views

Anyway to get the trigger to work?

I want to insert last insert query to the another column.For that i wish to use TRIGGERS -- -- Triggers `sync_log` -- DROP TRIGGER IF EXISTS `tg_sync_id_insertbgnn`; DELIMITER // CREATE TRIGGER ...
3
votes
1answer
325 views

Update DATE field when another field in the same table changes value. (Completely noob my google-fu didnt help.)

I'm awfully noob, so excuse my ignorance. I tried to google this but somehow i can't find an example on how to do this I hava a table, say: - id (autoinc pk) - name (varchar) - description ...
0
votes
1answer
73 views

Mysql - Select To XML

I'm Building Audit Trail with Trigger, and want to convert the inserted / updated / deleted value into XML and store it into another table. my trigger look like this: DELIMITER $$ CREATE ...
0
votes
1answer
124 views

How can I create a TRIGGER in MySQL from this Oracle syntax?

I'm teaching myself RDMSs through a text book. I'm used to using MySQL so I'm sticking to it, but the book uses Oracle. I'm trying to create a trigger in MySQL with the Oracle command CREATE OR ...
0
votes
1answer
62 views

Execute SQL query after execute TRUNCATE in a table

I have a table called fos_user_user where I have a default record "always". Since I'm testing all the time and sometimes I need to clean that table I'm asking if there is any way to run the following ...
1
vote
1answer
717 views

How to create trigger to update timestamp when record is updated or inserted in another table

I have a table t_task and a table t_taskaction with a foreign key to t_task. I would like to update timestamp from t_task that is refering the foreign key form t_taskaction with every update in ...
0
votes
1answer
245 views

Using count in triggers to update another table

Say i have a table called trx_request with the following data id | confirmed(TIME) | finished(TIME) ------------------------------------- 1 | 12:05:00 | NULL 2 | 13:11:00 | NULL and ...
0
votes
1answer
246 views

row-based replication with slave triggers

I'm replicating a prod MySQL database to an analytics one. I need to drop several tables with sensitive data from the analytics and also redact several columns of several remaining tables, replacing ...
1
vote
1answer
98 views

Can I execute an external process via MySQL on table change?

When using triggers, if an update is done to a table then a trigger is executed. This is very convenient. But what I would need is to execute an external script. Is it possible to configure MySQL ...
1
vote
1answer
251 views

column auto updated after 24 hours in mysql

i have a table T1 with column : Name State time mon 1 2014-01-09 11:23:00 tue 0 2014-01-07 14:40:00 wed 2 2014-01-08 09:23:00 thu ...
2
votes
1answer
169 views

How to call stored procedure inside trigger?

I have made stored procedure that will update product quantity as per product id. That works fine for me. Following is code : DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE ...
5
votes
2answers
217 views

Optimize queries from MySQL SP/Triggers/Functions

I have to investigate a MySQL Production server,While investigating I found a DELETE query in "SHOW PROCESSLIST" which was taking more than 400 second in preparing state, I tried to find the query in ...
-2
votes
1answer
179 views

Trigger not working, why? [closed]

What i'm doing wrong? CREATE TRIGGER `Calc` AFTER INSERT on `test`.`bookings` FOR EACH ROW BEGIN UPDATE `test`.`article` AS `ST` SET `ST`.`stock` = SUM(`test`.`bookings`.`amount`) WHERE ...
0
votes
0answers
45 views

MYSQL, how to build this Trigger

I have two tables in mysql Table: objects 1: ID (Auto Increment) INT eg: 1 2: Object (like "Flower) TEXT eg: Flower 3: Rank (like 90, calculated) FLOAT eg: 90 ...
1
vote
2answers
69 views

Creating a mysql trigger without hard-coding database name

Is there a way to create a trigger in mysql without hardcoding the database name? Doing a typical create trigger will stop working if I dump the database and then import it into a database of a ...
0
votes
1answer
56 views

Creating a My SQL Trigger

I want to create a trigger such that whenever a user is inserted in TABLE A(userid,type) his "userid" along with some predefined value depending in his "type" is inserted in TABLE B(userid,value) ...
-1
votes
1answer
417 views

how to log insert,delete,update queries in mysql

I want to log every insert,delete,update (successfully written) queries using mysql ? I tried following ways 1) used triggers ( but i can't get the SQL query ); 2) show processlists ( only showing ...
1
vote
2answers
3k views

MySQL Update Trigger that updates multiple columns from values in another table

I have a table PRODUCTS with columns id, name, sku, packaging_type, width, height, weight, case_count. I want a trigger to fire when packaging_type is updated. packaging_type will be an integer ...
0
votes
1answer
93 views

Are triggers bad for created/timestamp columns?

We are in need of some better auditing on some of our MySQL tables. Specifically, we'd like to store when the row was inserted and when it was last updated. Originally, I thought DEFAULT ...
0
votes
2answers
319 views

Trigger to delete inserted record if it doesn't exist in another table

I have a question. I have two tables: zip_code (zip_id, zip_code, state) - this is a static table that stores areas I service tech_zips (tz_id, tech_id, zip) - this is where I store what zip codes ...
0
votes
2answers
54 views

Trigger calls and application code

When a trigger is set on a table does the application code wait for the original statement and the trigger to execute before resuming or just the original statement? Consider application code to be ...
1
vote
1answer
2k views

mysql trigger to insert/update date column from datetime column

probably simple question but can't get it to work: I have a table with two datetime columns. In order to set up a date index and have simpler queries, i have added two date columns next to them. ...
0
votes
1answer
245 views

MYSQL Trigger not working

I am trying to create the following trigger: CREATE TRIGGER update_customers AFTER INSERT ON wp_usermeta FOR EACH ROW BEGIN if new.wp_metakey = first_name then insert into ...
1
vote
1answer
685 views

filling a table by triggering multiple tables on insert

I have a table m contains the following fields: id int(11) not null auto_increment, //PK name varchar(100) null, something_else varchar(100) default 'not important' I need to fill up table m by ...
1
vote
1answer
528 views

MySQL possibility to create global routines (stored procedures and/or functions)

Is it possible to somehow define globally available routines? It seems like every routine must be created in a scope of the database. When I tried to create a routine from console (without prior ...
1
vote
1answer
213 views

pt-online-schema-change tool will work for existing triggers for a table?

I have a table name "user" ,I have tried to do partition the table and it was having a trigger already. When i have tried to alter the table with pt-online-schema-change.It was showing the below ...
0
votes
2answers
175 views

What am I doing wrong in mysql database?

I'm creating a trigger that deletes rows from a table when they are inserted. However, when the trigger executes I get the following message: MYSQL ERROR: Can't update table 'player_viplist' in ...
0
votes
2answers
77 views

Update on cascade

I have two tables: service (id_service, libel, status) agent (id_agent, name, surname, id_service, status). The status field values ​​present in both tables have only two values ​​0 or 1 (0 = ...
0
votes
0answers
399 views

Update row to another table using mysql trigger

I have the following 2 tables (places & offer_company) in a MySQL database named COUPON1.81. CREATE TABLE IF NOT EXISTS `places` ( `id` int(60) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT ...
4
votes
2answers
1k views

Does a TRIGGER improve the performance?

I understand that TRIGGERs are useful, as they take care of subsequent queries, but do they have an advantage from performance point of view too? Replacing two or three queries as INSERT INTO table1 ...
0
votes
1answer
5k views

How to structure IF condition in MySQL trigger?

I am trying to write a MySQL trigger. I have two tables like this: Table A------------------------------Table B order_id--------sku---------------order_id----order_#----sku_copy ...
0
votes
1answer
2k views

Trigger to increment a column value after update in a column of same table

I have a table with two columns: C1 | C2 1 | 3 Whenever C1 gets updated to 0, C2 should be incremented by 1. I have developed a trigger: create trigger increment after update on T1 begin ...
0
votes
0answers
52 views

How to use trigger on my table?

I have a simple billing table consisting of client's phone number, client's connect date, client's expire date, and client's balance. For example: "Samuel Jonhson" 2013-07-01 2013-09-01 8 I want to ...
1
vote
1answer
317 views

GeomFromText in trigger has error

I have this trigger: DELIMITER $$ CREATE TRIGGER geometrytrigger BEFORE INSERT ON users FOR EACH ROW BEGIN SET NEW.user_geometry=GeomFromText(NEW.user_geometry); END$$ DELIMITER ; when I insert a ...
0
votes
3answers
2k views

Auto increment value needs to be changed inside trigger

I have a slight problem with a table that I have converted which used to be MyISAM, I have now made it a InnoDB table. Original structure: id varchar(15), seqno int(11) auto increment Joint PK ...