Description
A DB2 Triggers is a database program, which can be executed automatically by the server when an event is raised. DB2 Triggers executes before or after an insert, update or delete statement.
DB2 Triggers are fired, when a appropriate event occurs for the table. DB2 Triggers are used to perform verification checks on data values for insertion and perform calculations.
DB2 Triggers are of 2 types:
- Before Insert trigger
- After Insert trigger
Description
DB2 Before insert trigger will fire the trigger before performing insert records values inside the table.
Syntax
create trigger <trigger_name>
before insert
on <table_name> for each row
BEGIN
-- variable declarations
-- trigger code
END;
trigger_name => The name of the trigger to be create.
table_name => The accurate table in the database.
Examples
By viewing the below example, the concept of before insert trigger can be easily understood.
[sql]
db2 => select * from emp.employee01;
emp_id emp_name city designation salary perks
-------- ------------ ---------- -------------------- --------- -------
1 david delhi manager 12000 855
2 shaha mumbai assistant manager 13000 853
3 sha puna scales manager 11000 850
4 jack bangalore designer 14000 854
5 james mangalore web designer 15000 1124
5 record(s) selected
db => delimiter $$
db => CREATE TRIGGER inst_trigger BEFORE INSERT ON emp.EmpLOYEE01
-> FOR EACH ROW
-> BEGIN
-> UPDATE emp.Employee01 SET Salary=Salary-400 where perks>500;
-> end;
-> $$
DB20000I The SQL command completed successfully.
db2 => delimiter ;
db2 => insert into emp.employee01 (id,name,city,designation,salary,perks) values (6,'mike','chennai','develpoer',15500,840);
Query inserted
db2 => select * from emp.employee01;
emp_id emp_name city designation salary perks
--------- ----------- ----------- -------------------- --------- -------
1 david delhi manager 11600 855
2 shaha mumbai assistant manager 12600 853
3 sha puna scales manager 10600 850
4 jack bangalore designer 13600 854
5 james mangalore web designer 14600 1124
6 mike chennai develpoer 15100 840
6 record(s) selected
[/sql]
In the above example, before insert, trigger will fire the condition before inserting a record inside the table or on the table, and update command is performed on the column salary(Set Salary=Salary-400 where perks>500). Then the salary of all the employee changes as all the employee perks are greater then the applied condition.
Description
DB2 After Insert trigger will fire the trigger after performing insert records values inside the table.
Syntax
create trigger <trigger_name>
AFTER INSERT
ON <table_name> FOR EACH ROW
BEGIN
-- variable declarations
-- trigger code
END;
trigger_name =>The name of the trigger to be create.
table_name => The accurate table in the database.
Examples
By viewing the below example, the concept of after insert trigger can be easily understood.
[sql]
db2 => create table stud.student_table(stu_id int,stu_name varchar(255),stu_class int);
db2 => create table stud1.student_log1(user_id varchar(255),description varchar(255));
db2 => CREATE TRIGGER student_insert AFTER insert ON student_table FOR EACH ROW BEGIN INSERT into stud1.student_log1(user_id,description) VALUES (user(),CONCAT('INSERT student records',new.stu_id,' ',new.stu_name,' ',new.stu_class));
END$$
db2 => delimiter ;
db2 => insert into student_table(stu_id,stu_name,class) values (1,'mike',10);
db2 => insert into student_table(stu_id,stu_name,class) values(2,'mad',20);
db2 => insert into student_table(stu_id,stu_name,class) values(3,'mack',30);
db2 => select * from stud1.student_log1;
user_id description
----------------- --------------------------------
root@localhost INSERT student records1 mike 10
root@localhost INSERT student records2 mad 20
root@localhost INSERT student records3 mack 30
3 record(s) selected
[/sql]
In the above example, after insert, trigger will fire the condition after inserting a record inside the table or on the table i.e, after creating student_insert table, it will fire on the student_log1 table and all the values will display on student_log1 table which was inserted in student_table.
Key Points
- DB2 Triggers automatically executes when an event is raised.
- Before trigger - Before Insert Trigger fire before inserting the values.
- After trigger - After Insert Trigger fire after inserting the values.