SQL Trigger – Employee Database

In this article, you come to know how the SQL Trigger is use in a detail and step by step with a employee database example.

If your are looking for this topic then you are at right place.

Let’s start with the topic SQL Trigger using Employee Database

In Database, as trigger is a stored procedure which get invokes automatically whenever a special event in the database occurs.

Let’s a look with simple example in which trigger is get invoke when row is inserted into specified table.

It is can also be invoke when table column get updated.

Below is the syntax from which you can understand regarding creation of trigger.

create trigger [trigger_name] 
[before | after]  
{insert | update | delete}  
on [table_name]  
[for each row]  
[trigger_body] 

Below is the explanation regarding syntax.

• create trigger [trigger_name]: Replaces or Creates an existing trigger with the trigger_name.
• [before | after]: It represents when the trigger will be executed.
• {insert | update | delete}: It represents a DML operation.
• on [table_name]: It represents the name of the table associated with the trigger.
• [for each row]: It represents a row-level trigger that will be executed for each row being affected.
• [trigger_body]: This provides the operation to be performed as the trigger is fired.

What is Before and After trigger and how to use it?

BEFORE trigger: It run the trigger action before the triggering statement is run.

AFTER trigger : It run the trigger action after the triggering statement is run.

Now we going to check all this functionality using Employee Database.

In the Below given Employee Report Database, in which Employee salary assessment is recorded.

Such schema, create a trigger so that the total and average of specified salary is automatically inserted whenever a record is inserted.

Like this, the trigger will invoke before record is inserted so, BEFORE Tag can be used.

mysql> desc Employee; 
+-------+-------------+------+-----+---------+----------------+ 
| Field | Type        | Null | Key | Default | Extra          | 
+-------+-------------+------+-----+---------+----------------+ 
| eid   | int(4)      | NO   | PRI | NULL    | auto_increment | 
| name  | varchar(30) | YES  |     | NULL    |                | 
| sal1  | int(2)      | YES  |     | NULL    |                | 
| sal2  | int(2)      | YES  |     | NULL    |                | 
| sal3  | int(2)      | YES  |     | NULL    |                | 
| total | int(3)      | YES  |     | NULL    |                | 
| per   | int(3)      | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+ 
7 rows in set (0.00 sec)

SQL Trigger to problem statement

create trigger emp_sal 
before INSERT 
on 
Employee 
for each row 
set Employee.total = Employee.sal1 + Employee.sal2 + Employee.sal3, Employee.per = Employee.total * 60 / 100;

Above SQL statement will create a trigger in the employee database in which whenever work salary are entered.

Before inserting this data into the database, trigger will compute those two values and insert with the entered values.

mysql> insert into Employee values(0, "XYZ", 20, 20, 20, 0, 0); 
Query OK, 1 row affected (0.07 sec) 

mysql> select * from Employee; 
+-----+-------+-------+-------+-------+-------+------+ 
| eid | name  | sal1  | sal2  | sal3  | total | per  | 
+-----+-------+-------+-------+-------+-------+------+ 
| 100 | XYZ   |  20   |  20   |  20   |  60   |   36 | 
+-----+-------+-------+-------+-------+-------+------+ 
1 row in set (0.00 sec)

In such a way trigger can be creates and executed in the databases.

Conclusion:

Finally, done with SQL Trigger Employee Database

I hope you like this article and if you have any query, please comment below.