Write a trigger which will be executed whenever insertion is made to the employee table, if age is less than 21, insertion should not be allowed
Write a trigger which will be executed whenever insertion is made to the employee table, if age is less than 21, insertion should not be allowed
---->>>
Acode2=# create or replace function less() returns trigger as
Acode2-# '
Acode2'# declare
Acode2'#
Acode2'#
Acode2'# begin
Acode2'#
Acode2'# if new.eage < 21 then
Acode2'#
Acode2'# raise exception ''age is not valide'';
Acode2'# end if;
Acode2'# return new;
Acode2'# end;
Acode2'#
Acode2'# 'language 'plpgsql';
CREATE FUNCTION
Create Trigger function
Acode2=# create trigger vasi
Acode2-# before insert on employee
Acode2-#
Acode2-# for each row execute procedure less();
CREATE TRIGGER
Acode2=# \d employee;
Table "public.employee"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
eno | integer | | not null |
ename | character varying(50) | | |
eage | integer | | |
datej | date | | |
pno | integer | | |
Indexes:
"employee_pkey" PRIMARY KEY, btree (eno)
Foreign-key constraints:
"employee_pno_fkey" FOREIGN KEY (pno) REFERENCES project(pno)
Triggers:
vasi BEFORE INSERT ON employee FOR EACH ROW EXECUTE PROCEDURE less()
Acode2=# select * from employee;
eno | ename | eage | datej | pno
-----+---------+------+------------+-----
1 | Acode | 18 | 2020-04-28 | 1
2 | mujju | 18 | 2020-05-21 | 2
3 | adharsh | 18 | 2020-04-21 | 3
4 | rohan | 18 | 2019-04-12 | 4
5 | gaurav | 18 | 2019-08-01 | 1
6 | tejas | 18 | 2019-06-01 | 2
7 | utkarsh | 18 | 2019-08-15 | 3
(7 rows)
Out put of function
o\p
Acode2=# insert into employee values (7,'utkarsh', 18 ,'2019-08-15', 3);
ERROR: age is not valide
CONTEXT: PL/pgSQL function less() line 9 at RAISE
Acode2=# insert into employee values (10,'utkarsh', 22 ,'2019-08-15', 3);
INSERT 0 1
Acode2=# select * from employee;
eno | ename | eage | datej | pno
-----+---------+------+------------+-----
1 | Acode | 18 | 2020-04-28 | 1
2 | mujju | 18 | 2020-05-21 | 2
3 | adharsh | 18 | 2020-04-21 | 3
4 | rohan | 18 | 2019-04-12 | 4
5 | gaurav | 18 | 2019-08-01 | 1
6 | tejas | 18 | 2019-06-01 | 2
7 | utkarsh | 18 | 2019-08-15 | 3
10 | utkarsh | 22 | 2019-08-15 | 3
(8 rows)
0 Comments