Jun 27th, 2014 at 12:54 AM

Answer of questions on MySQL trigger

Q1. Why we use triggers?

Answer:

• Audit trails : Executing additional insertion can be done automatically with a trigger. • Validation: You can use triggers to validate data before updating the database. • Referential integrity enforcement: it occasionally may make sense to use triggers to ensure that these tasks occur automatically.

Q2. Write down five limitations of MySQL's trigger.

Answer:

• Temporary tables are not supported. • Views are not supported. • Triggers must be unique. • Error handling and reporting support is immature

Q3. Create insert,update,delete triggers with appropriate after and before clause on user table.

Answer:

create a log table

drop table if exists log_user;
create table log_user(id int(10) primary key auto_increment,
activity varchar(20), description text, time datetime);

create update trigger

delimiter //
drop trigger if exists au_user//
create trigger au_user after update on user
for each row
begin insert into log_user(activity,description,time) values('UPDATE',concat('Name: ',old.name,'=>',new.name,',Email :',old.email,'=>',new.email),now());
end;//
delimiter ;

create delete trigger

delimiter //
drop trigger if exists db_user//
create trigger bd_user before delete on user 
for each row
begin
insert into log_user(activity,description,time)values('DELETE',concat('Name :',old.name,'=>','none',', Email :',old.email,'=>','none'),now());
end;//
delimiter ;

create insert trigger

delimiter //
drop trigger if exists ai_user//
create trigger ai_user after insert on user 
for each row
begin
insert into log_user(activity,description,time)values('INSERT',concat('Name :','none','=>',new.name,'Email :','none','=>',new.email),now());
end;//
delimiter ;

Q4. How can you display triggers you have created?

Answer:

	mysql> show triggers G;

Q5. How can you modify trigger?

Answer:

At the time of writing, there was no supported command or GUI application available for modifying an existing trigger. Therefore, perhaps the easiest strategy for modifying a trigger is to delete and subsequently re-create it.

Q6. Write Before triggers vs. After triggers.

Answer:

Before trigger when validating or modifying data that we intend to insert or update. On the other hand, an after trigger should be used when data is to be propagated or verified against other tables, and for carrying out calculations.

Q7. Integrate one of your trigger into web application

Answer:

<?php
if(isset($_POST['btn'])){
	// Connect to the MySQL database 
	$db = new mysqli("localhost","root","","test"); 
	
	$stm=$db->stmt_init();
	$stm->prepare("select * from log_user");
	$stm->execute();
	$stm->bind_result($id,$activity,$desc,$time);
	while($stm->fetch())printf("<div style='border:1px dashed; width:700px;margin:5px auto; padding:8px'>ID: %d <br>Activity: %s <br> Description : %s <br> Time :  %s  <br/><br/></div><hr/>",$id,$activity,$desc,$time); 
	$stm->close();
	$db->close();
	
}
?>

html file format

<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Intregreted Trigger into web pages</title>
</head>
<form action="#" method="post">

<input type="submit" name="btn" value="Show" />
</form>

<body>
</body>
</html>

Answer of questions on MySQL trigger

Q1. Why we use triggers?

Answer:

Q2. Write down five limitations of MySQL's trigger.

Answer:

Q3. Create insert,update,delete triggers with appropriate after and before clause on user table.

Answer:

Q4. How can you display triggers you have created?

Answer:

Q5. How can you modify trigger?

Answer:

Q6. Write Before triggers vs. After triggers.

Answer:

Q7. Integrate one of your trigger into web application

Answer:

Comments 8