In [1]:

%load_ext sql

In [2]:

%sql mysql+pymysql://root:1234@localhost/information_schema

Initalize Database¶

In [10]:

%%sql
drop database triggers_test;
create database triggers_test;
use triggers_test;

create table Emp(
ssn char(10),
name varchar(50) not null,
salary decimal(8,2) ,
dept_id int not null, 
constraint minsalary check (salary >=10000),
Primary key  (ssn)
);

create table Dept(
id int,
name char(20) not null,
manager_ssn char(10),
budget decimal(10,2) default 0,
no_of_employee int default 0,

primary key (id)
);

Alter table  Dept add foreign key (manager_ssn) references Emp(ssn);
Alter table Emp add Foreign key (dept_id) references dept(id);

 * mysql+pymysql://root:***@localhost/information_schema
2 rows affected.
1 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.

Out[10]:

[]

Trigger 1 Example on Insert¶

In [15]:

%%sql

#DELIMITER // 
create trigger Emp_insert 
after Insert on Emp 
For Each Row
BEGIN
update Dept set budget= budget + new.salary where id=new.dept_id;
update Dept set no_of_employee= no_of_employee + 1 where id=new.dept_id;
END
#//

 * mysql+pymysql://root:***@localhost/information_schema
(pymysql.err.OperationalError) (1359, 'Trigger already exists')
[SQL: #DELIMITER // 
create trigger Emp_insert 
after Insert on Emp 
For Each Row
BEGIN
update Dept set budget= budget + new.salary where id=new.dept_id;
update Dept set no_of_employee= no_of_employee + 1 where id=new.dept_id;
END
#//]
(Background on this error at: https://sqlalche.me/e/20/e3q8)

Trigger 2 Example on Delete¶

In [9]:

%%sql 

create trigger del_emp 
after Delete on Emp 
For Each Row
BEGIN
update Dept set budget= budget - old.salary where id=old.dept_id;
update Dept set no_of_employee= no_of_employee - 1 where id=old.dept_id;
END

 * mysql+pymysql://root:***@localhost/information_schema
0 rows affected.

Out[9]:

[]

Trigger 3 Example on Update¶

In [6]:

%%sql

create trigger update_emp 
after Update on Emp 
For Each Row
BEGIN
update Dept set budget= budget - old.salary where id=old.dept_id;
update Dept set budget= budget + new.salary where id=new.dept_id;
update Dept set no_of_employee= no_of_employee - 1 where id=old.dept_id;
update Dept set no_of_employee= no_of_employee + 1 where id=new.dept_id;
END

 * mysql+pymysql://root:***@localhost/information_schema
(pymysql.err.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER //\ncreate trigger update_emp \nafter Update on Emp \nFor Each Row\nBEGIN\n' at line 1")
[SQL: DELIMITER //
create trigger update_emp 
after Update on Emp 
For Each Row
BEGIN
update Dept set budget= budget - old.salary where id=old.dept_id;
update Dept set budget= budget + new.salary where id=new.dept_id;
update Dept set no_of_employee= no_of_employee - 1 where id=old.dept_id;
update Dept set no_of_employee= no_of_employee + 1 where id=new.dept_id;
END //]
(Background on this error at: https://sqlalche.me/e/20/f405)

Trigger 4 Example on Update¶

In [16]:

%%sql

create trigger delete_dept 
before Delete on Dept
For Each Row
BEGIN
   IF old.no_of_employee >0  then
      SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Could not delete that department'; 
    END IF;
END

 * mysql+pymysql://root:***@localhost/information_schema
0 rows affected.

Out[16]:

[]

insert some data and inspect the department table¶

In [17]:

%%sql
insert into dept values (1, 'sales',NULL, 0, 0);
Insert into emp values ('123', 'Emp 1', 11000, 1);
Insert into emp values ('234', 'Emp 2', 11000, 1);
insert into dept values (2, 'hr',NULL, 0, 0);

select * from dept;

 * mysql+pymysql://root:***@localhost/information_schema
(pymysql.err.IntegrityError) (1062, "Duplicate entry '1' for key 'dept.PRIMARY'")
[SQL: insert into dept values (1, 'sales',NULL, 0, 0);]
(Background on this error at: https://sqlalche.me/e/20/gkpj)

In [11]:

%%sql

update emp set salary = 200 where ssn='123';

 * mysql+pymysql://root:***@localhost/information_schema
(pymysql.err.OperationalError) (3819, "Check constraint 'minsalary' is violated.")
[SQL: update emp set salary = 200 where ssn='123';]
(Background on this error at: https://sqlalche.me/e/20/e3q8)

In [6]:

%%sql

select * from Dept;

 * mysql+pymysql://root:***@localhost/information_schema
0 rows affected.

Out[6]:

id	name	manager_ssn	budget	no_of_employee

In [14]:

%%sql
select * from dept;

 * mysql+pymysql://root:***@localhost/information_schema
2 rows affected.

Out[14]:

id	name	manager_ssn	budget	no_of_employee
1	sales	None	22000.00	2
2	hr	None	0.00	0

In [18]:

%%sql 
select * from dept;

 * mysql+pymysql://root:***@localhost/information_schema
2 rows affected.

Out[18]:

id	name	manager_ssn	budget	no_of_employee
1	sales	None	22000.00	2
2	hr	None	0.00	0

In [19]:

%%sql
delete from dept where id =1;

 * mysql+pymysql://root:***@localhost/information_schema
(pymysql.err.OperationalError) (1644, 'Could not delete that department')
[SQL: delete from dept where id =1;]
(Background on this error at: https://sqlalche.me/e/20/e3q8)

In [28]:

%%sql


SELECT 
 case id when 1 
   then "Sales Department" 
    else id 
   end   as Dept_name
from dept;

 * mysql+pymysql://root:***@localhost/information_schema
2 rows affected.

Out[28]:

Dept_name
Sales Department
2

In [ ]:

%%sql 

create trigger Emp_insert_1
after Insert on Emp 
For Each Row
BEGIN

declare  N int;
SET N = (select  no_of_employee from dept where id=new.dept_id) ;
IF N >= 3 Then
	 SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Too many Employees';
  END IF;    
END