TRIGGERS

SQL>create table emp(ename varchar(15),eid number(4),salary
number(6),join_date date);
Table created.

SQL>create table backup(ename varchar(15),eid number(4),salary number(6));
Table created.

SQL>create table recycle(ename varchar(15),eid number(4),salary number(6));
Table created.

SQL>create table altered(ename varchar(15),eid number(4),salary number(6));
Table created.

SQL>create table emp2(ename varchar(15),eid number(4),salary number(6));
Table created.

1.Create a trigger to set the valueof salary as 20000 whenever the salary exceeds
25000.
SQL> insert into emp values('&ename',&eid,&salary,'&join_date');
Enter value for ename: A
Enter value for eid: 123
Enter value for salary: 25000
Enter value for join_date: 25-dec-10
old 1: insert into emp values('&ename',&eid,&salary,'&join_date')
new 1: insert into emp values('A',123,25000,'25-dec-10')
1 row created.

SQL> /
Enter value for ename: B
Enter value for eid: 456
Enter value for salary: 20000
Enter value for join_date: 02-oct-08
old 1: insert into emp values('&ename',&eid,&salary,'&join_date')
new 1: insert into emp values('B',456,20000,'02-oct-08')
1 row created.

SQL> /
Enter value for ename: C
Enter value for eid: 789
Enter value for salary: 35000
Enter value for join_date: 01-jan-06
old 1: insert into emp values('&ename',&eid,&salary,'&join_date')
new 1: insert into emp values('C',789,35000,'01-jan-06')
1 row created.

SQL> select * from emp;
ENAME
EID SALARY JOINDATE
--------------- ---------- ---------- ---------
A
123
25000 25-DEC-10
B
456
20000 02-OCT-08
C
789
35000 01-JAN-06

SQL> create trigger t1
2 after insert on emp
3 begin update emp set salary = 20000
4 where salary>25000;
5 end;
6 /
Trigger created.

SQL> select * from emp;
ENAME
EID SALARY JOINDATE
--------------- ---------- ---------- ---------
A
123
25000 25-DEC-10
B
456
20000 02-OCT-08
C
789
20000 01-JAN-06
3 rows selected.

2.Create a trigger to set the join date of employee to system date whenever a record
is inserted.
SQL> select * from emp;
ENAME
EID SALARY JOINDATE
--------------- ---------- ---------- ---------
A
123
25000 25-DEC-10
B
456
20000 02-OCT-08
C
789
20000 01-JAN-06
3 rows selected.

SQL> create or replace trigger t2
2 after insert on emp
3 begin
4 update emp set joindate=(select sysdate from dual);
5 end;
6 /
Trigger created.

SQL> insert into emp values('D',159,29000,'26-jul-09');
1 row created.

SQL> insert into emp values('E',753,15000,'21-feb-11');
1 row created.

SQL> select * from emp;
ENAME
EID SALARY JOINDATE
--------------- ---------- ---------- ---------
A
123
25000 23-JAN-12
B
456
20000 23-JAN-12
C
789
20000 23-JAN-12
D
159
20000 23-JAN-12
E
753
15000 23-JAN-12
5 rows selected.

3. Create a trigger to store the data which are updated in the employee table
SQL> insert into emp values('E',852,65000,'27-mar-08');
1 row created.

SQL> select * from emp;
ENAME
EID SALARY JOINDATE
--------------- ---------- ---------- ---------
A
123
25000 23-JAN-12
B
456
20000 23-JAN-12
C
789
20000 23-JAN-12
D
159
20000 23-JAN-12
E
753
15000 23-JAN-12
E
852
20000 23-JAN-12
6 rows selected.

SQL> create trigger t3
2 after update on emp
3 for each row begin
4 insert into altered values(:old.ename,:old.eid,:old.salary);
5 end;
6 /
Trigger created.

SQL> update emp set ename='G' where eid=852;
1 row updated.

SQL> select * from emp;
ENAME
EID SALARY JOINDATE
--------------- ---------- ---------- ---------

25000 23-JAN-12
20000 23-JAN-12
20000 23-JAN-12
20000 23-JAN-12
15000 23-JAN-12
20000 23-JAN-12

4.Create a trigger to take backup whenever a record is inserted into a table and also
take the deleting record of a table to another.
SQL> create trigger t4
2 after insert or delete on emp
3 for each row begin
4 if inserting then
5 insert into backup values(:new.ename,:new.eid,:new.salary);
6 elsif deleting then
7 insert into recycle values(:old.ename,:old.eid,:old.salary);
8 end if;
9 end;
10 /
Trigger created.

SQL> insert into emp values('H',657,26000,'09-nov-11');
1 row created.

SQL> delete from emp where eid=852;
1 row deleted.

SQL> select * from emp;
ENAME
EID SALARY JOINDATE
--------------- ---------- ---------- ---------
A
123
25000 23-JAN-12
B
456
20000 23-JAN-12
C
789
20000 23-JAN-12

D
159
20000 23-JAN-12
E
753
15000 23-JAN-12
H
657
20000 23-JAN-12
6 rows selected.
5.Create a trigger to check the salary value when a new record is inserted into the
table and if the salary greater than 25000,set it to 25000.
SQL> create or replace trigger t5
2 before insert on emp2
3 for each row
4 declare
5 sa emp2.salary% type;
6 begin
7 sa:=:new.salary;
8 if sa>25000 then
9 :new.salary:=25000;
10 end if;
11 end;
12 /
Trigger created.

SQL> insert into emp2 values('I',951,36000);
1 row created.

SQL> insert into emp2 values('J',357,18000);
1 row created.