/**************************************************************
  AFTER-INSERT TRIGGER
  New students automatically apply to geology major at
  Santa Cruz
**************************************************************/

create trigger R1
after insert on Student
for each row
insert into Apply values (New.ID, 'Santa Cruz', 'geology', null);

insert into Student values ('111', 'Kevin', 3.5, 1000);
select * from Student;
select * from Apply;

insert into Student values
  ('222', 'Lori', 3.6, 1000),
  ('333', 'Mary', 3.7, 950),
  ('444', 'Nate', 3.8, 900),
  ('555', 'Otis', 3.9, 1050),
  ('666', 'Pam', 4.0, 1100);
select * from Student;
select * from Apply;

/**************************************************************
  AFTER-DELETE TRIGGER
  Cascaded delete for Apply(ID) references Student(ID)
**************************************************************/

create trigger R2
after delete on Student
for each row
delete from Apply where ID = Old.ID;

delete from Student where ID > 500;
select * from Student;
select * from Apply;

/**************************************************************
  Cascaded update for Apply(location) references Campus(location)
**************************************************************/

create trigger R3
after update on Campus
for each row
update Apply
set location = New.location
where location = Old.location;

update Campus set location = 'Bezerkeley' where location = 'Berkeley';
select * from Campus;
select * from Apply;

/**************************************************************
  MULTI-STATEMENT TRIGGER
  Redo first trigger: also automatically apply to physics
  major at San Diego
**************************************************************/

drop trigger R1;
drop trigger R2;
drop trigger R3;

delimiter |
create trigger R1
after insert on Student
for each row
begin
  insert into Apply values (New.ID, 'Santa Cruz', 'geology', null);
  insert into Apply values (New.ID, 'San Diego', 'physics', null);
end
|
delimiter ;

insert into Student values
  ('777', 'Quincy', 3.6, 1000),
  ('888', 'Rita', 3.7, 950),
  ('999', 'Sue', 3.8, 900);
select * from Student;
select * from Apply;

/**************************************************************
 TRIGGER CHAINING
  When number of applicants exceeds 20, change Campus to "Done"
**************************************************************/

create trigger R3
after insert on Apply
for each row
update Campus set location = "Done"
where location = New.location
and 20 < (select count(*) from Apply where location = Campus.location);

select count(*) from Apply where location = "Santa Cruz";
select count(*) from Apply where location = "San Diego";
select count(*) from Student where ID < 300;

insert into Student (select * from Student where ID < 300);
select * from Campus;

select count(*) from Apply where location = "Santa Cruz";
select count(*) from Apply where location = "San Diego";
select count(*) from Student where ID < 300;

insert into Student (select * from Student where ID < 300);
select * from Campus;

insert into Student (select * from Student where ID < 300);
select * from Campus;

/**************************************************************
  BEFORE TRIGGERS
  Automatically reject new applications
**************************************************************/

delete from Student where ID < 300;
delete from Apply where ID < 300;
drop trigger R3;

create trigger R3
after insert on Apply
for each row
update Apply
set decision = 'N'
where ID = New.ID;

insert into Apply values (111, 'Santa Cruz', 'geology', null);

drop trigger R3;

create trigger R3
before insert on Apply
for each row
set New.decision = 'N';

insert into Apply values (111, 'Santa Cruz', 'geology', null);
select * from Apply;

/**************************************************************
  PROCEDURE AS TRIGGER BODY
  Automatically accept new applications with above average GPA,
  and reject the rest
**************************************************************/

drop trigger R3;

delimiter |
create trigger R3
before insert on Apply
for each row
begin
  declare g float; declare a float;
  select distinct GPA from Student where ID = New.ID into g;
  select avg(GPA) from Student into a;
  if (g > a) then set New.decision = 'Y';
    else set New.decision = 'N'; end if;
end
|
delimiter ;

select avg(GPA) from Student;
select * from Student;

insert into Apply values (345, 'Berkeley', 'CS', null);
insert into Apply values (999, 'Berkeley', 'CS', null);
select * from Apply;

/**************************************************************
  ENFORCING CONSTRAINTS
  Cannot insert student with GPA > 4.0
**************************************************************/

delimiter |
create trigger R4
before insert on Student
for each row
begin
  if (New.GPA > 4.0) then call raise_error(); end if;
end
|
delimiter ;

insert into Student values ('222', 'Lori', 3.5, 1000);
insert into Student values ('333', 'Mary', 4.5, 1000);

/**************************************************************
  TRIGGER RESTRICTION: CYCLES
**************************************************************/

drop trigger R1;
drop trigger R3;
drop trigger R4;

create table T1(A integer);
create table T2(A integer);
create table T3(A integer);
create table T4(A integer);

create trigger R1
after insert on T1
for each row
insert into T2 values (New.A);

create trigger R2
after insert on T2
for each row
insert into T3 values (New.A);

create trigger R3
after insert on T3
for each row
insert into T1 values (New.A);

insert into T1 values (1);

/**************************************************************
  TRIGGER RESTRICTION: SAME TRIGGERING CONDITION
**************************************************************/

drop trigger R1;
drop trigger R2;
drop trigger R3;

create trigger R1
after insert on T1
for each row
insert into T2 values (New.A);

create trigger R2
after insert on T1
for each row
insert into T3 values (New.A);

/**************************************************************
  TRIGGER NON-RESTRICTION: NESTED INVOCATIONS
**************************************************************/

drop trigger R1;

delimiter |
create trigger R1
after insert on T1
for each row
begin
  insert into T2 values (1);
  insert into T3 values (1);
end
|
delimiter ;

delimiter |
create trigger R2
after insert on T2
for each row
begin
  insert into T3 values (2);
  insert into T4 values (2);
end
|
delimiter ;

create trigger R3
after insert on T3
for each row
insert into T4 values (3);

insert into T1 values (0);
select * from T1;
select * from T2;
select * from T3;
select * from T4;

drop table T1;
drop table T2;
drop table T3;
drop table T4;