triggers
- create trigger on rahul_emp_del when deletion occurs on rahul_emp
1
2
3
4
5
6
7
8
9
10
create or replace trigger rahul_emp_del
before delete on rahul_emp
for each row
begin
insert into rahul_emp_history values
(:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,
:old.sal,:old.comm,:old.deptno);
end ;
- add total employees in rahul_dept
1
alter table rahul_dept add tot_emp number;
- update rahul_dept when insertion occurs on rahul_emp
1
2
3
4
5
6
7
8
create or replace trigger rahul_dept_tot_emp
after insert on rahul_emp
for each row
begin
update rahul_dept set tot_emp = (tot_emp + 1)
where deptno = :new.deptno;
end;
- trigger for insert or updating or deleting on rahul_emp
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
create or replace trigger rahul_dept_tot_emp
after insert or delete or update of deptno on rahul_emp
for each row
begin
if inserting or updating then
update rahul_dept set tot_emp = tot_emp + 1
where deptno = :new.deptno;
end if;
if updating or deleting then
update rahul_dept set tot_emp = tot_emp - 1
where deptno = :old.deptno;
end if;
end;
- trigger executes operations when a condition matches
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create or replace trigger rahul_dept_tot_emp
after insert or delete or update of deptno on rahul_emp
for each row
when(old.deptno <> new.deptno)
begin
if inserting or updating then
update rahul_dept set tot_emp = tot_emp + 1
where deptno = :new.deptno;
end if;
if updating or deleting then
update rahul_dept set tot_emp = tot_emp - 1
where deptno = :old.deptno;
end if;
end;
- conditions
1
if((to_char(sysdate, 'HH')>=8 &&to_char(sysdate, 'MM')>=30) && (to_char(sysdate, 'HH')<=17 && to_char(sysdate, 'MM')<=30) && (to_char(sysdate, 'd')>=1 && to_char(sysdate, 'd')<=6))