#!/usr/bin/env python
# coding: utf-8

# In[1]:


get_ipython().run_line_magic('load_ext', 'sql')


# In[2]:


get_ipython().run_line_magic('sql', 'mysql+pymysql://root:1234@localhost/information_schema')


# ### Initalize  Database

# In[10]:


get_ipython().run_cell_magic('sql', '', 'drop database triggers_test;\ncreate database triggers_test;\nuse triggers_test;\n\ncreate table Emp(\nssn char(10),\nname varchar(50) not null,\nsalary decimal(8,2) ,\ndept_id int not null, \nconstraint minsalary check (salary >=10000),\nPrimary key  (ssn)\n);\n\ncreate table Dept(\nid int,\nname char(20) not null,\nmanager_ssn char(10),\nbudget decimal(10,2) default 0,\nno_of_employee int default 0,\n\nprimary key (id)\n);\n\nAlter table  Dept add foreign key (manager_ssn) references Emp(ssn);\nAlter table Emp add Foreign key (dept_id) references dept(id);\n')


# ### Trigger 1 Example on Insert

# In[15]:


get_ipython().run_cell_magic('sql', '', '\n#DELIMITER // \ncreate trigger Emp_insert \nafter Insert on Emp \nFor Each Row\nBEGIN\nupdate Dept set budget= budget + new.salary where id=new.dept_id;\nupdate Dept set no_of_employee= no_of_employee + 1 where id=new.dept_id;\nEND\n#//\n')


# ### Trigger 2 Example on Delete

# In[9]:


get_ipython().run_cell_magic('sql', '', '\ncreate trigger del_emp \nafter Delete on Emp \nFor Each Row\nBEGIN\nupdate Dept set budget= budget - old.salary where id=old.dept_id;\nupdate Dept set no_of_employee= no_of_employee - 1 where id=old.dept_id;\nEND \n')


# ### Trigger 3 Example on Update

# In[6]:


get_ipython().run_cell_magic('sql', '', '\ncreate trigger update_emp \nafter Update on Emp \nFor Each Row\nBEGIN\nupdate Dept set budget= budget - old.salary where id=old.dept_id;\nupdate Dept set budget= budget + new.salary where id=new.dept_id;\nupdate Dept set no_of_employee= no_of_employee - 1 where id=old.dept_id;\nupdate Dept set no_of_employee= no_of_employee + 1 where id=new.dept_id;\nEND\n')


# ### Trigger 4 Example on Update

# In[16]:


get_ipython().run_cell_magic('sql', '', "\ncreate trigger delete_dept \nbefore Delete on Dept\nFor Each Row\nBEGIN\n   IF old.no_of_employee >0  then\n      SIGNAL SQLSTATE '45000'\n      SET MESSAGE_TEXT = 'Could not delete that department'; \n    END IF;\nEND\n")


# ### insert some data and inspect the department table

# In[17]:


get_ipython().run_cell_magic('sql', '', "insert into dept values (1, 'sales',NULL, 0, 0);\nInsert into emp values ('123', 'Emp 1', 11000, 1);\nInsert into emp values ('234', 'Emp 2', 11000, 1);\ninsert into dept values (2, 'hr',NULL, 0, 0);\n\nselect * from dept;\n")


# In[11]:


get_ipython().run_cell_magic('sql', '', "\nupdate emp set salary = 200 where ssn='123';\n")


# In[6]:


get_ipython().run_cell_magic('sql', '', '\nselect * from Dept;\n')


# In[14]:


get_ipython().run_cell_magic('sql', '', 'select * from dept;\n')


# In[18]:


get_ipython().run_cell_magic('sql', '', 'select * from dept; \n')


# In[19]:


get_ipython().run_cell_magic('sql', '', 'delete from dept where id =1;\n')


# In[28]:


get_ipython().run_cell_magic('sql', '', '\n\nSELECT \n case id when 1 \n   then "Sales Department" \n    else id \n   end   as Dept_name\nfrom dept;\n')


# In[ ]:


get_ipython().run_cell_magic('sql', '', "\ncreate trigger Emp_insert_1\nafter Insert on Emp \nFor Each Row\nBEGIN\n\ndeclare  N int;\nSET N = (select  no_of_employee from dept where id=new.dept_id) ;\nIF N >= 3 Then\n\t SIGNAL SQLSTATE '45000'\n      SET MESSAGE_TEXT = 'Too many Employees';\n  END IF;    \nEND\n\n")