Member Avatar

# MySQL Stored Routines: Another Useful Scenario # [In a past guide](http://blog.bobbyhensley.com/sql-procedures-functions-authentication-example/) I discussed MySQL stored routines within MySQL. Now I’m back with another guide on MySQL stored routines. And this time it’s a more concrete case; one that you may run into yourself in the future (or maybe you already have)! Let’s jump right on, shall we? ## The scenario… ## You’re implementing a comments system to your application. Users will be able to comment on articles you and your staff post. A concern arises: **explicit comments**. It’s something all web developers have to deal with: people being crude …

Member Avatar
+2 forum 1
Member Avatar

Funnily enough, I have absolutely no experience with MySQL foreign keys. I'd like to change that :) My database schema is rather complicated, and I have a lot of work ahead of me, but let's simplify things. Suppose I have **Members Table:** id (PRIMARY KEY) username first_name last_name company etc **Posts Table:** id (PRIMARY KEY) message member_id member_username etc So now what I'd like to do is find a way to use foreign keys (I don't even know if this is possible or what it's designed for) to tell MySQL that posts.member_id relates back to members.id and posts.member_username relates back …

Member Avatar
+1 forum 10
Member Avatar

I have a code for countdown timer and it works perfectly fine.. <html> <title>project prelim</title> <head> <script type="text/javascript"> var ss = 10; function countdown() { ss = ss-1; if (ss<0) { window.location="testover.html"; } else { document.getElementById("countdown").innerHTML=ss; window.setTimeout("countdown()", 1000); } } </script> </head> <body onload="countdown()"> <center> <table width="100%" height="600px" style="text-align:center;"> <tr><td valign="center"><h3>Redirecting to testover <h2 id="countdown"></h2></h3></td></tr> </table> </center> </body> </html> Now if I am implementing it in a form which has a button like <form action="batman.php" method="post"> <button id="submit" type="submit" >Submit</button> </form> The code which I have does not do the form submit action. How can I make the form submit …

Member Avatar
+0 forum 5
Member Avatar

I am new in apex. I want to write a trigger in apex for before insert. I have to standard object (Contact, Opportunity). SELECT sum(amount), Bussiness__c from opportunity where stagename='Closed Won' and id='006i000000Kt683AAB' group by Bussiness__c I want when trigger runs this get sum(Amount) field and Bussiness__c value and then update Contact Total_Business__c with Sum(Amount) Value. Here Bussiness__C is contact id at opportunity object. Thanks in advance and Waiting for your positive Response.

+0 forum 0
Member Avatar

Hi, I am getting below error after triggering in my table. ORA-04098: trigger 'HRMS2.NTS_ADVERTISEMENTS_TRIGGER' is invalid and failed re-validation and the below is the query for triggering i used. create sequence nts_advertisements_seq start with 1 increment by 1 nomaxvalue; create trigger nts_advertisements_trigger delete insert on nts_advertisements for each row begin select nts_advertisements_seq.nextval into :new.id from dual; end; thanks in advance.

+0 forum 0
Member Avatar

How To create Trigger in PHP

Member Avatar
+0 forum 2
Member Avatar

When I try to migrate from SQL Server 2000 to SQL Server 2008 R2 Express, when I run the application program there is an error message transaction aborted by the trigger, whereas before when I was using SQL Server 2000 does not exist error. after there was a note on the trigger statement to update data that is not given block BEGIN TRANSACTION in SQL Server 2000 while it had no effect. Does the complexity of SQL server 2008 which made ​​it should not be violated? It points what should be considered when migrating from SQL server 2000 to sql …

Member Avatar
+0 forum 1
Member Avatar

Hi, Please look at this code <div class="box"> <a href="http:www.google.com" target="_blank">click</a> </div> //css .box { width:250px; height:250px; border:1px solid red; } //script $('.box').click(function(){ $(this).find('a').trigger('click'); }); How to set target _blank when clicking the div.box. click is working fine but it opens the page in same window. Can you anyone have idea.. Thanks in advance, Developer

Member Avatar
+0 forum 11
Member Avatar

I am completely new to Triggers but I hear they can help me with a specific problem. I have a table: picid picture recordListingID 1 danny.jpg 0 2 jane.jpg 0 3 ralph.jpg 0 In the recordListingID column (int) i would like to generate numbers sequentially upon an INSERT. Is it possible? Can it be done without a trigger? Thanks in advance.

Member Avatar
+0 forum 4
Member Avatar

Hello all, I need help with part of an assignment that is due Sunday. This is my first week learning mySQL so clear and simple descriptions will be well liked. Here are the instructions. a)When adding a customer, add the customer's balance times the sales rep's commission rate to the commission for the corresponding sales rep. B)When updating a customer, add the difference between the new balance and the old balance multiplied by the sales rep's commission rate to the commission for the corresponding sales rep. c)When deleting a customer, subtract the balance multiplied by the sales rep's commission rate …

Member Avatar
+0 forum 1
Member Avatar

hi I had a question in an exam that asked for the code of a trigger that cancels a transaction if a query result doesn't meet a condition in oracle database. is it possible? I am googleing but I can't find an answer thanks

Member Avatar
+0 forum 3
Member Avatar

I am creating a basic PM system for my website. I have just a basic table for a pm: mysql>describe PM; +--------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+----------------+ | PMID | mediumint(9) | NO | PRI | NULL | auto_increment | | toID | mediumint(9) | NO | | | | | fromID | mediumint(9) | NO | | | | | to_name | varchar(100) | YES | | NULL | | | from_name | varchar(100) | YES | | NULL | | | about_itemID | mediumint(9) | YES | | NULL …

Member Avatar
+0 forum 4
Member Avatar

I have a trigger(that prohibits the attribution of more than 10000 dollars to a worker that is not manager nor president) that i want to modify. CREATE OR REPLACE TRIGGER restrict_salary BEFORE UPDATE ON EMP FOR EACH ROW WHEN (new.sal > 10000) DECLARE job ename.job%type; BEGIN select job into job from ename where empid = :new.id; IF job not in ('MANAGER', 'PRESIDENT') THEN raise_application_error (-20002, 'Error that fit''s on page'); END IF; END restrict_salary; / Now i want to prohibit an increase of more than 9% on the salary. How should i go about it. Thank you

Member Avatar
+0 forum 1
Member Avatar

I'm new to triggers, but have been working with MySQL for quite a while. Now I need to implement a trigger. I want to create a trigger on my 'persons' table so that after an update, ***if the attribute clubid is changed***, attribute clubname should be changed. I have a rough syntax below, which I think should work, but I'm not sure how to limit it updates where the clubid has changed. Can anyone tell me how to limit it to running if the value clubid has changed, and also whether the syntax below is otherwise OK? (I know this …

Member Avatar
+0 forum 9
Member Avatar

Hi, i got tis trigger CREATE PROCEDURE updateG AFTER INSERT ON gnojidba FOR EACH ROW BEGIN DECLARE box int; SELECT quantity INTO box FROM stock WHERE name = NEW.name; IF (box >= NEW.Qty_value) THEN UPDATE stock SET quantity = (quantity-NEW.Qty_value) WHERE name = NEW.name; ELSE UPDATE 'Error!' SET x=1; END IF; END; Problem: after user input quantity is changed in both tables and thats ok, but if user enters quantity that is greater than quantity in stock table, that greater value is inserted and stock quantity stays the same. I try also with raise error..., call, signal...etc. Is it possible …

Member Avatar
+0 forum 2
Member Avatar

Hello everyone, im doing a small program where i have a stock table with my Items and the current stock status. How do i substract from that stock status when i insert a new order in my ordertable. My tables are: STOCK: Item_ID Stock status ORDER Item_FK: Qty So i want to make a trigger or stored procedure that substracts Qty from stockstatus based on item_ID when a new row is inserted in my ORDER table. How to do that??

Member Avatar
+0 forum 1
Member Avatar

Hi, I have this question about the control triggers, I have this project that have textbox, using it's textChanged function and setting the autopostback in to true. Now, when I trace the command I have notice that the function have been triggered twice, and I just want to know now how that happens and how I can set this to trigger only once. Thanks, JButardo

Member Avatar
+0 forum 1
Member Avatar

I am trying to insert values from a table to another table. The thing is I want it such that only a row in the source table that does not exist in the destination table should be inserted. I have set each table to have a column that automatically increments so if there are 6 rows in table 1 and only 5 rows in table 2 , only row 6 in table 1 should be inserted into table 2. Here is my code `INSERT INTO Portfolio ([SYMBOL], [SECURITY],[DATE],[OPENING PRICE],[CLOSING PRICE],[UNITS],[BROKER], [BROKERAGE COMMISSION], [SECURITY LEVY],[CONTRACT STAMP],[VAT],[NET VALUE],[PER SHARE VALUE],[PURCHASE PRICE],[GAIN / …

Member Avatar
+0 forum 3
Member Avatar

I need help making this mysql trigger. I'm creating this student enrollment project and the trigger I need needs to check that there is no course schedule conflicts when a student enrolls in course. Everytime a student enrolls in a class a row is added to the courses database. I keep the course schedules in the schedule database. These images should help to see how to solve this problem. Courses db http://76.191.104.150/courses.png Schedule Db http://76.191.104.150/schedule.png Courses joined with db http://76.191.104.150/coursesjoinwithschedule.PNG A Class is may be taken up to three times a week which is why there is day1 day2 day3, …

+0 forum 0
Member Avatar

Hello Using MySQL, I have DB 1 with table T1 on Server 1 and DB 2 with table T1 on Server 2. Not on the same network and T1 contain the same columns with the same datatypes. I want to create a trigger on DB 1 so when there is a insert on T1 on DB 1, all the data inserted in T1 is copied (or inserted) into T1 but the T1 that is on DB 2 on Server 2. Thanks for the help,

Member Avatar
+0 forum 11
Member Avatar

I'm looking for an "auto-SMS VB.Net programme module" for window mobile 6.0 professional. my application will operate like below: 1. User required pair his hp bluetooth device with his car bluetooth device for first. 2. If the accident happened the car sensor will automatic trigger the car bluetooth device to trigger the sms button and send a SMS to a predefined recipient. (this scenario is same with Bluetooth headset, once you press the Bluetoth headset button, you can trigger the mobile phone call function automatically) The process of sending the SMS is 100% automatic, trigger by the car Bluetooth Device …

+0 forum 0
Member Avatar

hi, how can i trigger an update query on a certain record if that record was not update within last say: 1 minute???/ can i doit without using any scripting? if can't then php.

Member Avatar
+0 forum 3
Member Avatar

Hi, Is there a way to trigger F11 trough javascript or jquery so.. i can go into full-screen mode? If so how can i do it.? Thanx...

Member Avatar
+0 forum 1
Member Avatar

what;s wrong with this mysql trigger logically?? [CODE] DELIMITER | CREATE TRIGGER comment_count AFTER INSERT ON comments FOR EACH ROW BEGIN UPDATE sub_sections as t1 SET t1.published_comment_count = (select count(id) as cnt from comments where article_id=t1.id and published=0 and status=0) WHERE id= (select article_id from comments order by date_modified desc limit 0,1 ); END; DELIMITER ; [/CODE]

Member Avatar
+0 forum 5
Member Avatar

Hi, Actually I don't know if my question belongs in this topic or in the mysql topic. But I'm making a VB.net application with a mysql database and i have to make a trigger but I don't know where I have to use or implement it in my application. I have a table with reservations and a table with consumptions. So when a reservation is paid, all the consumption from that reservations have to be deleted automaticly. Can someone help me with this or do I have to ask it in the mysql topic? Thanks

Member Avatar
+0 forum 4
Member Avatar

Hi, Actually I don't know if my question belongs in this topic or in the VB.net topic. But I'm making a VB.net application with a mysql database and i have to make a trigger but I don't know where I have to use or implement it in my application. I have a table with reservations and a table with consumptions. So when a reservation is paid, the trigger has to delete all the consumption from that reservations automaticly. Can someone help me with this? Thanks

Member Avatar
+0 forum 1
Member Avatar

i want to be abale to update the filed 'activated' to 0 when the 'expireDate' is before today. can you show me how to do it with a trigger? (i want to do this after the user insert the row)

Member Avatar
+0 forum 1
Member Avatar

Hi All, Is it possible to bind an array or a Vector with a event listener? For an example if we add an element to the Vector or remove from the Vector, can I use an event listener to track the changes? Thanks a lot in advance. Cheers..!!

Member Avatar
+0 forum 5
Member Avatar

I have got a grid[Grid1] that build its data when a button[search] is clicked, I managed to Ajaxify it by placing it in an UpdatePanel. Before Ajaxifying Grid 1, the grid used to populate another grid[Grid2] and some other controls[Text and Labels] when a row in Grid 1 was clicked . The Grid2 and other controls used to get populated/updated on the OnItemCommand Event of Grid 1.After I placed the Grid 1 in the update panel,they stopped updating. It will work fine if I place Grid2 and other controls in the same Update Panel but the page is designed in …

+0 forum 0
Member Avatar

Hi I there a way to make a trigger completely independent from the calling table so that even if the trigger is made invalid, inserts can still be done to the table that calls the trigger? I created the following trigger: [CODE] CREATE OR REPLACE TRIGGER LOCAL.SYNC_TRIGGER AFTER INSERT ON LOCAL.LOCAL_TABLE FOR EACH ROW DECLARE PRAGMA AUTONOMOUS_TRANSACTION; ecode NUMBER; emesg VARCHAR2(200); BEGIN -- Code to setup data inserted into remote table omitted ... -- -- INSERT INTO REMOTE_TABLE@REMOTE_DB ( SEQ, COL1, COL2, ... ) VALUES ( REMOTE_TABLE_SEQ.NEXTVAL@REMOTE_DB, :new.col1, :new.col2, ... ); COMMIT; EXCEPTION WHEN OTHERS THEN ecode := SQLCODE; emesg …

Member Avatar
+0 forum 3

The End.