Trigger Working On Time Basis
I have a perl program that inserts rows into a mysql table and set the status to 2 of every row. I am trying to make a trigger in MySQL that will do the following:
1. After every insertion of a row, it will wait for 10 sec and after 10 sec it will check the status of that row. If the status of that row is changed, then it will not do anything. But if the status of that row is still 2 then it will update that row status to 7
View 1 Replies (Posted: February 11th, 2009, 12:54 AM)
Sponsored Links:
Related Forum Messages for MySQL:
Trigger Before Insert Not Working?
I am having trouble creating a BEFORE INSERT trigger. table schema: CREATE TABLE IF NOT EXISTS `myReferenceTable` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `linkFrom` bigint(20) NOT NULL, `linkTo` bigint(20) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `linkFrom` (`linkFrom`,`linkTo`), KEY `linkTo` (`linkTo`) ) ENGINE=InnoDB inserting data: INSERT INTO `myReferenceTable` (`id`, `linkFrom`, `linkTo`) VALUES (1, 1, 2), // allowed (2, 2, 1); // allowed My failed attempt at creating a BEFORE INSERT trigger which will not allow linkFrom and linkTo to equal each another. This table articleReferncesTable can not have any article refering to it self, /* This fails */ create trigger myReferenceTable_noDuplicate BEFORE INSERT ON myReferenceTable FOR EACH ROW BEGIN IF NEW.linkFrom = NEW.linkTo insert ignore() END IF; END; Example: INSERT INTO `myReferenceTable` (`id`, `linkFrom`, `linkTo`) VALUES (3, 1, 1), // should fail (4, 2, 2); // should fail the above data is not allowed. So i want this table to be a "set" table the following data is allowed: INSERT INTO `myReferenceTable` (`id`, `linkFrom`, `linkTo`) VALUES (3, 1, 2), // allowed (4, 1, 3); // allowed
Posted: Jan 22 at 8:27
View 1 Replies!
View Related
Trigger To Set Column To Max + 1 Not Working
I'm having difficulty writing a trigger that sets the rank column to the max rank value plus 1 for a group of user ids. Maybe the code would be more helpful than my description: CREATE TABLE `saved_listing` ( `saved_listing_id` int(10) NOT NULL auto_increment, `user_id` int(10) NOT NULL default '0', `listing_id` int(10) NOT NULL default '0', `listing_ty` varchar(10) NOT NULL default '', `notes` text NULL, `rank` int(10) NOT NULL default '0', `modify_by` int(10) NOT NULL default '1', `modify_dt` timestamp NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `create_by` int(10) NOT NULL default '1', `create_dt` datetime NOT NULL default '0000-00-00 00:00:00', `active` enum('Yes','No') NOT NULL default 'No', PRIMARY KEY (`saved_listing_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ; Here's my trigger code: CREATE TRIGGER ins_saved_listing BEFORE INSERT ON saved_listing FOR EACH ROW BEGIN SET NEW.create_dt = NOW(); SET NEW.rank = (SELECT MAX(rank) + 1 FROM saved_listing WHERE user_id = NEW.user_id); END Here's the error message I'm getting: 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3
Posted: Mar 9 at 3:05
View 1 Replies!
View Related
Database Trigger Not Working?
I've created simple trigger to create cloud table (MyISAM table just to perfor FULL TEXT searches) however it's not working (the data is not being added). DELIMITER || DROP TRIGGER IF EXISTS `table_cloud` || CREATE TRIGGER `table_cloud` AFTER INSERT ON `table` FOR EACH ROW BEGIN INSERT INTO `table_cloud` SELECT `id`, `name`, `description` FROM `table` WHERE id = LAST_INSERT_ID(); END; || DELIMITER ;
Posted: Apr 10 at 23:43
View 1 Replies!
View Related
Database - After INSERT Trigger Not Working?
I've created a trigger that resembles the following: delimiter // CREATE TRIGGER update_total_seconds_on_phone AFTER INSERT ON cdr FOR EACH ROW BEGIN......... It seems to go through okay. However it doesn't appear to be invoking when I need it to. Here's an example: mysql> select total_seconds from foo where phone_id = 1; +---------------+ | total_seconds | +---------------+ | 0 | +---------------+ 1 row in set (0.00 sec) mysql> insert into cdr (billsec, userfield) VALUES(60, 1); Query OK, 1 row affected, 12 warnings (0.00 sec) mysql> select total_seconds from foo where phone_id = 1; +---------------+ | total_seconds | +---------------+ | 0 | +---------------+ EDIT: The warnings in this particular case do not affect the trigger. It's mostly additional columns which do not have default values in the cdr table that cause the warnings. For the sake of simplicity, I kept my INSERT statement brief.
Posted: Oct 21 08 at 17:56
View 1 Replies!
View Related
Insert And Update Trigger Not Working Properly
I have two tables t1 and t2. i have created two triggers tr1 after insert on t1 and tr2 after update on t2. in both the tables i m updating table t2. so, it is throwing an error saying the t2 table is already getting updated in another trigger so can not update it again.
Posted: May 9 09 at 9:29
View 2 Replies!
View Related
Compare Time Posted With Current Time Not Working
May I know how to get records with interval of 1 hour in database where the posting time is in this format '2008-05-15 00:10:40' I tried with this : $query="SELECT date,name FROM message WHERE (timediff(date,NOW())<=CRUDATE() CURTIME())"; But is not working. May I know what is the correct way of doing it?
Posted: May 14, 2008, 19:44
View 4 Replies!
View Related
Trigger On Time
i read the documentation about trigger, and i'd like to ask u if is possible to trigger on time. Or better i'd like to execute some statement only at 12:00 o'clock. is it possible with some trigger?
Posted: September 5th, 2006, 03:51 PM
View 9 Replies!
View Related
Db BEFORE UPDATE Trigger Activation Time?
I have two InnoDB tables: CREATE TABLE master( id INTEGER UNSIGNED NOT NULL auto_increment PRIMARY KEY ); CREATE TABLE details( id INTEGER UNSIGNED NOT NULL auto_increment PRIMARY KEY, master_id INTEGER UNSIGNED NOT NULL, CONSTRAINT `byMasterId` FOREIGN KEY (`master_id`) REFERENCES `master`(`id`) ON UPDATE CASCADE ON DELETE CASCADE ); And I need a BEFORE UPDATE trigger on master table to do some validation (and cancel an update in some cases). Do cascade changes in details table occur after BEFORE UPDATE trigger?
Posted: Feb 25 10 at 20:09
View 1 Replies!
View Related
Database Before Update Trigger Activation Time?
I have two InnoDB tables: CREATE TABLE master( id INTEGER UNSIGNED NOT NULL auto_increment PRIMARY KEY ); CREATE TABLE details( id INTEGER UNSIGNED NOT NULL auto_increment PRIMARY KEY, master_id INTEGER UNSIGNED NOT NULL, CONSTRAINT `byMasterId` FOREIGN KEY (`master_id`) REFERENCES `master`(`id`) ON UPDATE CASCADE ON DELETE CASCADE ); And I need a BEFORE UPDATE trigger on master table to do some validation (and cancel an update in some cases). Do cascade changes in details table occur after BEFORE UPDATE trigger?
Posted: Feb 6 at 20:21
View 2 Replies!
View Related
Store The Time Of Creation Of Record Using A Trigger/otherwise In Mysql?
I want to automatically store request_time as the current time when keyword is updated.Here's a sample table: id request_time keyword 1 2011-02-03 8:00:08 free On inserting free, request_time should be set to NOW();.Here's the trigger i have but it doesnt work: delimiter // create trigger ai_update_userinput after update on user_input for each row begin update user_input [code]...
Posted: Mar 2 at 9:33
View 2 Replies!
View Related
Working With Intervals Of Time?
I've not been able to wrap my head around how to write a query that deals with "intervals of time", at least not without resorting to egregious hacks. To explain: Imagine my client gives me utility bills that describe electrical consumption between an irregular pair of dates -- the dates are determined by when the meter was read. (From here on, I'll use "interval" to mean a start date / end date pair.) So I have a "meter_reading" table: reading_facts ---------------------- start_date | datetime end_date | datetime kwh_consumed | float cost | float I want to be able to "re-factor" bills day by day, week by week, month by month, quarter by quarter, etc, for example to generate consumption graphs with different time scales. To simplify things, assume the existence of a series of "interval tables": daily_intervals --------------------- start_date | datetime end_date | datetime -- start_date + 1 day weekly_intervals --------------------- start_date | datetime end_date | datetime -- start_date + 7 days monthly_intervals --------------------- start_date | datetime end_date | datetime -- start_date + 1 month ... etc I want to make a query that says "for each row in the interval table, what is the average consumption over that interval"? I have already written a stored function 'COVERAGE' that computes the percentage that a row of an interval table (e.g. one week) covers one of rows of the reading_facts table. What I can't get is how to form a query that finds every row in the reading_facts table that is at least partially covered by a row in the interval table (i.e. interval_table.start_date < reading_facts.end_date AND interval_table.end_date > reading_facts.start_date), and calls my stored function with those rows. And then there's the small matter of what I do with the results of calling the stored function... If I'm going about this all wrong, let me know. (I'm also leaving out lots of details, like how I actually create an average reading, the fact that I don't actually have datetime objects in the tables, but rather foreign key pointers etc.)
Posted: March 14, 2010 12:59AM
View 4 Replies!
View Related
Php - Update In Time In Db Is Not Working
I am trying to insert date time on the MySQL DB. I have kept the field as VARCHAR(9182) date_default_timezone_set('Asia/Kolkata'); $timestamp = date("m/d/Y h:i:s a", time()); $utimeprocess = "UPDATE tabelconf SET time_of_pstart = '".mysql_escape_string($timestamp)."' WHERE UniqueID = '".mysql_escape_string($dbUniqueID)."'"; $result = mysql_query($utimeprocess); return of $result is 1
Posted: Dec 7 09 at 5:02
View 2 Replies!
View Related
InnoDb Working Some Of The Time But Not Others?
I'm in the process of swapping over a database for a rewrite of my program and part of that is writing both a conversion script and a script to create new tables. I'm renaming tables, changing indexes and generally altering most of the table in some way, part of that is that I'm changing from MyISAM to InnoDB tables. The conversion script works flawlessly but the script to create new tables falls over at a specific point. Query: create table team_resources ( amount double not null default 0, resource int unsigned not null default 0, team int unsigned not null default 0, primary key (resource,team) ) ENGINE = InnoDB I get error 121 which is the error given when a table cannot be created. The script is run from a Python file but I get the same error in both my SQL program and phpMyAdmin in both raw script and the table wizard helper form thingie. The tables all converted to InnoDB just fine so I'm stumped as to why it has issues creating new ones. This query works if I take out the InnoDB part.
Posted: Mar 30 09 at 20:02
View 2 Replies!
View Related
Working Time Between Two Datetimes?
I need to calculate a working duration between two datetimes. Let's say a guy should work on Mon, Tue, Wed, Thi, Fri between 9:00 and 18:00. He started a project Wed at 14:00 and finished it Mon of the folowing week at 13:00. I need to know how many hours did he spend on the project. I have imagined a dates and times tables like [Code]...
Posted: 03-23-09, 11:13
View 11 Replies!
View Related
Working Wit Date/time Values
I'm using the following query: Quote: SELECT * FROM IT_event_cal WHERE '$date' BETWEEN start_date AND stop_date Where start and stop_date are of type date/time (0000-00-00 00:00:00). For this particular query, I'd like to match a particular date (0000-00-00), and any time. How can I make the time part a wild-card value?
Posted: July 9th, 2008, 10:49 PM
View 9 Replies!
View Related
Put Time Difference - Working With Dates
I subtract two dates from each other because I need the time difference between them. I toggle the usual problems but when I write it to MySQL (Time) I see there is always 1 hour too much difference. If I expect 2 min difference I get 1 h 2 min. Now I think it's a timezone problem but is there a way to solve this? Date totalDate = visit.getTotalTime(); Date visitDate = visit.getVisitTime(); java.sql.Time sqlTotalTime = new java.sql.Time(totalDate.getTime() - visitDate.getTime()); ps.setTime(4, sqlTotalTime); gives 1h 2 sec difference and not 2 sec: 08/Dec/2010:07:27:54 08/Dec/2010:07:27:52
Posted: May 18 at 7:19
View 1 Replies!
View Related
Calculate Working Hours Takes Lot Of Time
I am using the below sql query to calculate working hours. The problem which i am facing is that query is taking lot of time to calculate the working hours. reduce the execution time of this query or if there is any other way to calculate working hours The following query take 63.499 sec [Code...]
Posted: 04-29-10, 02:16
View 2 Replies!
View Related
Query Is Taking Lot Of Time To Calculate The Working Hours?
I am using the below sql query to calculate working hours. The problem which i am facing is that query is taking lot of time to calculate the working hours. The following query take 63.499 sec Code: SELECT sql_calc_found_rows gstime, MAX(stoptime) AS mx, MIN(starttime) AS mn, Sec_to_time(SUM(Time_to_sec(Timediff(stoptime, starttime)))) AS totalworktime FROM (SELECT gstime,........
Posted: Apr 29, 2010, 22:07
View 2 Replies!
View Related
Mysql Trigger Stored Trigger Is Already Used By Statement Which Invoked Stored Trigger?
I want to set up a trigger so that if on an update the prediction field is = 3 then the trigger changes the value to 4 and saves it in the database. The trigger is below. For some reason I keep getting an error saying: "#1442 - Can't update table 'tzanalytic_forecast_cached' in stored function/trigger because it is already used by statement which invoked this stored function/trigger."Is this set up the right way? delimiter $$ CREATE TRIGGER no_BoW BEFORE UPDATE ON t FOR EACH ROW BEGIN set @prediction = new.prediction; [code]....
Posted: Oct 17 09 at 17:08
View 1 Replies!
View Related
Mysqldump Server Becomes Busy For Longer Time And Site Stops Working
When I'm making a backup of mysql databases, during the dump apache is unable to load pages because mysql is no longer responding to new requests. The backup is taking 5 minutes or so, which essentially brings down the website until it's finished. Has anyone got any suggestions on how to overcome this problem? I'm thinking maybe if I could quickly make a copy of the database to another database within mysql, then backup the copy that was just made.
Posted: Jun 19, 2008, 20:29
View 4 Replies!
View Related
|
TRACKER
