SQL-Basics 10(Triggers & Case Statement)

                                                            Triggers & Case Statement

create database today;

use today;

select database();

Triggers:-(it basically an action and it invoke automatically)

----->Whenever we are trying to perform any kind of insert/update/delete(DML operations)(data manipulation language).before insert/update/delete, after insert/update/delete some other column, some other table.

----->In the case of functions/procedures we need to call, but for triggers, we do not need to call again, it is a one-time activity.

------>Triggers are applicable for insert/delete/update operations.

------>we have a total of 6 types of triggers.

1)before delete

2)after delete

3)before insert 

4)after insert

5)before update

6)after update

create table course(

course_id int,

course_desc varchar(30),

course_mentor varchar(30),

course_price int,

course_discout int,

create_date date)

create table course_update(

course_mentor_update varchar(50),

course_price_update int,

course_discount_update int)

1)Before Insert Triggers:-

delimiter //

create trigger course_before_insert

before insert 

on course for each row

begin

select new.create_date = sysdate();

end //

Error Code: 1415. Not allowed to return a result set from a trigger

Reason:-

----->In please the set I  have mentioned select here.

delimiter //

create trigger course_before_insert

before insert 

on course for each row

begin

set new.create_date = sysdate();

end //

select * from course;

insert into course values(101,'FSDA','sudhanshu',4000,10);

Error Code: 1136. Column count doesn't match value count at row 1

insert into course(course_id,course_desc,course_mentor,course_price,course_discout)

values(101,'FSDA','sudhanshu',4000,10);

select * from course;

insert into course(course_id,course_desc,course_mentor,course_price,course_discout) 

values(102,'FSDS','sudhanshu',17000,10);

select * from course;

Q)Suppose I want to insert the user information who has to insert the data?

create table course1(

course_id int,

course_desc varchar(30),

course_mentor varchar(30),

course_price int,

course_discout int,

create_date date,

user_info varchar(50));

insert into course1(course_id,course_desc,course_mentor,course_price,course_discout)

values(101,'FSDA','sudhanshu',4000,10);

select * from course1;

delimiter //

create trigger course_before_insert1

before insert 

on course1 for each row

begin

set new.create_date = sysdate();

    select user() into user_val;

    set new.user_info=user_val;

end //

Error Code: 1327. Undeclared variable: user_val

Reason:-

------>value for user_val is not declare.

delimiter //

create trigger course_before_insert1

before insert 

on course1 for each row

begin

    declare user_val varchar(50);

set new.create_date = sysdate(); #sysdate() is the inbuild default function(select sysdate())

    select user() into user_val;  #Here user_val is the local variable 

    set new.user_info=user_val;

end //

insert into course1(course_id,course_desc,course_mentor,course_price,course_discout)

values(101,'FSDA','sudhanshu',4000,10);

insert into course1(course_id,course_desc,course_mentor,course_price,course_discout)

values(101,'FSDA','sudhanshu',4000,10);

insert into course1(course_id,course_desc,course_mentor,course_price,course_discout)

values(101,'FSDA','sudhanshu',4000,10);

select * from course1;

select sysdate();

delimiter //

create trigger course_before_insert16

before insert 

on course1 for each row

begin

    declare user_val varchar(50);

set new.create_date = sysdate(); 

    select user() into user_val; 

    set new.user_info=user_val;

    insert into ref_course values(sysdate(),user_val);

end //

create table ref_course(

record_insert_date date,

record_insert_user varchar(50));

select * from ref_course;

insert into course1(course_id,course_desc,course_mentor,course_price,course_discout)

values(101,'FSDA','sudhanshu',4000,10);

select * from course1;

select * from ref_course;

Note:-

----->Inside triggers we can write any SQL statement, there are no limits we can write this or not.

----->Suppoce I have inserted into 1 table parallelly we can update 10 tables as well.

create table test1(

c1 varchar(50),

c2 date,

c3 int);

create table test2(

c1 varchar(50),

c2 date,

c3 int);

create table test3(

c1 varchar(50),

c2 date,

c3 int);

Q)I have a requirement if I insert the test1, it should be inserted parallelly test2, and test3 also? and the above 3 tables isolated tables? There is no connection b/w the 3 tables(primary key and foreign key also)?

sol:-

----->Try to create the triggers.

insert into test1 values("sudhanshu",sysdate(),161514)

select * from test1;

select * from test2;

select * from test3;

delimiter $$

create trigger to_update_others

before insert

on test1 for each row

begin

insert into test2 values("xyz",sysdate(),233456);

    insert into test3 values("abc",sysdate(),543217);

end $$

select * from test1;

select * from test2;

select * from test3;

insert into test1 values("sudhanshu1",sysdate(),16151418);

select * from test1;

select * from test2;

select * from test3;

Q)what happend if the insertion of the table test1 will be rollback?

sol?

--->Nothing (we have created the before insertion)

General info(In real time(organization)):-

-----> whenever we try update one table then so many depending tables,It will try to update over there.

2)after insert:-

delimiter $$

create trigger to_update_table

after insert

--------------------on test1 for each row

begin

update test2 set c1='abc' where c1='xyz';

    delete from test3 where c1='abc';

end $$

insert into test1 values("galla",sysdate(),16151418);

select * from test1;

select * from test2;

select * from test3;

3)after delete:-

delimiter $$

create trigger to_delete_table

after delete on test1 for each row

begin

insert into test3 values('after delete',sysdate(),202222);

end $$;

select * from test1;

select * from test3;

delete from test1 where c1='galla';

select * from test1;

select * from test3;

4)Before delete:-

delimiter $$

create trigger to_before_delete_table

before delete on test1 for each row

begin

insert into test3 values('before  delete',sysdate(),202222);

end $$

delete from test1 where c1='sudhanshu1';

select * from test1;

select * from test3;

Q)What is the diff b/w before delete and after delete?after insert and before insert?after update and before update?

---->we can absorb by doing the operation on the same table.

Observation:-

delimiter $$

create trigger to_before_delete_table_obseravation

before delete on test1 for each row

begin

insert into test1 values(old.c1,old.c2,old.c3);

end $$

select * from test1;

insert into test1 values("sudhanshu",sysdate(),16151418);

insert into test1 values("sudhanshu1",sysdate(),16151419);

insert into test1 values("galla",sysdate(),16151420);

insert into test1 values("galla1",sysdate(),16151421);

select * from test1;

delete from test1 where c1='galla';

Error Code: 1442. Can't update table 'test1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

delimiter $$

create trigger to_before_delete_table_obseravation1

before delete on test1 for each row

begin

insert into test2 values(old.c1,old.c2,old.c3);

end $$

delete from test1 where c1='galla';

#Error Code: 1442. Can't update table 'test1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

create table test11(

c1 varchar(50),

c2 date,

c3 int);

create table test12(

c1 varchar(50),

c2 date,

c3 int);

create table test13(

c1 varchar(50),

c2 date,

c3 int);

delimiter $$

create trigger to_before_delete_table_obseravation2

before delete on test11 for each row

begin

insert into test12(c1,c2,c3) values(old.c1,old.c2,old.c3);

end $$

insert into test11 values("sudhanshu",sysdate(),16151418);

insert into test11 values("sudhanshu1",sysdate(),16151419);

insert into test11 values("galla",sysdate(),16151420);

insert into test11 values("galla1",sysdate(),16151421);

select * from test11;

delete from test11 where c1='galla';

select * from test12;

delimiter $$

create trigger to_before_delete_table_obseravation3

after delete on test11 for each row

begin

insert into test12(c1,c2,c3) values(old.c1,old.c2,old.c3);

end $$

delete from test11 where c1='galla';

select * from test12;

delete from test11 where c1='galla1';

select * from test12# For this 2 triggers are activated.

5)after update:-

delimiter $$

create trigger to_after_update_table

after update  on test11 for each row

begin

insert into test12(c1,c2,c3) values(old.c1,old.c2,old.c3);

end $$

select * from test11;

update test11 set c1='abc' where c1='sudhanshu'

select * from test12;

6)Before update:-

delimiter $$

create trigger to_before_update_table

before update  on test11 for each row

begin

insert into test12(c1,c2,c3) values(new.c1,new.c2,new.c3);

end $$

update test11 set c1='update_new' where c1='abc';

select * from test11;

select * from test12;

Note:-

----->if the table contains both before and after update triggers it will execute first before the update first.

------>All the time before executing the first same thing happened insert and delete also.

#Q1)create table where col=name,mail_id,phone_number,address,users_sys 

#Q2)Verify and validate who is inserting the record inside this particular table all the time(insert/delete/update)?

#Q3)Try to check all the time that uses name should start with g for each and every insert opearion?

Case Statement:-

SELECT * FROM ineuron_partition.ineuron_courses;

Ex:-1

select * ,

case  when course_name='fsda' then "this is my batch"

   else "this is not your batch"

end as statement from ineuron_courses;

Ex:-2

select * ,

case  when course_name='fsda' then "this is my batch"

    when course_name='fsds' then "this is my batch"

    else "this is not your batch"

end as statement from ineuron_courses;

Ex:-3

select * ,

case when length(course_name)=4 then "len 4"

    when length(course_name)=2 then "len 2"

    else "other length"

end as statement from ineuron_courses;

Ex:-4

select * ,

case when course_name='fsda' then sysdate()

    when course_name='fsds' then  system_user()

    else "this is not your batch"

end as statement from ineuron_courses;

Ex:-5

update ineuron_courses set course_name=case

when course_name='RL' then 'reinforcement learning'

when course_name='DL' then 'deep learning'

end;

select * from ineuron_courses;

Ex:-6

update ineuron_courses set course_name=case

when course_name='RL' then 'reinforcement learning'

when course_name='DL' then 'deep learning'

else 'looking good name'

end;