mysql trigger (mysql-trigger)

http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html


CREATE
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name trigger_time trigger_event
    ON tbl_name FOR EACH ROW trigger_body



trigger_body is the statement to
      execute when the trigger activates. If you want to execute
      multiple statements, use the
      BEGIN ... END
      compound statement construct. This also enables you to use the
      same statements that are permissible within stored routines



mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
 FOR EACH ROW SET @sum = @sum + NEW.amount;


++++++++++++++++++++++++++++++++++
--  ***************************************************************************
--  *   Copyright (C) 2012, Paul Lutus                                        *
--  *                                                                         *


--  *   This program is free software; you can redistribute it and/or modify  *
--  *   it under the terms of the GNU General Public License as published by  *
--  *   the Free Software Foundation; either version 2 of the License, or     *


--  *   (at your option) any later version.                                   *
--  *                                                                         *
--  *   This program is distributed in the hope that it will be useful,       *


--  *   but WITHOUT ANY WARRANTY; without even the implied warranty of        *
--  *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the         *
--  *   GNU General Public License for more details.                          *


--  *                                                                         *
--  *   You should have received a copy of the GNU General Public License     *
--  *   along with this program; if not, write to the                         *


--  *   Free Software Foundation, Inc.,                                       *
--  *   59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.             *
--  ***************************************************************************



create database if not exists tutorial;
use tutorial;

-- drop table if exists debug;

-- create table if not exists debug (
--   debug text
-- );

drop table if exists purchases;

create table if not exists purchases (


  Item text not null,
  Quan integer not null,
  Price decimal(10,2) not null,
  `Disc %` decimal(10,2),
  PreTax decimal(10,2),
  Tax decimal(10,2),
  Subtotal decimal(10,2),
  Total decimal(10,2),


  pk integer not null auto_increment,
  primary key (`pk`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8; 

drop trigger if exists update_purchases;

drop trigger if exists insert_purchases;

drop procedure if exists process_purchase;



delimiter |

create procedure process_purchase (
  in quantity integer,
  in price decimal(10,2),
  inout discount decimal(10,2),
  inout beforetax decimal(10,2),
  inout salestax decimal(10,2),


  inout subtotal decimal(10,2),
  out total decimal(10,2),
  in  newpk integer
  )
BEGIN
  set discount = if(quantity >= 10,10,0);
  set beforetax = price * quantity * 1 - (discount / 100);
  set salestax = beforetax * 0.085;


  set subtotal = beforetax + salestax;
  set @prior_total = 0;
  if(newpk = 0) then
    select temptable.Total into @prior_total from tutorial.purchases as temptable where pk != 0 order by pk desc limit 1;
  else


    select temptable.Total into @prior_total from tutorial.purchases as temptable where pk < newpk order by pk desc limit 1;
  end if;
  set total = subtotal + @prior_total;
END|

CREATE TRIGGER `update_purchases`


before update ON `purchases`
for each row
BEGIN
  call process_purchase(new.Quan,new.Price,new.`Disc %`,new.PreTax,new.Tax,new.Subtotal,new.Total,new.pk);
END|


CREATE TRIGGER `insert_purchases`

before insert ON `purchases`
for each row
BEGIN
  call process_purchase(new.Quan,new.Price,new.`Disc %`,new.PreTax,new.Tax,new.Subtotal,new.Total,new.pk);
END|


delimiter ;


-- insert some sample data

insert into purchases (Item,Quan,Price) values('Gadgets',10,5.98);
insert into purchases (Item,Quan,Price) values('Widgets',8,4.33);
insert into purchases (Item,Quan,Price) values('Tools',16,32.88);


insert into purchases (Item,Quan,Price) values('Hardware',34,2.45);
insert into purchases (Item,Quan,Price) values('Mousetraps',22,5.93);

-- update purchases set Total = 0;

-- select * from debug;



+++++++++++++++++++++++++++++++++++++++++

create trigger ai_eav
after insert on eav
for each row
begin
set @id=new.entity;
set @attribute=new.attribute;
set @value=new.value;
update pivot
set 
Author=(select if(@attribute='Author',@value,Author)),
Title=(select if(@attribute='Title',@value,Title)),
Publisher=(select if(@attribute='Publisher',@value,Publisher))
where
id=@id;
end

+++++++++++++++++++++++++++++++++++++++++++++++++

Database changed
mysql> show tables;

+------------------+
| Tables_in_shamun |
+------------------+
| contacts         |


| forum            |
+------------------+

2 rows in set (0.00 sec)

mysql> delimiter ;;
mysql> create trigger foo
-> after insert on forum
-> for each row
-> begin
-> insert into contacts (email) values ('example@abc.com');
-> end
-> ;;
Query OK, 0 rows affected (0.31 sec)

mysql> delimiter ;

// daily query
mysql> insert into forum (status) values ('open');
Query OK, 1 row affected, 13 warnings (0.00 sec)

mysql> select *from forum;

+----+--------+---------+------+------+------+


| id | status | subject | date | time | user |
+----+--------+---------+------+------+------+
|  2 | open   | NULL    | NULL | NULL | NULL |
+----+--------+---------+------+------+------+

1 row in set (0.00 sec)

mysql> select email from contacts;

+-----------------+
| email           |
+-----------------+
| example@abc.com |
+-----------------+

1 row in set (0.00 sec)