DBMSLab/PLSQLTriggers.txt at master · MBadriNarayanan/DBMSLab · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
1) Create a trigger for emp table which makes the entry in name column in upper case.

create or replace trigger upper_case_name before insert on employee for each row

declare

        dup_flag INTEGER;

        begin

              :new.name := upper(:new.name);

        end;
/

Trigger created.


SQL> insert into employee values(1,'Badri','Clerk',12,'IT','23-AUG-99',45000);

SQL> select * from employee ;

EMPNO       NAME        DESIGNATION         DEPTNO          DNAME           JDATE         SALARY
------      ------      ------------        -------         ------          ------        -------
1           BADRI       Clerk               12               IT             23-AUG-99     45000


2) Create a trigger for inserting, updating records into emp table and sal should be greater than 10000.


create or replace trigger checker2 before insert on emp for each row

begin

      if(:new.salary<10000') then

                raise_application_error(-20500,'WRONG');

      end if;

end;


Trigger created.


3) Create a trigger on emp table that avoids deletion on Fridays.


create or replace trigger avoid_del before delete on emp

begin

      if(to_char(to_date(CURRENT_DATE),'DAY' = 'FRIDAY') then

                raise_application_error(-20500,'NOT A VALID MONTH');

      end if;

end;


Trigger created.


4) Create a trigger on emp table so that records of 'sunil' not be deleted.


create or replace trigger avoid_delete before delete on emp for each row

begin

      if(:old.empname = 'sunil') then

                raise_application_error(-20500,'Cannot Delete Sunil's Data');

      end if;

end;


Trigger created.