Gecko [ www.jdcpatala.org ]

Name	Size	Permission
gcol_blocked_sql_funcs_main.in...	11.78 KB	-rw-r--r--
gcol_cleanup.inc	1.34 KB	-rw-r--r--
gcol_column_def_options.inc	20.94 KB	-rw-r--r--
gcol_dependancies_on_vcol.inc	2.39 KB	-rw-r--r--
gcol_handler.inc	3.03 KB	-rw-r--r--
gcol_init_vars.pre	1.17 KB	-rw-r--r--
gcol_ins_upd.inc	18.39 KB	-rw-r--r--
gcol_keys.inc	26.66 KB	-rw-r--r--
gcol_non_stored_columns.inc	6.42 KB	-rw-r--r--
gcol_partition.inc	5.8 KB	-rw-r--r--
gcol_rejected.inc	21.29 KB	-rw-r--r--
gcol_select.inc	40.05 KB	-rw-r--r--
gcol_supported_sql_funcs.inc	1.63 KB	-rw-r--r--
gcol_supported_sql_funcs_main....	41.09 KB	-rw-r--r--
gcol_trigger_sp.inc	2.84 KB	-rw-r--r--
gcol_unsupported_storage_engin...	1.38 KB	-rw-r--r--
gcol_view.inc	6.89 KB	-rw-r--r--

Code Editor : gcol_trigger_sp.inc

################################################################################
# inc/gcol_trigger_sp.inc                                                      #
#                                                                              #
# Purpose:                                                                     #
#  Testing triggers, stored procedures and functions                           #
#  defined on tables with generated columns.                                     #
#                                                                              #
#                                                                              #
#                                                                              #
#------------------------------------------------------------------------------#
# Original Author: Andrey Zhakov                                               #
# Original Date: 2008-09-04                                                    #
# Change Author:                                                               #
# Change Date:                                                                 #
# Change:                                                                      #
################################################################################

create table t1 (a int, 
                 b int generated always as (a/10) virtual,
		 c int generated always as (a/10) stored);

create table t2 (a timestamp);

delimiter |;

create trigger trg1 before insert on t1 for each row
begin
  if (new.b < 10) then
    set new.a:= 100;
    set new.b:= 9;
    set new.c:= 9;
  end if;

if (new.c > 50) then
    set new.a:= 500; 
  end if;
end|

create trigger trg2 after insert on t1 for each row
begin
  if (new.b >= 60) then
    insert into t2 values (now()); 
  end if;
end|

create function f1()
returns int
begin
  declare sum1 int default '0';
  declare cur1 cursor for select sum(b) from t1;
  open cur1;
  fetch cur1 into sum1;
  close cur1;
  return sum1;
end|

delimiter ;|

set sql_warnings = 1;

insert into t1 (a) values (200);
select * from t1;
select * from t2;

insert into t1 (a) values (10);
--sorted_result
select * from t1;
select * from t2;

insert into t1 (a) values (600);
--sorted_result
select * from t1;
--replace_column 1 <timestamp>
select * from t2;

select f1();

set sql_warnings = 0;

drop trigger trg1;
drop trigger trg2;
drop table t2;

delimiter |;

create procedure p1()
begin
  declare i int default '0';
  create table t2 like t1;
  insert into t2 (a) values (100), (200);
  begin
    declare cur1 cursor for select sum(c) from t2;
    open cur1;
    fetch cur1 into i;
    close cur1;
    if (i=30) then
      insert into t1 values (300,default,default);
    end if;
  end;
end|

delimiter ;|

delete from t1;

call p1();

--sorted_result
select * from t2;
--sorted_result
select * from t1;

drop table t1,t2;
drop procedure p1;

${this.title}

Code Editor : gcol_trigger_sp.inc