how to create after insert trigger in mysql

Posted by Shanky11 under Others on 9/3/2012 | Points: 10 | Views : 1621 | Status : [Member] | Replies : 4
create trigger trgupdt
after insert on product
for each row
begin
declare p_id float;
set p_id=(select max(pid)from product);
update product set total = sum(price+vat)where pid=p_id;
end;
table -product
pid pname price vat total
error------------cant update tablee 'product' in stored fucnction/trigger because it is already used by statement which invoked this stored fuction /trigger




Responses

Posted by: Muhsinathk on: 9/13/2012 [Member] Bronze | Points: 25

Up
0
Down
Differences between storedprocedures and triggers
1. When you create a trigger you have to identify event and action of your trigger but when you create s.p you don't identify event and action

2.Trigger is run automatically if the event is occured but s.p don't run automatically but you have to run it manually

3. Within a trigger you can call specific s.p but within a sp you cannot call atrigger

4.Trigger execute implicitly whereas store procedure execute via procedure call from another block.

5.We can call a stored procedure from front end (.asp files, .aspx files, .ascx files etc.) but we can't call a trigger from these files.

6. Stored procedure can take the input parameters, but we can't pass the parameters as an input to a trigger.

Shanky11, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Hariinakoti on: 9/13/2012 [Member] Starter | Points: 25

Up
0
Down
Hi,
see this below link
http://www.roseindia.net/sql/trigger/mysql-trigger-after-insert.shtml

Thanks & Regards
Hari

Shanky11, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Hariinakoti on: 9/13/2012 [Member] Starter | Points: 25

Up
0
Down
Hi,
see this below link
http://forums.mysql.com/read.php?99,114015

Thanks & Regards
Hari

Shanky11, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Hariinakoti on: 9/13/2012 [Member] Starter | Points: 25

Up
0
Down
Hi Shanky11,
if you found correct answer please select Mark As Answer

Thanks & Regards
Hari

Shanky11, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response