0
votes
1answer
25 views

MySQL Syntax Error When Creating a Trigger

I am trying to create a trigger but I am getting a syntax error and I am not really sure why I am. CREATE TRIGGER Section_Insert AFTER INSERT ON Section -> FOR EACH ROW BEGIN -> INSERT ...
1
vote
1answer
19 views

Can triggers be used to implement automated polymorphic association?

(Apologies for any bad formatting or ignorance of common practices within this community; this is my first question here.) I'm attempting to (re)design a MySQL database for a personal social ...
1
vote
1answer
19 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
34 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
44 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
70 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
68 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
75 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
22 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
35 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
67 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
84 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
64 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
45 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
92 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
322 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
79 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
470 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
78 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
142 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
65 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
888 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
313 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
292 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
117 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
298 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
234 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
241 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
218 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
46 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
72 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
57 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
435 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
97 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
331 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
57 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
317 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
767 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
578 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
229 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
179 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
82 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
408 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
6k 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 ...
1
vote
1answer
349 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 ...