Data Control Language
To create User
create user ‘Vilasini’@localhost identified by 'tiger';
To check whether already user present there or not
select user,host from mysql.user;
To show grants
show grants for ‘Vilasini’@'localhost';
Cd Path
bin>mysql -u Nandhini -p
Enter the password
Path changed to user nandhini
Show databases;
To give the access on User
Grant select on sunitha.branch to ‘Vilasini’@’localhost’;(*.* all the tables can access)
To Revoke all access
Revoke all privileges,grant option from ‘Vilasini’@’localhost’;
To change password of the user
Alter user ‘Vilasini’@’localhost’ identified by ‘12345’;
To change username
Use mysql database then give the query
Update user set user=’newusername’ where user=’oldusername’;
To drop user
Drop user ‘username’@’localhost’;
Triggers
create database triggers;
use triggers;
#Before insert trigger
delimiter $$
create trigger age_trigger
before insert on customers
for each row
if new.age<0 then set new.age=0;
end if;
delimiter;;
insert into customers values(102,-40,"james");
select * from customers;
insert into customers values(102,-40,"pranav");
#after insert trigger
create table customers1(id int auto_increment primary key,name varchar(20) not null,email
varchar(30),birthdate date);
create table message(id int auto_increment,messageid int,message varchar(300) not
null,primary key(id,messageid));
delimiter $$
create trigger check_null_dob
after insert
on customers1 for each row
begin
if new.birthdate is null then
insert into message values(new.id,new.name,concat('Hi',new.name,'pudob'));
end if;
end
delimiter ;;
After update trigger
create table medicine(mid int,medname varchar(20),price int,quantity int);
insert into medicine values(1,"calpol",100,20);
insert into medicine values(2,"crocin",150,10);
select * from medicine;
create table neworder(mid int,odate date,newquantity int);
delimiter $$
create trigger newordermedicine after update on medicine for each row
begin
if new.quantity <20 then
insert into neworder values(new.mid,sysdate(),200);
end if;
end;
delimiter ;;
update medicine set quantity=15 where mid=1;
select * from medicine;
Before Update
1. DELIMITER $$
2.
3. CREATE TRIGGER before_update_salesInfo
4. BEFORE UPDATE
5. ON sales_info FOR EACH ROW
6. BEGIN
7. DECLARE error_msg VARCHAR(255);
8. SET error_msg = ('The new quantity cannot be greater than 2 times the current
quantity');
9. IF new.quantity > old.quantity * 2 THEN
10. SIGNAL SQLSTATE '45000'
11. SET MESSAGE_TEXT = error_msg;
12. END IF;
13. END $$
14.
15. DELIMITER ;
AFTER DELETE
1. DELIMITER $$
2.
3. CREATE TRIGGER after_delete_salaries
4. AFTER DELETE
5. ON salaries FOR EACH ROW
6. BEGIN
7. UPDATE total_salary_budget SET total_budget = total_budget - old.amount;
8. END$$
9.
10. DELIMITER ;
mysql> INSERT INTO total_salary_budget (total_budget)
11. SELECT SUM(amount) FROM salaries;
BEFORE DELETE
1. DELIMITER $$
2.
3. CREATE TRIGGER before_delete_salaries
4. BEFORE DELETE
5. ON salaries FOR EACH ROW
6. BEGIN
7. INSERT INTO salary_archives (emp_num, valid_from, amount)
8. VALUES(OLD. emp_num, OLD.valid_from, OLD.amount);
9. END$$
10.
11. DELIMITER ;
Triggers Program
1.Create PL SQL triggers for cargo booking and cancellation system.
2.Write PL SQL Triggers to display available items after a successful purchase and also
display the available items before purchasing.
3.Create PL SQL triggers for bus reservation system cancellation and reservation
actions.
4.Write PL SQL Triggers to display available items after a successful purchase and
also display the available items before purchasing.
5.Write a PL SQL Trigger program to update books details in library management system
after purchasing books.
6.Write PL SQL Triggers to display available seats after a successful reservation and also
display the available seats before reservation.
7.Demonstrate the Triggers for the above table.
8.Create a Employee database and write SQL Triggers for insert, delete, and update
operations in a database table.
9.Insert row in employee table using Triggers.
10.Demonstrate the triggers for automatic updation.
